SQL Server Instances installation

SQL Server Instances

SQL Server Instances define the container of all tasks you can perform within SQL Server Platform. For each instance, it is created its own set of databases, security credentials, configuration settings, services running under Windows, and so many others SQL Server Objects.

You can install up to 50 different instances of SQL Server under the same machine (including virtual machines). It could be 50 named instances, or 1 default and 49 named instances. You refer to each instance as <machine>\<instance_name>, remember that you can hide all instances setting off the SQL Server Instance Discoverable feature (whether the instance response to an enumeration request).

Installing more than one instance let you:

• Test QA testing or development
• You can support multiple service packs or patch levels
• Different DBA has access to as many as databases they require (group access permissions)
• Need of supporting multiple sets of SQL Server configuration options

There is only one limitation, for installation purposes, only SQL Server Enterprise Edition is licensed to install 50 concurrent instances. Remember, each instance, each database and other objects such as tables, columns, can be set up to use different collations.

SQL Server Configuration Manager

This applet is responsible for giving you the chance for managing SQL Server Installed Services and Protocols. Some important tasks that you will be able to perform are

• Start, stopping, pausing and restarting services
• Change Service Accounts and their password (do it only inside this applet, never use Windows Service applet to change password, or starting, stopping and pausing services)
• Managing start-up modes of a service
• Configuring different service start-up parameters

Just remember one thing, SQL Configuration Manager is the only one applet that let you do all this changes without the need of SQL Server restarting. You can also change all account passwords and the changes will be available immediately.

The Configuration Manager is the only one who has the code necessary for regeneration of master keys critical to the operation of all SQL Server services.

I will give you some recipes to install and configure a SQL Server Instance:

Creating Service Accounts:

1. Click Start, right click My Computer (Computer), select Manage, a Windows Management windows shows up
2. Expand Users and Local Groups (Windows 7)
3. Right click at Users (folder) and select New User.
4. In the username field type SQLSERVER_ENGINE, for a SQL Server service account for the database engine, supply the account with a very strong password, clear both User Must Change Password At Next Logon and Password Never Expires check boxes.
5. Repeat all these steps until you complete your service accounts (desirable more than two different accounts, one for database engine and the other for SQL Server Agent)

Installing a SQL Server Instance:

1. Start SQL Server installation routine
2. If your prerequisites are not installed, the setup will install them for you
3. At the SQL server installation windows select Installation link (at the left panel in front of you)
4. Click the New SQL Server Stand-alone Installation link,
5. Installation process begins and executes a configuration check out. If this check completes successfully it will show a status screen with some items checked "passed".
6. Click Ok, next: Select the server edition you want to install, and then click Next,
7. Select the features that your SQL Server instance will be running, then click Next,
8. Select Default Instance, enter the name of the service accounts you have created before (and their passwords)

Now it is important to mention that you have to go to Collation notch and select the collation sequence you are going to use by default at the instance level. Please take a little time to chose the right collation sequence according to the language(s) you are working with and then click Next.

9. Database Engine configuration page will show up, select mixed mode (SQL Server and Windows authentication) and set a password, click Add Current User to add the Windows account that you are running the installation under as an administrator within SQL Server. Click Add to add any other Windows accounts that you want as administrators within SQL Server
10. Click on the data directories tab to review the settings,

It is a very good practice to keep in mind file locations. For example, it will be faster and safe to keep most of these files in different hard disks. For security, performance and backup/restore policy reasons.

12. Click on the Filestream tab, select Filestream for Transact-SQL, enable Filestream for File I/O Streaming access. Put the Windows share name set to the required name or leave it to the default MSSQLSERVER, click Next. (If for some reason you forgot to enable Filestreams, you can do so from SQL Server Configuration Manager, anytime later)

13. Answer all other remaining options and review the information at the Ready to Install page. When satisfied click Install.

SQL Server will be reporting you every single step and change it made. My opinion is that SQL Server Setup routine has become not only easy, but more intuitive and complete than previous SQL Server releases. If you are taking Exam 70-432 I invite you to check out a little bit more details on this process using the references listed in past issues of this post.

Once again, thank you very much for joining us in this new project for offering a SQL Server and C# common places for C# Developers.

Best regards

Jaime Gibertoni

twitter: @jaimegibertoni