SQL Server 2012 supports up to 50 instances of the Database Engine on a single host com-puter and up to 25 instances on a failover cluster. Instances are functionally separate deploy-ments of SQL Server 2012 features on the same host computer and have separate program files and data directories. Applying a service pack or software update to one instance does not update other instances unless you specify that it do so. A user or group assigned the SQL Server Administrator role for one instance might not have the role on any other instance even though the same server hosts those other instances.
Instances can host more than just separate copies of the SQL Server Database Engine.
You can deploy the following features individually or together as separate instances in SQL Server 2012:
■ Database Engine Services
■ SQL Server Replication
■ Full-Text and Semantic Extractions for Search
■ Data Quality Services
■ Analysis Services
■ Reporting Services – Native
Although it is possible to install a named instance as the first instance on a host, in most cases the first instance installed on a computer is the default instance. (The default instance
Lesson 2: Managing SQL Server Instances CHAPTER 2 81 has the name of the host on which it was installed.) Instance names have the following
prop-erties and limitations:
■ They are not case sensitive.
■ If you specify MSSQLServer as the instance name, the installation routine installs the default instance. If a default instance already exists on the host, the installer presents you with an error.
■ You can’t use the name DEFAULT or any other reserved keywords such as ADD, ALL, EXECUTE, ALTER, PRIMARY, or RECONFIGURE. You can find a full list of reserved key-words at http://msdn.microsoft.com/en-us/library/ms143507.aspx.
■ Instance names can be a maximum of 16 characters.
■ The first character in an instance name must be a letter. This can include letter charac-ters from other character sets such as Cyrillic.
■ You cannot use the backslash (\), comma (,), colon (:), semicolon (;), single quote ('), ampersand (&), embedded space, or at sign (@) in an instance name.
To install an additional SQL Server Database Engine instance by using the GUI, perform the following steps:
1. Open SQL Server Installation Center from the Configuration Tools folder of the Microsoft SQL Server 2012 folder in the Start menu. Click Yes when presented with the User Account Control dialog box.
2. In the SQL Server Installation Center dialog box, click Installation and then choose New SQL Server Stand-Alone Installation Or Add Features To An Existing Installation. In the Browse For Folder dialog box, specify the location of the SQL Server 2012 installation files.
3. On the Setup Support Rules page, click Show Details to verify that no problems are present, although this is unlikely because you are installing an additional instance.
Click OK.
4. Installation will check for updates. Click Next. On the Setup Support Rules page, verify that all steps pass and then click Next.
5. On the Installation Type page, choose Perform A New Installation Of SQL Server 2012, as shown in Figure 2-11.
FIGURE 2-11 Installing a new instance
6. On the Product Key page, enter a product key or choose between the Evaluation or Express editions. Click Next.
7. On the License Terms page, enable I Accept The License Terms and then click Next.
8. On the Setup Role page, choose SQL Server Feature Installation and then click Next.
9. On the Feature Selection page, choose Database Engine Services and then click Next.
10. On the Installation Rules page, click Next.
11. On the Instance Configuration page, shown in Figure 2-12, enter the name for the alternate instance. You must conform to the rules about instance names described earlier in this lesson. You can also choose an alternate instance directory if necessary.
Click Next.
Lesson 2: Managing SQL Server Instances CHAPTER 2 83 FIGURE 2-12 Instance Configuration page
12. Review the Disk Space Requirements and then click Next.
13. Review the Service Account and Collation settings and then click Next.
14. On the Database Engine Configuration page, select the authentication mode and which users will hold the role of SQL Server Administrator. You can also specify the options for instance data directories and whether FILESTREAM is enabled for the instance.
15. Click Next twice and then click Install. Click Close to dismiss the Setup Wizard.
To install an additional instance from the command line, either on a supported host with a traditional GUI or on a compatible Server Core installation of Windows Server 2008 R2, use the Setup.exe command with the /Action=Install and /Features=SQLEngine option. For example, to install an additional instance of the Database Engine named Alternate and set the Contoso\Kim_Akers user account to hold the role of SQL Server Administrator, use the follow-ing command:
Setup.exe /qs /Action=Install /Features=SQLEngine /InstanceName=Alternate / SQLSYSADMINACCOUNTS="Contoso\Kim_Akers" /IAcceptSQLServerLicenseTerms
EXAM TIP
Although it is possible to change the default collation, this process requires substantive effort, and it is often simpler to add a new instance with a different default collation. You can create databases with different collations by using the COLLATE clause of the CREATE DATABASE statement independent of default collation.
Quick Check
■ How many instances of SQL Server 2012 can you install on a failover cluster?
Quick Check Answer
■ You can install a maximum of 25 instances of SQL Server 2012 on a failover cluster.