Click here to monitor SSC
  • Av rating:
  • Total votes: 50
  • Total comments: 0
Glenn Berry

Provisioning a New SQL Server Instance – Part Three

18 September 2012

Once you've installed and configured SQL Server 2012, there are some tasks that should be done to ensure that  maintenance, monitoring and alerting systems are in place to keep the instance running smoothly. Glenn Berry explains how.

In Part Two of this series, I covered the key steps that were necessary in order to properly install and configure an instance of SQL Server 2012. In this installment, I will discuss the next set of steps that are required after you have already completed the steps in Part Two. The idea here is to make sure that your new instance is completely ready to be used and that you have done everything necessary to help keep your instance running smoothly in the future.

Confirming Network Connectivity

The first order of business is to ensure that you have network connectivity to your new instance of SQL Server. You need to make sure that the appropriate network protocols are enabled on the SQL server instance. You can do this by going to the Start Menu, then going to SQL Server 2012, and then Configuration Tools, and selecting SQL Server Configuration Manager. This will open up the SQL Server Configuration Manager application. Next, you need to click on SQL Server Network Configuration in the left-hand pane, and then click on Protocols for MSSQLSERVER (for a default instance of SQL Server). This will display the available network protocols in the right pane of the window, as shown in Figure 1.

In most cases your applications are going to be using the TCP/IP protocol, so you need to make sure that TCP/IP is enabled so that other machines can communicate with your database server over the network. Depending on what edition of SQL Server you have installed, it may already be enabled, but it is always a good idea to confirm that it is enabled.

SQL Server Configuration Manager Network Protocols

Figure 1: SQL Server Configuration Manager Network Protocols

If you see that it is disabled in SQL Server Configuration Manager (like you see in Figure 1), you are going to need to enable it by right-clicking on it and selecting Enable. After you do this, you will get a warning message that tells you that you will have to restart the appropriate SQL Server Service before the change will take effect, as seen in Figure 2.

Service Change Warning Message

Figure 2: Service Change Warning Message

This warning message is true, so you will need to restart the SQL Server Service for the correct instance of SQL Server that you are working on before TCP/IP will be enabled.

You can do this from SQL Server Configuration Manager by clicking on SQL Server Services in the left-hand pane of the SQL Server Configuration Manager window. This will display all of the installed SQL Server related services for all of the SQL Server instances on the machine in the right-hand-pane of the window. Then you can right-click on the correct service (which should be called SQL Server (MSSQLSERVER) for a default instance) and select Restart to restart the SQL Server Service. Make sure you select the correct service and make sure you are aware that any connections that might exist (which should be none in this case) will be dropped when you do this. After enabling TCP/IP you will want to confirm that you can connect to your instance from a remote machine, such as your workstation or a web server. You can quickly and easily do this on any Windows machine using an old DBA technique with a Microsoft Data Link file.

Confirming SQL Server Connectivity

You simply can right-click on the desktop and select New – Text Document to create an empty text file. Then, you need to right-click on the file and select Rename, and change the file extension from .txt to .udl to create an empty Microsoft Data Link file. If you don’t see any file extensions on your files, you can go into Folder and Search options in Windows Explorer and disable the option to hide file extensions.

After you have created a Microsoft Data Link File, simply double-click on it to open up the Data Link Properties dialog as shown in Figure 3.

Data Link Properties – Connection Tab

Figure 3: Data Link Properties – Connection Tab

For some reason known only to Microsoft, this dialog always opens on the Connection tab (as shown in Figure 3), but you actually need to click on the Provider tab to get to what you see in Figure 4.

Data Link Properties – Provider Tab

Figure 4: Data Link Properties – Provider Tab

Depending on what OLE DB Providers are installed on the machine that you want to test, you may see a different list than what is shown in Figure 4. For this test, you should select the Microsoft OLE DB Provider for SQL Server, and then go back to the Connection tab. You need to enter a server name (or IP address), and then a set of valid credentials with either Windows NT Integrated security (which will use your Windows credentials) or a User name and password using SQL Server authentication as you see in Figure 5.

Data Link Properties – Provider Tab with Connection Information

Figure 5: Data Link Properties – Provider Tab with Connection Information

After you have done this, you should be able to select a database on the server and then click on the Test Connection button to make a connection to that database on that server. If all is well, you should be rewarded by the success dialog as shown in Figure 6.

Microsoft Data Link – Test connection succeeded dialog

Figure 6: Microsoft Data Link – Test connection succeeded dialog

This quick, simple test lets you confirm that the remote machine you tested can connect to the SQL Server instance and database. It proves that the SQL Server service is running and has an appropriate network protocol enabled. It also proves that you have network connectivity that is not being blocked by a firewall and that the credentials that you used have rights to connect to the instance and database. This quick technique is very useful for verifying connectivity and basic functionality from any machine, whether or not it has SQL Server Management Studio or any other development tools installed.

Setting Up Monitoring and Automation

In order to make your SQL Server instance more resilient and reliable, you need to take advantage of the built-in features for detecting problems and notifying someone that something is wrong in an automated fashion. One of the first steps in this effort should be to setup Database Mail, so that you can have SQL Server send email notifications when problems occur. You can use the Database Mail Configuration Wizard in SSMS to do this.

You also should make sure that the SQL Server Agent service is configured with its Start Mode set to Automatic, so that it will automatically start whenever Windows starts. You can do this using SQL Server Configuration Manager, as shown in Figure 7.

SQL Server Agent Properties

Figure 7: SQL Server Agent Properties

The next important step is to create an Operator in the Operators folder under SQL Server Agent in Object Explorer in SSMS. You can go to the Operators folder, right-click, and select New Operator… to open the New Operator dialog as shown in Figure 8. Before you do this, you should make sure that you have your email administrator create an email distribution group that you can use as an email address for your operator instead of using a single real person’s email address. This will let multiple people see the email notifications that go out to this Operator. You can also setup Pager information for this operator which can send text messages to smart phones or actually activate an on-duty pager.

New Operator dialog

Figure 8: New Operator dialog

Setting Up SQL Server Agent Alerts

Now that you have SQL Server Agent running, Database Mail setup, and a SQL Server Agent Operator created, it is time to create some SQL Server Agent Alerts for some of the most critical errors that require fast notification and action. These include Severity 19 through Severity 25 errors and Error 825. An example T-SQL script to create these SQL Server Agent Alerts is shown in Listing 1.

-- Add important SQL Agent Alerts
-- Change Alert names and operator_name as needed
-- Glenn Berry
-- SQLskills
-- 9-10-2012

USE [msdb];
GO

-- Change @OperatorName as needed
-- Alert Names start with the name of the server
DECLARE @OperatorName SYSNAME = N'SQLDBAs';
DECLARE @Sev19AlertName SYSNAME = N'Glenn2012 Alert - Sev 19 Error: Fatal Error in Resource';
DECLARE @Sev20AlertName SYSNAME = N'Glenn2012 Alert - Sev 20 Error: Fatal Error in Current Process';
DECLARE @Sev21AlertName SYSNAME = N'Glenn2012 Alert - Sev 21 Error: Fatal Error in Database Process';
DECLARE @Sev22AlertName SYSNAME = N'Glenn2012 Alert - Sev 22 Error Fatal Error: Table Integrity Suspect';
DECLARE @Sev23AlertName SYSNAME = N'Glenn2012 Alert - Sev 23 Error: Fatal Error Database Integrity Suspect';
DECLARE @Sev24AlertName SYSNAME = N'Glenn2012 Alert - Sev 24 Error: Fatal Hardware Error';
DECLARE @Sev25AlertName SYSNAME = N'Glenn2012 Alert - Sev 25 Error: Fatal Error';
DECLARE @Error825AlertName SYSNAME = N'Glenn2012 Alert - Error 825: Read-Retry Required';



-- Sev 19 Error: Fatal Error in Resource
EXEC msdb.dbo.sp_add_alert @name = @Sev19AlertName,
              
@message_id=0,
              
@Severity=19,
              
@enabled=1,
              
@delay_between_responses=900,
              
@include_event_description_in=1,
              
@job_id=N'00000000-0000-0000-0000-000000000000';

EXEC msdb.dbo.sp_add_notification @alert_name = @Sev19AlertName,
@operator_name=@OperatorName, @notification_method = 1;


-- Sev 20 Error: Fatal Error in Current Process
EXEC msdb.dbo.sp_add_alert @name = @Sev20AlertName,
              
@message_id=0,
              
@Severity=20,
              
@enabled=1,
              
@delay_between_responses=900,
              
@include_event_description_in=1,
              
@job_id=N'00000000-0000-0000-0000-000000000000'

EXEC msdb.dbo.sp_add_notification @alert_name = @Sev20AlertName,
@operator_name=@OperatorName, @notification_method = 1;


-- Sev 21 Error: Fatal Error in Database Process
EXEC msdb.dbo.sp_add_alert @name = @Sev21AlertName,
              
@message_id=0,
              
@Severity=21,
              
@enabled=1,
              
@delay_between_responses=900,
              
@include_event_description_in=1,
              
@job_id=N'00000000-0000-0000-0000-000000000000'

EXEC msdb.dbo.sp_add_notification @alert_name = @Sev21AlertName,
@operator_name=@OperatorName, @notification_method = 1;


-- Sev 22 Error: Fatal Error Table Integrity Suspect
EXEC msdb.dbo.sp_add_alert @name = @Sev22AlertName,
              
@message_id=0,
              
@Severity=22,
              
@enabled=1,
              
@delay_between_responses=900,
              
@include_event_description_in=1,
              
@job_id=N'00000000-0000-0000-0000-000000000000'

EXEC msdb.dbo.sp_add_notification @alert_name = @Sev22AlertName,
@operator_name=@OperatorName, @notification_method = 1;


-- Sev 23 Error: Fatal Error Database Integrity Suspect
EXEC msdb.dbo.sp_add_alert @name = @Sev23AlertName,
              
@message_id=0,
              
@Severity=23,
              
@enabled=1,
              
@delay_between_responses=900,
              
@include_event_description_in=1,
              
@job_id=N'00000000-0000-0000-0000-000000000000'

EXEC msdb.dbo.sp_add_notification @alert_name = @Sev23AlertName,
@operator_name=@OperatorName, @notification_method = 1;


-- Sev 24 Error: Fatal Hardware Error
EXEC msdb.dbo.sp_add_alert @name = @Sev24AlertName,
              
@message_id=0,
              
@Severity=24,
              
@enabled=1,
              
@delay_between_responses=900,
              
@include_event_description_in=1,
              
@job_id=N'00000000-0000-0000-0000-000000000000'

EXEC msdb.dbo.sp_add_notification @alert_name = @Sev24AlertName,
@operator_name=@OperatorName, @notification_method = 1;


-- Sev 25 Error: Fatal Error
EXEC msdb.dbo.sp_add_aalert @name = @Sev25AlertName,
              
@message_id=0,
              
@Severity=25,
              
@enabled=1,
              
@delay_between_responses=900,
              
@include_event_description_in=1,
              
@job_id=N'00000000-0000-0000-0000-000000000000'

EXEC msdb.dbo.sp_add_notification @alert_name = @Sev25AlertName,
@operator_name=@OperatorName, @notification_method = 1;



-- Error 825: Read-Retry Required
EXEC msdb.dbo.sp_add_alert @name = @Error825AlertName,
              
@message_id=825,
              
@Severity=0,
              
@enabled=1,
              
@delay_between_responses=900,
              
@include_event_description_in=1,
              
@category_name=N'[Uncategorized]',
              
@job_id=N'00000000-0000-0000-0000-000000000000';

EXEC msdb.dbo.sp_add_notification @alert_name = @Error825AlertName,
@operator_name=@OperatorName, @notification_method = 1;
GO

Listing 1: Creating Important SQL Server Agent Alerts

Adding Ola Hallengren’s Maintenance Solution

Rather than using the built-in SQL Server Maintenance Plan Wizard to create a number of different SQL Server Maintenance Plans that are hard to understand and troubleshoot, as well as being inefficient, you should strongly consider using Ola Hallengren’s free Maintenance Solution scripts, which are available at http://ola.hallengren.com/.

These scripts are very well tested and have been available since January 2008, with steady updates and improvements over the past nearly five years. Ola's scripts have won multiple awards, are used by tens of thousands of people across the globe, and SQLskills has many clients using them in production.

These scripts cover database backups (Full, Differential and Transaction Log) for user databases, and full backups for your system databases. They also handle index maintenance for all of your databases. Finally they handle database integrity checking for your user and system databases by running DBCC CHECKDB. They are configurable so you can change backup locations, exclude databases, etc.

You can simply download the MaintenanceSolution.sql script from Ola’s website, change the parameter values as needed, and then run the script to create eleven new SQL Server Agent jobs on your instance. After you create these jobs, you will want to add email notifications to each job so that your Operator is notified if a job fails, as shown in Figure 9.

Adding an E-Mail Notification to a SQL Server Agent Job

Figure 9: Adding an email Notification to a SQL Server Agent Job

You also need to add a job schedule to each of these eleven SQL Server Agent Jobs, based on your business requirements, infrastructure, and recovery point objective (RPO) and recovery time objective (RTO) goals. An example of this is shown in Figure 10.

Adding a Job Schedule to a SQL Server Agent Job

Figure 10: Adding a Job Schedule to a SQL Server Agent Job

If you don’t have clear RPO/RTO requirements from your business, you might start out with a job schedule like this:

Job Name Run Schedule
CommandLog Cleanup Every Sunday at 12:00AM
DatabaseBackup - SYSTEM_DATABASES – FULL Every day at 11:55PM
DatabaseBackup - USER_DATABASES – DIFF Every day at 12:00PM
DatabaseBackup - USER_DATABASES – FULL Every day at 12:00AM
DatabaseBackup - USER_DATABASES – LOG Every 30 minutes
DatabaseIntegrityCheck - SYSTEM_DATABASES Every Saturday at 8:00AM
DatabaseIntegrityCheck - USER_DATABASES Every Saturday at 9:00AM
IndexOptimize - USER_DATABASES Every Monday at 3:00AM
Output File Cleanup Every Sunday at 12:01AM
sp_delete_backuphistory Every Sunday at 12:02AM
sp_purge_jobhistory Every Sunday at 12:03AM

Summary

The third installment of this series has laid out a number steps that are required in order to ensure that your new instance of SQL Server is completely ready for use. It has also covered a number of steps that will make your instance easier to monitor and maintain. These include making sure you have Database Mail configured, making sure that SQL Server Agent is running and set to autostart, and making sure that you have an Agent Operator configured. Finally, we showed how to create SQL Server Agent Alerts and how to configure Ola Hallengren’s Maintenance Solution scripts.

Glenn Berry

Author profile:

Glenn Berry is a Principal Consultant with SQLskills. He has worked as a SQL Server professional for many years in a variety of roles, most recently as Database Architect for Avalara in Parker, CO.

Glenn has been a SQL Server MVP since 2007, and he has a whole collection of Microsoft certifications, including MCITP, MCDBA, MCSE, MCSD, MCAD, and MCTS, which proves that he likes to take tests. His expertise includes DMVs, high availability, hardware selection and configuration, and performance tuning. He is also an Adjunct Faculty member at University College - University of Denver, where has been teaching since 2000. He has completed the Master Teacher Program at Denver University - University College.

Glenn is heavily involved in the SQL Server community, and is a frequent speaker at user groups, SQL Saturdays, and the PASS Community Summit. He is the author of the book SQL Server Hardware, and he wrote chapters for SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2 books.

Glenn's blog is at http://sqlserverperformance.wordpress.com/ and he can be reached by email at glenn@SQLskills.com and on Twitter at GlennAlanBerry.

Search for other articles by Glenn Berry

Rate this article:   Avg rating: from a total of 50 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.
 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Highway to Database Recovery
 Discover the best backup and recovery articles on Simple-Talk, all in one place. Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.