Guidelines for choosing antivirus software to run on the computers that are running SQL Server

These are the very general guidelines to help you decide which kind of antivirus software to run on the computers that are running Microsoft SQL Server and I personally recommend to perform some testing before and after the installation of antivirus to determine if there are any performance issues as the antivirus software will utilize some system resources to perform their duties.

To drill own, basically there are two kinds of servers, one is High Risk Servers which are generally exposed to public internet or which have open ports to the servers that are not behind firewall or which hosts file shares or HTTP services like IIS or Apache.
And the servers which do not meet the above criteria of High Risk servers will fall under the category of Low Risk Server although not always.

When you configure antivirus software on a server running SQL Server, make sure to exclude the following

File Extensions and Directories

• SQL Server database files
• .mdf
• .ndf
• .ldf
• SQL Server backup files
• .bak
• .trn
• Full-Text catalog files
• Default instance: Program Files\Microsoft SQL Server\MSSQL\FTDATA
• Named instance: Program Files\Microsoft SQL Server\MSSQL$instancename\FTDATA
• Trace Files
• .trc
• Audit files
• .sqlaudit
• Query files
• .sql
• Directory that holds Analysis Services Data and Temporary files
• Directory that holds Analysis Services Log files
• Analysis Services backup files

Processes

• SQLServr.exe - Process related to SQL Server Database engine
• ReportingServicesService.exe  - Process related to SQL Server Reporting Services
• MSMDSrv.exe  - Process related to SQL Server Analysis Services

We can also run antivirus software on a SQL Server cluster. For this we have make sure that the antivirus software we choose supports cluster. 

When running antivirus on cluster make sure to exclude 

• Q:\ (Quorum drive)
• C:\Windows\Cluster

Doing this improves the performance of the files and helps make sure that the files are not locked when the SQL Server service must use them. However, if these files become infected, the antivirus software cannot detect the infection. So if you suspect a virus infection then you have to scan the entire system without any exclusions.

Reference: http://support.microsoft.com/kb/309422

Fix SSMS Error - The automatically saved settings file ‘\\Settings\CurrentSettings-YYYY-MM-DD.vssettings’ cannot be found.

When you try to Open or Close SQL Server Management Studio after an upgrade or fresh installation of SQL Server 2008 R2 you might get the below error which states,

The automatically saved settings file '\\Settings\CurrentSettings-YYYY-MM-DD.vssettings' cannot be found. You can change this file on the 'Import and Export Settings' Tools Options page. The IDE will use your most recent  settings file for this session.

However there is no option "Import and Export Settings" in SQL Server Management Studio.
So to Fix this error,

1. Open Registry Editor
2. Navigate to the location HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell
3. Edit the value of the Reg Key "VisualStudioLocation" to  %USERPROFILE%\Documents\SQL Server Management Studio
4. Close Registry Editor

Now when you open or close SQL Server Management Studio, You will not get the error.

Microsoft SQL Server 2008 R2 Service Pack 2 is now available for download

SQL Server 2008 R2 Service Pack 2 (SP2) is now available for download. This Service Pack contains Cumulative Update 1 to 5 from SQL Server 2008 R2 SP1. The package can be used to upgrade the following SQL Server 2008 R2 editions: 

• SQL Server 2008 R2 Parallel Computing Edition
• SQL Server 2008 R2 Datacenter Edition
• SQL Server 2008 R2 Enterprise Edition and Developer Edition
• SQL Server 2008 R2 Standard Edition
• SQL Server 2008 R2 Web Edition
• SQL Server 2008 R2 Workgroup Edition
• SQL Server 2008 R2 Express Edition

And it can be downloaded from the here.

Phases of Database recovery

From my previous post “What happens when a SQL Server instance is restarted?” we know what activities will be carried out when the SQL Server instance gets restart request.

Now, it’s time to understand what recovery phases the database will undergo.
The databases undergo recovery phases in two scenarios

1. When the SQL server  or service is restarted
2. When the database is being restored.

There are 3 Phases of Recovery and are based on the last checkpoint in the transaction log.

Recovery Phases - Drill Down

Recovery Phases - Graphical

What happens when a SQL Server instance is restarted?

Have you ever wondered or got curious to know what will happen or what are the activities carried out when an SQL Server instance get a restart request?

SQL Server instance will stop and then start again. Yes, this is obvious and there are lot more things that happen when a restart command is issued on an SQL Server instance.

With this post I am trying to list down the activities that happen during the restart of a SQL server instance, may be the sequence is not correct and the list might be incomplete. In that case, you can always correct me and complete the list. J

First of all, the service stops and before the service stops,

1. Checkpoint is issued on all databases
2. Check for the jobs that are running and stop them
3. Release the locks on database files to Operating System
4. Release the memory used by SQL Server instance
5. Flush the metadata collected for DMV’s and DMF’s
6. Record an event in default trace and event viewer regarding the SQL Server instance shutdown

During the starting of SQL Server service,

1. The service is authenticated by verifying the credentials provided in the logon account and the service is started.
2. Startup parameters (master database data file path, log file path and error log file path, etc… if any) are verified
3. The port on which SQL server is listening is opened.
4. Memory is allocated
5. Read master database metadata for information about user databases
6. Attach all the user database
7. Undergo database recovery phases (Analysis, redo and undo phases.)
8. Obtain lock on the database files
9. tempdb files are allocated based on the initial size settings and other setting like collation are copied from model database.
10. An entry to default trace is recorded about the start of SQL Server instance
11. All the events are recorded to SQL Server log file and event viewer
12. Accept connections to databases
13. Start the metadata collection for DMV’s and DMF’s
14. Recompile Stored Procedures