SQL Server Setup

SQL Server Setup includes planning, installation, configuration, upgrade, migration, and patching instances of SQL Server. Setup can be frustrating because SQL server is a complex set of features, and installation requirements and functionality change with every major release.  This blog describes considerations for SQL Server Setup operations and provides pointers to the best information available to you.

It is true that SQL Server installation is different for SQL Server 2000 compared to SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2.  However, if you plan ahead, an instance of SQL Server is fairly easy to install, regardless of version, and there is good documentation and community support for all scenarios. Keep in mind that all SQL Server customers must successfully clear the Setup hurdle before moving forward with SQL Server, so you are not alone. Leaving version differences aside for the moment, these strategies remain constant for all versions of SQL Server:

Installation:

• Instance Naming - Your organization might have standards for SQL Server instance naming, so investigate existing standards before doing additional work here. Basically, you can install a named instance or a default instance of SQL Server.  This decision will affect SQL Server service names and the connection strings used by applications that connect to the instance. A computer can host one default instance of SQL Server, a default instance and multiple named instances, or one or more named instances. For more information, see Instance Configuration in SQL Server Books Online.

• Multiple Instances – You can run multiple instances of SQL Server on a single computer as long as you have, at most, one default instance.  Many organizations choose to run only named instances of SQL Server. Before running multiple instances of SQL Server on a single computer, consider:
  • Resource allocation – network traffic, CPU capability, physical disk space, and memory allocation.
  • Expected growth for a given database - Estimate growth in 6-month or 12-month increments and plan accordingly. The expected growth rate is also a consideration for a computer hosting a single instance of SQL Server.
  • Service Level Agreement requirements – For example, don’t mix instances of SQL Server that have different availability requirements.    

• Editions of SQL Server – SQL Server is available in multiple editions.  SQL Server Express is free, but has a limited set of features, and it has intentional performance limitations. On the other end of the spectrum, SQL Server Enterprise and SQL Server Datacenter are engineered for mission-critical online transaction processing (OLTP), business intelligence, and other high-demand, real-time applications. They are used by major airline reservation systems, banking, finance, and inventory systems, and other applications with high availability requirements that scale to five-9’s:  99.999% uptime. For more information about features of SQL Server supported by various editions, see Features Supported by the Editions of SQL Server. Note that this topic is updated for every major release of SQL Server. The link I provided is for SQL Server 2008 R2. For pricing information, see How to buy SQL Server.     

• Hardware and Software Requirements – You must install SQL Server on hardware and software configurations that have been tested by Microsoft. Hardware and software requirements do not demand the most expensive cutting edge equipment. Rather, they ensure that you experience the performance and functionality you expect and deserve from enterprise software. The section below includes links to version-specific hardware and software requirements, as well as links to system configuration requirements that will block installation if not present when you run SQL Server Setup.

• Feature Selection – SQL Server includes components and features to develop, update, transform, store, analyze, report, and manage data. New PowerPivot features allow data to be pulled from SQL Server and manipulated in Excel, SharePoint, and MapPoint through a concept called self-service BI. For more information, see Microsoft SQL Server PowerPivot Planning and Deployment. The Feature Selection page during SQL Server Setup allows you to specify features to install. For more information, see the Books Online topic for Feature Selection in SQL Server 2008 R2 Setup.

• SQL Server Books Online – SQL Server Books Online is the official Microsoft documentation for SQL Server, and it is available on MSDN. Books Online is updated with changes that affect features and functionality for each major release. Topics are carried forward unchanged for a new release if product functionality remains the same. Use the links below to get to SQL Server Books Online on MSDN. You can also use public search engines to search for SQL Server topics of interest, but be sure that the results you drill down to are for the version of SQL Server you are using.
  • Books Online for SQL Server “Denali”
  • SQL Server 2008 R2 Books Online
  • SQL Server 2008 Books Online
  • SQL Server 2005 Books Online
  • SQL Server 2000 Books Online

• Setup documentation - To prepare for installation, read the following Books Online topics for the version of SQL Server you will install. Links to topics for recent versions of SQL Server are included below.
  • Hardware and Software Requirements
  • Security Considerations for a SQL Server Installation
  • System Configuration Checks for a SQL Server Installation
  • How to: Install SQL Server

•  
  •  For SQL Server “Denali” (Still in pre-release.  We call this a CTP, or a Customer Technology Preview release.)
    • Hardware and software requirements for installing SQL Server “Denali”
    • Security Considerations for a SQL Server Installation
    • System configuration checks during Setup:
      • Installing “Denali”
      • Upgrading to “Denali”
    • Install SQL Server “Denali”(Setup)

•  

  • For SQL Server 2008 R2

    • Hardware and software requirements for installing SQL Server 2008 R2
    • Security considerations for a SQL Server installation
    • System configuration checks during installation
    • How to: Install SQL Server 2008 R2 (Setup)

•  

  • For SQL Server 2008

    • Hardware and software requirements for installing SQL Server 2008
    • Security considerations for a SQL Server installation
    • System configuration checks during installation
    • How to: Install SQL Server 2008 (Setup)

•  

  • For SQL Server 2005

    • Hardware and software requirements for installing SQL Server 2005
    • Security considerations for a SQL Server installation
    • System configuration checks during installation
    • How to: Install SQL Server 2005 (Setup)

•  

  • For SQL Server 2000

    • Hardware and software requirements for installing SQL Server 2000
    • Setting up Windows Service Accounts for SQL Server 2000
    • How to Install SQL Server 2000 (Setup)

Upgrade:

The most common methods to upgrade from one version of SQL Server to another are to:

1. Use the Upgrade Advisor tool to identify blocking issues, mitigate those issues, and then upgrade your instance of SQL Server using SQL Server Setup. This is referred to as direct upgrade or in-place upgrade.
2. Install a new instance of SQL Server of the higher version, and then use detach/attach to move user databases from the old instance of SQL Server to the new one. This is referred to as indirect upgrade. The disadvantage of indirect upgrade is that you have to manually move your user information to the new instance of SQL Server.

Resources for upgrading:

Upgrading to SQL Server 2008 R2:

• Using Upgrade Advisor to upgrade to SQL Server 2008 R2
• How to: Upgrade to SQL Server 2008 R2 (Setup)

Upgrading to SQL Server 2008:

• Using Upgrade Advisor to upgrade to SQL Server 2008
• How to: Upgrade to SQL Server 2008 (Setup)

Upgrading to SQL Server 2005:

• Using Upgrade Advisor to upgrade to SQL Server 2005
• How to: Upgrade to SQL Server 2005 (Setup)

Migration:

Database migration refers to an operation that moves a database from a platform like Oracle, Sybase, DB2, Access, or MySql to SQL Server. Microsoft has a migration assistant tool to assist with the steps for such a migration. The migration assistant converts database objects to SQL Server objects, migrates data to SQL Server, and validates the migration of code and data. For information about migrating to SQL Server, see the SQL Server 2008 migration whitepapers. There is also a team blog for the SQL Server Migration Assistant to assist with migration from MySQL.

Migration Assistant tools:

Migrating to SQL Server 2008

Migrating to SQL Server 2005

Other Setup Resources:

SQL Server Installation Portal - https://technet.microsoft.com/en-us/sqlserver/ff625277.aspx

Installation and upgrade forum - https://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/threads

Peter Saddow’s blog. Peter is a SQL Server program manager who works on SQL Server Setup improvements - https://blogs.msdn.com/b/petersad/