SQL Server installation and initial configuration – points to consider



The journey starts with the SQL Server installation. Installing a SQL Server is key for a stabilized and healthy database system. A properly installed and configured SQL Server rarely shows any issues. So let’s get started.

Service Account recommendation:

Although a single DOMAIN\ACCOUNT can be used for the SQL Server and the SQL Agent service, it is not desirable and not recommended as both services have different execution context. Use separate domain accounts for each service.

Installation recommendation:

If a DOMAIN\ACCOUNT is used for the SQL Server service account, then

(a)   Add the DOMAIN\ACCOUNT to the local administrator group.

(b)   Log on to the intended server with this account (optional).

(c)    Run the SQL Server setup executable as “run as administrator” privileges.

(d)   Installation media can be resided in a network share or in a DVD.

Once installation is done, you need to remove the SQL Server account from the local admin group; this is recommended for security reasons. However, please note that you may still prefer the SQL Server Service account to be a part of the local admin group to avoid permission related issues.

Local policy rights to SQL Server Service Account:

SQL Server service account rights varies from edition to edition. Do not assign any local policy rights to SQL Server and SQL Agent service account in advance which are not recommended. Each edition of the SQL Server installation process will determine the required local policy rights and will be added automatically during the installation of SQL Server. To validate appropriate service rights, review the MSDN recommendations from the following link:

http://msdn.microsoft.com/en-us/library/ms143504.aspx

Windows consideration:

(a)   Network Binding order:

1.      Make sure that the domain network is the first bound network. The miss-ordering issue usually reports in SQL Server fail-over cluster installation.

2.      Usually, TCP/IP will be the most used protocol. Consider changing the order of “network protocol bindings” to make network communications faster.

(b)   Disk alignment and block size:

1.      NTFS Allocation Unit (formerly known as cluster size) must be 64K. When formatting the partition that will be used for SQL Server data files, it is recommended that you use a 64-KB allocation unit size for data, logs, and tempdb.

2.      Ensure that Volume Alignment (also known as disk, partition or sector alignment) has been set correctly. Review and align the sector to 1024KB (or 2048KB max) before formatting the disk.

3.      HBA Disk Queue Depth: In the SAN, a queue depth of 64 is generally accepted as a good starting point in the absence of any specific vendor recommendations.

(c)    Windows Power Policy option: In Windows 2008, there are three power consumption options (power plan), where “Balanced” is set to default. Make sure that the “High Performance” has been selected. To make “power plan” configuration effective, the server BIOS power management needs to be changed to “OS Control” mode.

(d)   TCP Chimney setting: Disable the TCP Chimney offload in Windows level. “TCP Chimney Offloads” functionality does not work with virtualized Windows OS (VMware and hyper-v), therefore it can be disabled permanently.

(e)   SQL Server Port: Create an appropriate inbound rule in Windows firewall for SQL Server ports (e.g. 1433, 5022) instead of disabling the firewall service.

Instant file initialization: To take advantage of the instant file initialization feature, assign the “Perform volume maintenance tasks” windows right to the SQL Server Service Account. When it is enabled, the data file (mdf and ndf, but not ldf) will be initialized instantaneously without filling any empty spaces with zeros.

Tempdb consideration: It is not necessary to create multiple tempdb data files without understanding the workload nature and concurrency in a Server. If the OLTP server experiences high concurrencies, latch contention, row versioning, a high number of sort/hash operations, then adding multiple data files will be helpful.

(a)   In a SMP based system, 8 tempdb data files can be added initially. If more data files are required, then add two tempdb data files at a time.

(b)   In a NUMA based system, add data files based on the number of NUMA nodes detected by the SQL Server Engine. For a 4-NUMA system, the tempdb might have 8 or 12 data files. If more data files are needed, then increase the tempdb data files by the number of NUMA nodes.

(c)    Make sure that all tempdb data files are equal in size.

(d)   Tempdb data files and log must be isolated and resided in a single dedicated drive. Log files of tempdb can be placed in the Log drive, but it is not necessary.

MAXDOP: The default value of MAXDOP is 0 (zero). It works best for a well written OLTP application and if the database is maintained properly. However, in some workloads, MAXDOP = 1 works best, such as SharePoint database. There is no appropriate settings for MAXDOP and it varies from server to server, and sometimes even in the same server time to time (review http://support.microsoft.com/kb/2806535). Following are some considerations:

(a)   In a SharePoint implementation, it is highly recommended by Microsoft to use MAXDOP=1.

(b)   If parallelism introduces blocking on CXPACKET, lowering the MAXDOP setting may help.

(c)    In a system, if there is an enormous amount of spinlock and latch contention occurring, lowering the MAXDOP value will help to improve the performance.

(d)   Consider increasing “cost threshold for parallelism” to fine tune the parallelism along with or instead of MAXDOP.

(e)   In case of high CAXPACKET, consider redesigning indexes, removing fragmentations and updating statistics instead of changing MAXDOP.

(f)     Please remember that creating or rebuilding an index will suffer from lower values of MAXDOP.

Transaction Log:  Following are the recommendations for the Transaction Log:

(a)   Place the transaction log in a dedicated drive.

(b)   Do not create multiple transaction logs for a single database.

(c)    Pre-size the log file well in advance; use 4G or 8GB increments per growth.

Database Integrity: Make sure that the “Page Verify” option for all databases is set to “CHECKSUM”. Running DBCC CHECKDB in production server is resource intensive and it does not help to protect the database from corruption. Instead of running DBCC CHECKDB, consider configuring SQL Server “Event Alert Service” for database fatal error (Severity from 022 to 025) and get notifications right away.

System database issues: rebuild, relocation and restore - Are we ready?

"What is the probability that the sun will NOT rise tomorrow?"

“What is the probability that the master database will NOT corrupt tomorrow?”

To answer the first question, we have to live billions of lights years, even more, I don’t know.

But I believe that the master database may corrupt tomorrow; it could be happening right now while you are reading this article. Are we ready to recover it?

In this article, we will be recovering the master database after a disaster (later on msdb, model and tempdb). When the master database has an issue, SQL Server will not start and we may see startup errors.

Good practices and good to know about master database:

Consider following recommendations:

1.        Always have a current backup of the master database available and perform system databases backup, daily basis.

2.        Perform file copy (mdf and ldf) when you stop SQL Server service during maintenance window.

3.        When there is a configuration change, do a backup before and after.

4.        Back up the master database as soon as possible after the following operations:

(a)    Creating, modifying, or dropping any database

(b)    Changing server or database configuration values

(c)     Modifying or adding logon accounts.

5.        Do not create user objects in master.

6.        Do not set the TRUSTWORTHY option to ON for the master database.

7.        Repairing system databases is not an option.

8.        Run database consistency and make sure it’s healthy.

9.        Create alerts to get earliest possible notifications for any issues that could occur.

10.    Never change configuration of any system database except the model database

11.    Location can be changed in SQL Server Configuration Manager.

Part One: Master Database:

We will review all system database’s recovery option. In our first part, we will work on the master database only.

Recovering the master database (figure # 1):

So, SQL Server 2012 (also 2005, 2008) will not start and threw an error. Following are the steps to restore the master database from the latest backup.

(a)    Open the Command prompt with administrative privileges.

(b)   Start the SQL Server Service in a single-user mode as follows:

(1)    Default instance:

C:\>NET START MSSQLSERVER /m

(2)    Named Instance:

C:\>NET START MSSQL$InstanaceName /m

e.g. C:\>NET START MSSQL$HealthCanada01 /m

Note: Replace server name and instance name to match your case

(c)    Now start the sqlcmd from command prompt

(1)    Default instance:

C:\>sqlcmd

(2)    Named instance:

C:\>sqlcmd  -SYourServer\InstanceName

e.g. C:\>sqlcmd –SmyWinServer\HealthCanada01

Note: Replace server name and instance name to match your case

(d)   In the sqlcmd command window, run the following command:

>RESTORE DATABASE master FROM DISK=’H:\DBBackup\master_backup.BAK’ WITH REPLACE

>GO

Note: Replace path and backup file name to match your case

(e)   As soon as the restore is done, SQL Server service will be terminated. Now Restart the SQL Server service by using SQL Server Configuration Manager or by using the NET START command:

Figure # 1: Restoring the master database from the latest backup.

Rebuild the master database (figure # 2):

We may need to rebuild the master database if:

(a)    After installation, the collation setting needs to change.

(b)   The master database is corrupt and there is no backup.

Following are the step by step processes to rebuild the master database. In SQL 2008R2 and SQL 2012 there is no need for setup DVD/ISO. Please note that rebuilding the master database will create all three databases - master, model and msdb.

(a)    Open the Command prompt with administrative privileges.

(b)   Go to the following folder:

SQL Server 2008R2:

C:\> CD  C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2

SQL Server 2012:

C:\> CD  C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012

SQL Server 2005: See this article:

http://msdn.microsoft.com/en-us/library/ms144259(v=sql.90).aspx

SQL Server 2008: See this article

http://msdn.microsoft.com/en-us/library/dd207003(v=sql.100).aspx

(c)    Use the following command and fit your requirement as well:

Syntax:

setup.exe

/QUIET

/ACTION=REBUILDDATABASE

/INSTANCENAME=instance_name

/SQLSYSADMINACCOUNTS=domain\ accounts

[/SAPWD=new_password]        

[/SQLCOLLATION=new_collation_name]

To rebuild the default instance:

setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=ARIBU\sqladmin /SAPWD=myPassw0rd

To rebuild the default instance to change collation:

setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=ARIBU\sqladmin /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

To rebuild the named instance (HealthCanada01):

setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME= HealthCanada01 /SQLSYSADMINACCOUNTS=ARIBU\sqladmin /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

To rebuild the named instance (HealthCanada01) with sa password and to change collation:

setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME= HealthCanada01 /SQLSYSADMINACCOUNTS=ARIBU\sqladmin /SAPWD=myPassw0rd /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

(d)   Once the master database is built, we need to reconfigure various settings again, such as login, Linked server, Server wide settings, and so on. It is also a good idea to apply SQL Server patches after rebuilding the master database.  

Figure # 2: Rebuilding the master database.

In the above screenshot I ran the following command:

setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=ARIBU\sqladmin /SAPWD=1amThe$a /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

Changing the location of master database:

In some situations, we need to start SQL Server Service by changing the location of the master database. This can be done in command prompts or from SQL Server Service Manager. Below is a screenshot (figure # 3) for starting a SQL Server 2012 Service when master database sits in a different location other than the default location.

Figure # 3: New location of master database.

Part Two: msdb and model:

Changing location of msdb or model database:

To move the model and msdb system database data or log file because of a hardware failure or in a planned location, follow these steps to relocate the file to a new location.

1.       Stop the instance of Microsoft SQL Server 2005/2008/208R2/2012 if it is started.

2.       Start the instance of Microsoft SQL Server Service in master-only recovery mode by entering one of the following commands at the command prompt.

For the default (MSSQLSERVER) instance, run the following command.

NET START MSSQLSERVER /f /T3608 /T4022

For a named instance, run the following command.

NET START MSSQL$instancename /f /T3608 /T4022

3.       For each file to be moved, use sqlcmd commands or SQL Server Management Studio to run the following statement.

ALTER DATABASE database_name  

MODIFY FILE( NAME = logical_name , FILENAME = 'new_path\os_file_name' )

4.       Exit the sqlcmd utility or SQL Server Management Studio.

5.       Stop the instance of Microsoft SQL Server.

6.       Move the file or files to the new location.

7.       Restart the instance of Microsoft SQL Server. For example, run

NET START MSSQLSERVER or

NET START MSSQL$instancename

8.       Verify the file change by running the sp_helpfile.

After Moving All System Databases:

If we have moved all of the system databases to a new drive or volume or with a different drive letter/folder, we need to change the SQL Server Agent log path. If we do not update this path, SQL Server Agent will fail to start.

To change the SQL Server Agent Log Path:

1.     From SQL Server Management Studio, in Object Explorer, expand SQL Server Agent.

2.     Right-click Error Logs and click Configure.

3.     In the Configure SQL Server Agent Error Logs dialog box, specify the new location of the SQLAGENT.OUT file.

 

Example of relocating msdb and model database:

In this example, we will move the msdb and model database to a new location, which is “H:\SQLSystemDB”.  Prior to stop SQL Server Service collect the logical and OS file name (use sp_helpfile)

To execute the ALTER DATABASE command we can use sqlcmd.

For msdb:

ALTER DATABASE msdb

MODIFY FILE( NAME = MSDBData , FILENAME = 'H:\SQLSystemDB\MSDBData.mdf' )

ALTER DATABASE msdb

MODIFY FILE( NAME = MSDBLog , FILENAME = 'H:\SQLSystemDB\MSDBLog.ldf' )

GO

Figure # 4: msdb database relocation:

For model:

ALTER DATABASE model

MODIFY FILE( NAME = modeldev , FILENAME = 'H:\SQLSystemDB\model.mdf' )

ALTER DATABASE model

MODIFY FILE( NAME = modellog , FILENAME = 'H:\SQLSystemDB\modellog.ldf' )

GO

Figure # 5: model database relocation:

Restoring msdb and model database:

So far restoring the msdb and model database from backup is somehow misleading (??). Thus, a common trick is to restore the model and msdb database in a different instance of SQL Server which has same Edition/Build and then copy back the mdf and ldf to the folder.

Part three: tempdb startup issue:

Relocating tempdb is one of the simplest task and it can be done when SQL Server service is up and running. However, reallocating tempdb data files will not be in effect until the SQL Server Service restarts. But if we add data files to tempdb this will take effect as soon as the creation has been done.

SQL Server startup issue may occur due to following reasons:

1.       Drive failure of tempdb,

2.       Unavailability or change of folder/drive

Resolving tempdb issue:

To rectify the startup issue with SQL Server due to unavailability of tempdb, following are the steps

1.       Open the command prompt with administrative privilege

2.       Start the SQL Server service with following command

Default instance: NET START MSSQL SERVER /f /T3608

Named Instance: NET START MSSQL$InstanceName /f /T3608

3.       Open the sqlcmd command window

Default instance: C:\> sqlcmd

Named Instance: C:\> sqlcmd  -SComputername\InstanceName

4.       We can query the sys.master_files to get the logical name of each data file and corresponding physical name with location of each data file. Execute following command:

SELECT  name ,

        physical_name

FROM    sys.master_files

WHERE   database_id = 2

5.       In the sqlcmd window run the ALTER DATABASE command

Syntax:

ALTER DATABASE database_name

MODIFY FILE( NAME = logical_name , FILENAME = 'new_path\os_file_name' )

Example for tempdb (one data and one log):

ALTER DATABASE tempdb

MODIFY FILE( NAME = tempdev , FILENAME = 'H:\tempdb\tempdb.mdf' )

ALTER DATABASE tempdb

MODIFY FILE( NAME = templog , FILENAME = 'H:\tempdb\templog.ldf' )

GO

6.       Restart SQL Server as usual without any switch and trace flag.

Relocating tempdb data files:

Here are two examples for “how to move tempdb” to a different location.

Following is the example (figure # 6) to move tempdb from its default location “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA” to a new location “H:\tempdb” in SQL Server 2012.

Figure # 6: tempdb move to a new location

Following is the example (figure # 7) to move tempdb from the location “H:\tempdb” to a new location “H:\tempdbNew” in SQL Server 2012, where we have 4 data files and 1 log file.

Figure # 7: tempdb move to a new location (muiltiple data files)

Figure # 7A: tempdb move to a new location (muiltiple data files)

Figure # 7B: tempdb move to a new location (muiltiple data files)

Reference and Read More:
Move System Databases

http://msdn.microsoft.com/en-ca/library/ms345408.aspx

The SQL Server Instance That Will not Start (written by Gail Shaw)

https://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/

Rebuild System Databases
http://msdn.microsoft.com/en-us/library/dd207003.aspx

Rebuilding master database:

SQL 2005: http://msdn.microsoft.com/en-us/library/ms144259(v=sql.90).aspx

SQL 2008: http://msdn.microsoft.com/en-us/library/dd207003(v=sql.100).aspx