Archive for January, 2013

How to resolve the 64-bit to 32-bit linked server error?

You have been requested by the Development team to create Linked Servers from your reporting server to 3 other SQL Servers instances for data access.  You have completed the request in a minute or less.  When you attempted to query the tables on the Linked Servers that you had created you received the following error:

OLE DB provider “SQLNCLI10″ for linked server “SERVERNAME” returned message “Unspecified error”.
OLE DB provider “SQLNCLI10″ for linked server “SERVERNAME” returned message “The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.”
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI10″ for linked server “SERVERNAME “. The provider supports the interface, but returns a failure code when it is used.

After further review, you have found that your reporting server is running SQL Server 2008 R2 Enterprise Edition 64-bit and the 3 SQL Server instances are running SQL Server 2000 Enterprise Edition 32-bit.

What can you do to resolve this problem to enable the Development team to use the Linked Servers as soon as you can?

First, you need to create the sp_tables_info_rowset_64 stored proc:

-- Create the sp_tables_info_rowset_64 stored proc in the master database
USE master
go
CREATE PROCEDURE sp_tables_info_rowset_64
@table_name SYSNAME,
@table_schema SYSNAME = NULL,
@table_type nvarchar(255) = NULL
AS
DECLARE @Result INT SET @Result = 0
EXEC @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type
GO

Then, you need to grant the execute permission on the stored proc created in the above step:

-- Grant the EXEC on the stored to the principal
USE master
go
GRANT EXEC ON sp_tables_info_rowset_64 TO PUBLIC -- replace PUBLIC with security account for the linked server

REFERENCES:

Issue with 64-bit SQL Server using SQL 2000 linked server
http://sqlblog.com/blogs/roman_rehak/archive/2009/05/10/issue-with-64-bit-sql-server-using-sql-2000-linked-server.aspx

What is Replication?

Replication is a form of high availability technology that’s similar to the publishing industry. You will hear and see terminologies such as Publishers, Subscribers, Distributors, Articles, Agents, and etc. Similar to Log Shipping and Database Mirroring, Replication is a per database rather than a per instance high availability technology. But, that’s where the similarities end. In Replication, you have more granular control over the specific database objects that will get copied or transferred over to the secondary database that’s located in a separate server across the street, across the river, or even across continents.

The common typical uses of replication can be:

  • Offload the reporting to the subscriber(s) to reduce the resource contentions on the OLTP servers.
  • Integrating and combining data from multiple sites or various geographically distant branch offices.
  • Integrating data from various database systems such as Oracle, MySQL, Sybase, PostgreSQL, and etc. or anything database system that can be communicated via OLE DB.
  • Increasing availability by replicating all the data on a server to another server so that in case the primary server fails, the applications and users can switch to the secondary server.

Let’s say your company is headquartered in New York City with branch offices throughout the countries in Europe, you wish to reduce the contention and reporting needs for the European offices from constantly querying the OLTP server that’s located in a top secret Data Center that’s located in New Jersey somewhat 30 minutes from Manhattan. What can you do?

Before we get too excited about traveling to London again and visiting all the branch offices throughout the European cities to setup all the required servers because you wish to work more closely with your colleagues in Europe, if the company approves, we need to first understand the terminologies, concepts, pre-requisites, setup requirements, and monitoring of replication before you book your flights and hotels for the long overdue road trips. Yes. You can complete the setup remotely. But, why not take this opportunity to have face time with your coworkers and work together as a team, check out the construction progress of the Shard, revisit the Buckingham Palace, and get your taste bud reacquainted with fish and chips and some of the great beer?

As we had stated from the beginning, Replication is similar to the publishing industry. It is essentially a process of copying and synchronizing data from the source database to the subscribing database(s) that are located in separate servers. It contains terminologies that you may or already very well familiar with:

  • Publisher: contains the source database(s).
  • Publication: the database objects to be published; it can be tables, views, and stored procedures.
  • Subscriber: the database that located in a separate server that will receive the publication changes.
  • Subscription: the process or method of receiving the publication.
  • Distributor: the server that delivers the publication to the subscriber(s). It can be on the same server as the Publisher. But, it is usually best for it to be located on a different remote server to reduce contentions.
  • Article: the tables, views, and stored procedures to be published.

Replication starts with the first or initial synchronization of the objects to be published from the Publisher to the Subscriber(s). Yes. You can have multiple Subscribers. The first or initial synchronization is called a snapshot. You would first define the articles to be published in the Publisher, then the snapshot (a point in time copy of the Publication) is created on the Publisher, and then the Distributor will deliver the snapshot to the Subscriber(s). When data is copied or transferred over to the Subscriber(s) from the Publisher, it is considered as synchronized. The process of synchronization can happen instantly or on a scheduled basis depending on the replication scenarios, type of replication, and the configurations.

The initial snapshot is stored in the snapshot folder of the Distribution server. The Distribution server can be located in the same server as the Publisher server or it can be located on a separate remote server. You must make sure you have sufficient disk space for the initial snapshot as it is the main reason for snapshot failures. In addition, you must make sure the snapshot agent account has permission to write to the snapshot folder.

There are 3 common replication scenarios:

  • Central Publisher: It is the most common form of replication. The setup involves with one Publisher, one Distributor that can be on the same server as the Publisher but preferably should be located in a different server, and one or more Subscriber(s).
  • Central Subscriber: The setup involves with multiple Publishers and one Subscriber. This setup is usually reserved for centralized reporting databases where you have multiple branch offices servers replicating to the centralized reporting database back in the headquarter office or at a specific location.
  • Publishing Subscriber: This is usually reserved for situations where there’s low-bandwidth between the Publisher and Subscriber. You can first publish to a subscriber, then the subscriber in turn distributes to others within its region where there’s higher bandwidth for network.

There are 4 types of replications that I will be exploring in the future blogs and articles. Here’s a brief overview of each:

  • Snapshot: provides the initial synchronization for all other types of publications (transactional, peer-to-peer, and merge). It involves copying the articles of the publication over to the subscriber(s) and the existing subscriber(s) will be overwritten. Usually reserved for small databases and for initial synchronizations.
  • Transactional: is the most common type of replication. Changes that occur on the Publisher can also applied on the subscriber(s) immediately.
  • Peer-to-Peer: is a type of transactional replication where the each of the servers involved in replication is both a Publisher and Subscriber usually for load-balancing proposes.
  • Merge: allows the Subscribers that need update to the same sets of data at various times then the changes will get sent back to the Publisher where the changes will get merged and then sent back to all other Subscribers.

When the Distribution server is created, a new database called the Distribution database will be automatically created. This database will be created on the Distribution server. It serves as the store-and forward database. It holds all transactions from the Publisher waiting to be distributed to the Subscribers. The transactions are held in this Distribution database until they are distributed to the subscribers successfully. At a specific time interval, the transactions are purged from the distribution database.

When you create a publication to be subscribed, you have the option of either having the data pushed to the subscribers or have the subscribers pull the data when they are needed. For the push subscriptions, the Distribution Agent, the agent that moves the transactions to the subscribers will be located on the Distribution server. For the pull subscriptions, the Distribution Agent, the agent that moves the transactions to the subscribers will be located on the Subscribers.

The Replication processes are maintained by the Replication agents. Each one has its core responsibilities in making Replication work and continues to function:

  • SQL Server Agent: all the agents perform the replication tasks via SQL Server Agent jobs. The SQL Server Agent manages these jobs.
  • Snapshot Agent: responsible for synchronization to make sure the Subscriber(s) have the same selected database schema and data as in the Publication(s) of the Publisher. After the process of synchronization, all updates are made through replication.
  • Log Reader Agent: moves transactions marked for replication from the published database to the distribution database.
  • Distribution Agent: moves transactions held in the distribution database out to the subscribers in push subscriptions. In pull subscriptions, the Distribution Agent runs on the Subscriber. Merge replication does not have a Distribution Agent. It has a Merge Agent instead.
  • Merge Agent: is utilized only for Merge replications. It moves and reconciles the data changes that occurred after the initial snapshot. It runs on the publication server for push subscriptions and on subscriber servers for pull subscriptions.
  • History Cleanup Agent: clears the agent history from the distribution database every 10 minutes by default.
  • Distribution Clean Agent: clears the replicated transactions from the distribution database every 72 hours by default.
  • Expired Subscription Cleanup Agent: removes the expired subscriptions from the published databases. Runs once per day.
  • Reinitialize Subscriptions Having Data Validation Failures Agents: detects the subscriptions that failed data validation and marks them for re-initialization. It is not scheduled by default and it has to be run manually. Yes. This can mean that you will likely need a new snapshot applied to the subscribers.
  • Replication Monitoring Refresher for Distribution Agent: refreshes the SQL Server Replication Monitor on a scheduled interval.
  • Replication Agents Checkup Agent: make sure the replication agents are logging history as it is critical to have the history for troubleshooting.

In the subsequent or future blogs and articles, I will explore and document the major replication types, how to monitor them, and other critical information that I need to know and use on a daily basis as a SQL Server DBA supporting replication.

How to remove Database Mirroring?

Due to recent changes, your Network Engineering team has decided to modify the network with segmentation changes. Unfortunately due to their workload, they were too busy to notify you that the Mirror server will be located in a separate network. Yes. That’s correct. Your Principal server will no longer be able to communicate with your Mirror server. You have been alerted via the alerts that you had set up for Database Mirroring monitoring in addition to the log space alert that you had setup that the mirroring state description is DISCONNECTED and your log space utilization for the Principal database is almost or 100% full. You had thought about switching it to high performance mode by setting the safety option to off for database mirroring so that it will be changed from synchronous mode to asynchronous mode so that the transactions doesn’t need the Mirror server to commit before committing in the Principal server. But, that doesn’t work because the Principal server can’t communicate the Mirror server as it is a requirement for the switching the safety options. What can you do?

In this scenario, you have to remove Database Mirroring and then set it up again when a Mirror server is made available. When you remove Database Mirroring, each partner server instance will be left with a separate copy of the database. The mirror database will be left in the restoring state. To drop the mirror database, you must first restore it using the WITH RECOVERY option. The following are the overview steps before we move on to the implementation steps:

1. Connect to the Principal or the Mirror server.
2. Remove Database Mirroring.
3. In the mirror server, recover the mirrored database to bring it out of the restoring state.
4. Drop the former mirrored database.

Implementation steps:

1. Connect to the Principal or the Mirror server.
2. Remove Database Mirroring.

 -- To remove database mirroring, connect to either partner and then set the partner off
 ALTER DATABASE AdventureWorks -- replace your with your Principal or Mirror database here
 SET PARTNER OFF
 

3. In the Mirror server, recover the mirrored database to bring it out of the restoring state.

 -- Connect to the mirror server, bring the formerly mirrored database out of restoring mode
 RESTORE DATABASE AdventureWorks -- replace your with your Mirror database here 
 WITH RECOVERY

4. Drop the former mirrored database.

-- Drop the formerly mirrored database
 DROP DATABASE AdventureWorks -- replace your with your Mirror database here 

REFERENCES:

Removing Database Mirroring
http://msdn.microsoft.com/en-us/library/ms180801(v=sql.105).aspx

How to: Remove Database Mirroring
http://msdn.microsoft.com/en-us/library/ms189112(v=sql.105).aspx

How to query a mirrored database using a snapshot?

If you looked at the mirrored database in the Mirror server, you will find that the mirrored database has the keywords “(Mirror, Synchronized / Restoring…)” appended to it. As we all know, if a database is restoring, you can’t read from it. Then, how can you query against it if you wanted to run reports against or just to check if the updates from the Principal database did actually get mirrored over to the mirrored database? Yes. You can if you first create a database snapshot and then query against the snapshot.
The keyword snapshot may have given away or hinted on what it means. A database snapshot is a read-only copy of a database that is taken at the point of time when the snapshot is taken. It is similar to taking a photo of yourself when you were twenty-two years old. You wish you were always twenty-two whenever you looked at the photo of which remind you of your fond memories of your younger days. But, again, it is only a photo, you have aged since then. The hard-partying days are over for you. Let’s get focused on SQL Server. Here are some facts about the database snapshots:

  • Available only for the Enterprise and Developer editions of SQL Server 2005+
  • Must be created in the same instance as the source
  • You can create multiple database snapshots for a database
  • Usually used for reporting, restore point, and just for testing purposes
  • You must use T-SQL to create a database snapshot
  • Read-only copy of the source
  • Use sparse files to store data

Sparse files are a feature of the NTFS file system. When created initially, a spare file takes up little disk space. It grows as changes in the source database increases. When you create a snapshot, the data pages are not copied over to the snapshot, when the data pages become dirty or changes occurred then the data pages will get copied over to the database snapshot. It is similar to the photo you had taken when you were twenty-two. You were young and looked great on the photo. But, it is only a snapshot. As you age, you gain more experience and more mileage on your body. Let’s just say things also changed, shall we?

In the following steps, we will create a database snapshot for the mirrored database and then query against it. Here are the overview steps before the implementation steps as always:
1. Query against the system view sys.master_files to view the logical file name of your mirrored database.
2. Make sure you have enough disk space in your server.
3. Create the snapshot.
4. Query against the snapshot.
5. Drop the snapshot.

Implementation steps:
1. Query against the system view sys.master_files to view the logical file name of your mirrored database.

-- Find the logical file name for the data file for the mirrored database
-- Make a note of it as it will be need for the creation of the snapshot
SELECT name AS LogicalName
, type_desc
, physical_name
FROM sys.master_files
WHERE DB_NAME(database_id) = 'AdventureWorks' -- replace with your mirrored database here

2. Make sure you have enough disk space in your server.

-- Make sure you have enough disk space
XP_FIXEDDRIVES

3. Create the snapshot.

-- Create the snapshot
CREATE DATABASE AdventureWorks_dbss01242013 ON -- replace with your mirrored database here
(
NAME = AdventureWorks_Data
, FILENAME = 'C:\Snapshot\AdventureWorks_dbss01242013.ss'
)
AS SNAPSHOT OF AdventureWorks -- replace with your mirrored database here
GO

4. Query against the snapshot.

-- You can query the database snapshot similar to how you query a regular database
USE AdventureWorks_dbss01242013
GO
SELECT *
FROM HumanResources.Employee
GO

5. Drop the snapshot.

-- Drop a database snapshot
USE master
GO
DROP DATABASE AdventureWorks_dbss01242013

REFERENCES:

How to: Create a Database Snapshot
http://msdn.microsoft.com/en-us/library/ms175876(v=sql.105).aspx

Understanding Sparse File Sizes in Database Snapshots
http://msdn.microsoft.com/en-us/library/ms175823(v=sql.105).aspx

Drop a Database Snapshot
http://msdn.microsoft.com/en-us/library/ms190220(v=sql.105).aspx

How to monitor Database Mirroring?

To view the current status of the Database Mirroring, you can open the Database Mirroring Monitor by right-clicking the Principal database > Tasks > Launch Database Mirroring Monitor… You can see an example of the screen-shot here. In the Database Mirroring Monitor, you can see the Principal and Mirror servers, mirroring state, unsent log, unrestored log, and other valuable information about the Database Mirroring status. Wouldn’t it be nice, instead of having to get in front of your laptop and open up the Database Mirroring Monitor to check the Database Mirroring status, you will only get an email notification only if there are issues that will require your manual intervention to troubleshoot? There are a number of ways to monitor the status of Database Mirroring. Please do share with me if you have better or alternative solutions. The possible alternatives can be WMI events, threshold events that can be set in the Database Mirroring Monitor, extended events, and/or powershell.

Here’s an overview of the two possible options:

1. Query the system view master.sys.database_mirroring for the mirroring state description for the databases that are in the principal or mirror roles. If their states are not in synchronized or synchronizing, then send out alert. You can schedule a SQL Server Agent job to run on an hourly basis or at a specific time interval as per your requirements.

2. Query the system view master.sys.dm_os_performance_counters for the Unsent log and Unrestored log if either one of them is greater than 100 MB, then send out alert. You can schedule a SQL Server Agent job to run on an hourly basis or at a specific time interval as per your requirements.

Implementation steps with plain vanilla HTML report to be sent via database mail. You can add cosmetic works with coloring and formatting if needed:

1. Query the system view master.sys.database_mirroring for the mirroring state description for the databases that are in the principal or mirror roles. If their states are not in synchronized or synchronizing, then send out alert. You can schedule a SQL Server Agent job to run on an hourly basis or at a specific time interval as per your requirements.

-- Querying the system view sys.database_mirroring 
-- for the principal or mirror databases that are not synchronized or synchronizing
IF EXISTS ( 
	SELECT @@SERVERNAME as InstanceName
	, DB_NAME(dbm.database_id) as DatabaseName
	, dbm.mirroring_role_desc
	, mirroring_state_desc 
	FROM  master.sys.database_mirroring dbm
	WHERE  dbm.mirroring_role_desc IN ('PRINCIPAL','MIRROR') 
		AND dbm.mirroring_state_desc NOT IN ('SYNCHRONIZED','SYNCHRONIZING')
)		
BEGIN
	-- declare the variables for the database mail and message formatting
	DECLARE @IsDBMailEnabled BIT
	, @MailSubject VARCHAR(123)
	, @MailProfile VARCHAR(123)
	, @Subject VARCHAR (123)
	, @Recipients VARCHAR (123)
	, @TableHTML VARCHAR(MAX)
	
	SET @Recipients = 'email@company.com' -- replace with your recipient(s) here
	
	SELECT @IsDBMailEnabled = CONVERT(INT, ISNULL(value, value_in_use)) 
	FROM  sys.configurations
	WHERE name LIKE 'Database Mail XPs'

	IF @IsDBMailEnabled = 0
	RAISERROR('Warning: Database mail is disabled. Database mail is required to send out reports', 12, 1)

	ELSE		
		BEGIN
			SELECT @TableHTML = 
			'<html><table border = "1">' +
			'<tr>
				<th>Instance Name</th>
				<th>Database Name</th>
				<th>Mirroring Role</th>
				<th>Mirroring State Desc</th>
			 </tr>'
			 
			 SELECT @TableHTML = @TableHTML + 			
				'<tr>' +
			    '<td>' + @@SERVERNAME + '</td>' +
		        '<td>' + DB_NAME(dbm.database_id) + '</td>' +
				'<td>' + dbm.mirroring_role_desc + '</td>' + 
				'<td>' + mirroring_state_desc + '</td></tr>'
			FROM  master.sys.database_mirroring dbm
			WHERE  dbm.mirroring_role_desc IN ('PRINCIPAL','MIRROR') 
				AND dbm.mirroring_state_desc NOT IN ('SYNCHRONIZED','SYNCHRONIZING')
				
			SELECT @TableHTML = @TableHTML + '</table></HTML>'		
	
			/*Sending mail*/
			SELECT @MailSubject = 'Database Mirroring Status: ' + CONVERT(VARCHAR(50),@@servername)

			-- Find the database mail profile name
			create table #dbmail_profile
			(
			profileid int null,
			profilename varchar(125) null,
			accountid int null,
			accountname varchar(125) null,
			sequencenumber int null
			)
			insert #dbmail_profile
			EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

			select @MailProfile = profilename
			from #dbmail_profile
			where sequencenumber = 1

			drop table #dbmail_profile

			EXEC msdb.dbo.sp_send_dbmail
				@profile_name = @MailProfile,
				@recipients = @Recipients,
				@subject = @MailSubject,
				@body_format = 'HTML',
				@body = @TableHTML;														
		END	
END

2. Query the system view master.sys.dm_os_performance_counters for the Unsent log and Unrestored log if either one of them is greater than 100 MB, then send out alert. You can schedule a SQL Server Agent job to run on an hourly basis or at a specific time interval as per your requirements.

-- Querying the sys.dm_os_performance_counters for database mirroring status
-- Log Send Queue KB: In the Database Mirroring Monitor, it is the Unsent log
-- Redo Queue KB: In the Database Mirroring Monitor, it is the Unrestored log
-- Redo Bytes/sec: In the Database Mirroring Monitor, it is the Current restore rate
IF EXISTS (
	SELECT pc.instance_name as DatabaseName -- principal database name
	, pc.counter_name
	, pc.cntr_value
	FROM master.sys.dm_os_performance_counters pc
	WHERE pc.object_name like '%Database Mirroring%'
		AND pc.instance_name in (	SELECT DB_NAME(dbm.database_id) as DatabaseName
									FROM  master.sys.database_mirroring dbm
									WHERE  dbm.mirroring_role_desc IN ('PRINCIPAL','MIRROR') ) 
		AND ( (pc.counter_name = 'Log Send Queue KB' AND pc.cntr_value >= 102400) -- Unsent log >= 100 MB
			OR (pc.counter_name = 'Redo Queue KB' AND pc.cntr_value >= 102400)) -- Unrestored log >= 100 MB
		AND (pc.counter_name = 'Redo Bytes/sec')
)	
BEGIN
	-- declare the variables for the database mail and message formatting
	DECLARE @IsDBMailEnabled BIT
	, @MailSubject VARCHAR(123)
	, @MailProfile VARCHAR(123)
	, @Subject VARCHAR (123)
	, @Recipients VARCHAR (123)
	, @TableHTML VARCHAR(MAX)
	
	SET @Recipients = 'email@company.com' -- replace with your recipient(s) here
	
	SELECT @IsDBMailEnabled = CONVERT(INT, ISNULL(value, value_in_use)) 
	FROM  sys.configurations
	WHERE name LIKE 'Database Mail XPs'

	IF @IsDBMailEnabled = 0
	RAISERROR('Warning: Database mail is disabled. Database mail is required to send out reports', 12, 1)

	ELSE		
		BEGIN
			SELECT @TableHTML = 
			'<HTML><table border = "1">' +
			'<tr>
				<th>Database Name</th>
				<th>Counter Name</th>
				<th>Counter Value</th>
			 </tr>'			
			
			SELECT @TableHTML = @TableHTML + '<tr>' +
			    '<td>' + pc.instance_name + '</td>' +
		        '<td>' + pc.counter_name + '</td>' +
				'<td>' + convert(varchar(25), pc.cntr_value) + '</td></tr>'  
			FROM master.sys.dm_os_performance_counters pc
			WHERE pc.object_name like '%Database Mirroring%'
				AND pc.instance_name in (	SELECT DB_NAME(dbm.database_id) as DatabaseName
											FROM  master.sys.database_mirroring dbm
											WHERE  dbm.mirroring_role_desc IN ('PRINCIPAL','MIRROR') )
				AND ( (pc.counter_name = 'Log Send Queue KB' AND pc.cntr_value >= 102400) -- Unsent log >= 100 MB
					OR (pc.counter_name = 'Redo Queue KB' AND pc.cntr_value >= 102400)) -- Unrestored log >= 100 MB
				AND (pc.counter_name = 'Redo Bytes/sec')

			SELECT @TableHTML = @TableHTML + '</table></HTML>'				
	
			/*Sending mail*/
			SELECT @MailSubject = 'Database Mirroring Monitor Status: ' + CONVERT(VARCHAR(50),@@servername)

			-- Find the database mail profile name
			create table #dbmail_profile
			(
			profileid int null,
			profilename varchar(125) null,
			accountid int null,
			accountname varchar(125) null,
			sequencenumber int null
			)
			insert #dbmail_profile
			EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

			select @MailProfile = profilename
			from #dbmail_profile
			where sequencenumber = 1

			drop table #dbmail_profile

			EXEC msdb.dbo.sp_send_dbmail
				@profile_name = @MailProfile,
				@recipients = @Recipients,
				@subject = @MailSubject,
				@body_format = 'HTML',
				@body = @TableHTML;														
		END	
END

REFERENCES:

sys.database_mirroring
http://msdn.microsoft.com/en-us/library/ms178655(v=sql.105).aspx

sys.dm_os_performance_counters
http://technet.microsoft.com/en-us/library/ms187743(v=sql.105).aspx

How to failover to the mirrored database?

To test or if you need to failover to the partner in Database Mirroring, you can follow the steps below. As always let’s understand the steps involved before getting our hands dirty. Please do read my explanation on how to setup Database Mirroring if needed.

Steps required:

1. Query the current Database Mirroring information such as the mirroring status, role description, safety level, partner name and instance name.
2. Alter the database to set the safety to full if needed then issue the failover command. Role reversal will happen. The Principal will become the Mirror and the Mirror will become the new Principal.
3. On the new Principal server, set the safety to off if needed (if the distance is far away in between the Principal and Mirror server).
4. Validate the role reversal.
5. Make sure all the required logins and users’ access privileges and SQL Server Agent jobs are transferred over to the Mirror server.
6. Collaborate with the application team to redirect the application to connect to the new Principal database.

Implementation steps:
1. On the Principal server, query the current Database Mirroring information such as the mirroring status, role description, safety level, partner name and instance name.

-- Querying the database mirroring status, role desc, safety level, partner name and instance
SELECT @@SERVERNAME as InstanceName
, DB_NAME(dbm.database_id) as DatabaseName
, dbm.mirroring_state_desc
, dbm.mirroring_role_desc
, dbm.mirroring_safety_level_desc
, dbm.mirroring_partner_name
, dbm.mirroring_partner_instance
FROM sys.database_mirroring dbm
WHERE dbm.mirroring_guid IS NOT NULL

2. Alter the database to set the safety to full if needed then issue the failover command. Role reversal will happen. The Principal will become the Mirror and the Mirror will become the new Principal.

-- To failover to the mirror database
-- Set the safety to full if necessary prior to failover
USE master
GO
ALTER DATABASE AdventureWorks -- replace your principal database name here
SET SAFETY FULL -- set safety to full if necessary
GO
ALTER DATABASE AdventureWorks -- replace your principal database name here
SET PARTNER FAILOVER -- failover to the partner
GO

3. On the new Principal server, set the safety to off if needed (if the distance is far away in between the Principal and Mirror server).

-- On the new principal server
-- Set the safety off to change database mirroring to high performance if the site distance is far apart
USE master
GO
ALTER DATABASE AdventureWorks -- replace your principal database name here
SET SAFETY OFF

4. Validate the role reversal. On the new Principal server, run the following query:

-- Querying the database mirroring status, role desc, safety level, partner name and instance
SELECT @@SERVERNAME as InstanceName
, DB_NAME(dbm.database_id) as DatabaseName
, dbm.mirroring_state_desc
, dbm.mirroring_role_desc
, dbm.mirroring_safety_level_desc
, dbm.mirroring_partner_name
, dbm.mirroring_partner_instance
FROM sys.database_mirroring dbm
WHERE dbm.mirroring_guid IS NOT NULL

5. Make sure all the required logins and users’ access privileges and SQL Server Agent jobs are transferred over to the Mirror server.

6. Collaborate with the application team to redirect the application(s) to connect to the new Principal database.

REFERENCES:

sys.database_mirroring
http://msdn.microsoft.com/en-us/library/ms178655(v=sql.105).aspx

How to setup Database Mirroring?

Similar to Log Shipping, Database mirroring is a form of high availability technology that’s per database as opposed to per instance in clustering. It is faster than log shipping and has options for automatic failover for the available editions and if a witness server is available. Data transfer is encrypted if both the principal and mirrored servers are in the same domain. Let’s get familiarized with the terminologies and concepts and understand the pre-requisites before getting our hands dirty on the actual setup steps.

Database mirroring requires the database to be mirrored to be in full recovery model as it is essentially a process of forwarding the transaction log from the principal database over to the mirrored database to be written in a separate server. Database mirroring comprises of two mandatory server roles and an optional third server role:

  • Principal server (mandatory): This is the source of the mirroring.
  • Mirror server (mandatory): This is the standby server that will receive the transaction logs from the principal server.
  • Witness server (optional): You will need this server only if you will be configuring automatic failover. It will continuously check to see if there are any failures with the principal server and fail over to the mirror server. You can use the free version of SQL Server: SQL Server Expression for this server role.

There are 3 operating modes that you must be aware of as the selection will have a dramatic influence on the performance of database mirroring and the protection level against disaster. We must first, however, need to understand the difference between synchronous and asynchronous operations:

  • Synchronous operations: Transactions can only be committed on the principal server after they are committed in the mirror server and after the mirror server sent acknowledgement back to the principal server. This is considered as a high-safety mode.
  • Asynchronous operations: Transactions can be committed on the principal server without waiting for the mirror server to receive and write the log to disk. This is considered as a high performance mode.

The 3 operating modes are as follows:

  • High safety with automatic failover mode (high availability with a witness server)
  • High safety without automatic failover mode (high protection without a witness server)
  • High performance mode

To communicate between the principal server and the mirror server, SQL Server requires endpoints for the communication process over the network. The database mirroring endpoint controls the port on which the server listens for database mirroring messages from other servers.

Pre-requisites:

  1. Identify your principal, mirror, and the witness server (if setting up automatic failover).
  2. Check to make sure your principal and mirror servers are at the same service pack.
  3. Identify the database to have database mirroring and make it is in full recovery model.
  4. Each of the server instances will require a database mirroring endpoint.
  5. Make sure the port numbers for the endpoints are open. Speak to your security administrator if necessary.
  6. Make sure the service accounts for database mirroring “cross-exist” if they are different. For example, a valid login from the principal server exists in the mirrored server and a valid login from the mirrored server exists in the principal server. In addition, you will need to grant the CONNECT permission to the database mirroring endpoint of the server instances. If you are using the SQL Server Agent account as the service accounts for database mirroring, you will be fine.

In the following example, we will be setting up database mirroring in a High safety without automatic failover mode (high protection without a witness server). Here are the overview steps:

  1. Create the endpoint for database mirroring in the principal server.
  2. Create the endpoint for database mirroring in the mirror server.
  3. Take a full backup of the principal database.
  4. Restore the full backup to the mirror server with no recovery.
  5. Backup the transaction log of the principal database.
  6. Restore the transaction log backup to the mirror server with no recovery.
  7. On the mirrored server, alter the database to set its partner to the principal server.
  8. On the principal server, alter the database to set its partner to the mirrored server.

Implementation steps:

  1. Create the endpoint for database mirroring in the principal server.
  2. -- On the principal server
    
    -- Check the database mirroring endpoint
    -- You can drop it using the DROP ENDPOINT endpointname statement
    SELECT name
    , role_desc
    , state_desc
    FROM sys.database_mirroring_endpoints
    
    -- To find the port number for Database Mirroring
    select name
    , type_desc
    , port
    from sys.tcp_endpoints
    
    -- Create the database mirroring endpoint if not exist
    CREATE ENDPOINT DB_Mirroring
        STATE=STARTED 
        AS TCP (LISTENER_PORT=5022) 
        FOR DATABASE_MIRRORING (ROLE=PARTNER)
    GO
    
  3. Create the endpoint for database mirroring in the mirror server.
  4. -- On the mirror server
    
    -- Check the database mirroring endpoint
    -- You can drop it using the DROP ENDPOINT endpointname statement
    SELECT name
    , role_desc
    , state_desc
    FROM sys.database_mirroring_endpoints
    
    -- To find the port number for Database Mirroring
    select name
    , type_desc
    , port
    from sys.tcp_endpoints
    
    -- Create the database mirroring endpoint if not exist
    CREATE ENDPOINT DB_Mirroring
        STATE=STARTED 
        AS TCP (LISTENER_PORT=5023) 
        FOR DATABASE_MIRRORING (ROLE=PARTNER)
    GO
    
  5. Take a full backup of the principal database.
  6. -- Backup the principal database
    -- For example:
    BACKUP DATABASE AdventureWorks -- replace with your principal database name here
    TO DISK = 'C:\SQLBACKUPS\AdventureWorks.bak' -- replace with your backup path here
    WITH COMPRESSION
    
  7. Restore the full backup to the mirror server with no recovery.
  8. -- Restore the principal database to the mirror server with NORECOVERY
    -- For example:
    
    -- view the logical file names 
    RESTORE FILELISTONLY
    FROM DISK = 'C:\SQLBACKUPS\AdventureWorks.bak' -- replace with your backup path here
    
    -- restore the database with NORECOVERY
    RESTORE DATABASE AdventureWorks -- replace with your mirror database name here
    FROM DISK = 'C:\SQLBACKUPS\AdventureWorks.bak' -- replace with your backup path here
    WITH MOVE 'AdventureWorks_Data' TO 'C:\AW\AdventureWorks_Data.mdf',
    MOVE 'AdventureWorks_Log' TO 'C:\AW\AdventureWorks_Log.ldf',
    NORECOVERY
    
  9. Backup the transaction log of the principal database.
  10. -- Backup the transaction log of the principal database
    -- For example:
    BACKUP LOG AdventureWorks -- replace with your principal database name here
    TO DISK = 'C:\SQLBACKUPS\AdventureWorks.trn' -- replace with your backup path here
    WITH COMPRESSION
    
  11. Restore the transaction log backup to the mirror server with no recovery.
  12. -- Restore the transaction log backup to the mirror server
    -- For example:
    RESTORE LOG AdventureWorks -- replace with your mirror database name here
    FROM DISK = 'C:\SQLBACKUPS\AdventureWorks.trn' -- replace with your backup path here
    WITH NORECOVERY
    
  13. On the mirrored server, alter the database to set its partner to the principal server.
  14. -- On the mirror server, alter the database to set its partner to the principal server
    -- For example:
    ALTER DATABASE AdventureWorks -- replace with your principal database name here
    SET PARTNER = 'TCP://fully qualified principal server name:5022' -- replace with your fully qualified principal server name and port number for the endpoint
    
  15. On the principal server, alter the database to set its partner to the mirrored server.
  16. -- On the principal server, alter the database to set its partner to the mirror server
    -- For example:
    ALTER DATABASE AdventureWorks -- replace with your mirror database name here
    SET PARTNER = 'TCP://fully qualified mirror server name:5023' -- replace with your fully qualified mirror server name and port number for the endpoint
    

To see the Database Mirroring status, role description, safety level, partner name and instance name, you can run the following query on either the Principal or the Mirror server:

-- Querying the database mirroring status, role desc, safety level, partner name and instance
SELECT @@SERVERNAME as InstanceName
, DB_NAME(dbm.database_id) as DatabaseName
, dbm.mirroring_state_desc
, dbm.mirroring_role_desc
, dbm.mirroring_safety_level_desc
, dbm.mirroring_partner_name
, dbm.mirroring_partner_instance
FROM sys.database_mirroring dbm
WHERE dbm.mirroring_guid IS NOT NULL

REFERENCES:

Setting Up Database Mirroring
http://msdn.microsoft.com/en-us/library/ms190941(v=sql.105).aspx

Database Mirroring Endpoint
http://msdn.microsoft.com/en-us/library/ms179511(v=sql.105).aspx

How to install SQL Server on an active/passive cluster?

SQL Server clustering is a collection of two or more physical servers known as nodes with access to shared resources forming a public network that serves as the interface for applications. SQL Server service, SQL Server agent service, and disks are example of shared resources. Each of the nodes communicates constantly with each other through an internal private network inside the cluster. This is also known as the heartbeat.  The heartbeat checks whether a node is up and running. Only one node in an active/passive cluster can access to the shared resources at a time. When active node losses heartbeat or communication to the other node in the cluster, the passive node will take over the ownership of shared resources to service all requests coming in to the cluster. This is known as failover.

SQL Server clustering is created on top of Microsoft Cluster Services (MSCS).  MSCS is capable of detecting hardware and software failures and automatically change the control of the resources to a functioning node when needed.  To coordinate the distributed transactions between 2 or more nodes, the Microsoft Distributed Transaction Control (MSDTC) service is needed. Within a cluster, the Quorum Drive contains the update-to-date information about the state of the cluster. Without it, the cluster can’t function at all.

Prior to installing SQL Server on a cluster, you should ask or work with the System Administrators to install and configure the Microsoft Cluster Services (MSCS). In addition, you should have your checklist ready for verification and preparation for the installation. It is recommended that you write down the answer next to each of the check items. The following is an example of such list:

  1. Know the nodes for the cluster and their ip addresses and access to both nodes.
  2. The SQL Server installation product key.
  3. The SQL Server components to be installed.
  4. The SQL Server virtual server name.
  5. Default or named instance?
  6. The shared disks to select.
  7. The SQL Server virtual IP address.
  8. The subnet mask. Usually it is 255.255.255.0
  9. The dedicated domain account for the SQL Server service.
  10. The dedicated domain account for SQL Server Agent service.
  11. The dedicated domain accounts for each of the services to be installed (SSAS, SSIS, and/or SSRS).
  12. The SQL Server Collation to select.
  13. Windows or Mixed authentication mode?
  14. The list of users to be the SQL Server administrator for the instance.
  15. The default locations for the data and log files for the user databases and tempdb and also the location for the backup directory.
  16. Do you need to enable FILESTREAM?

Before running the installation wizard, you should run the validation wizard for your cluster configuration to make sure all components are compliant:

  1. Click the “Start” button then click on “Administrative Tools” and then click on “Failover Cluster Management.”
  2. In “Failover Cluster Manager,” click on the “Validate a Configuration” link.
  3. In “Select Server or a Cluster,” browse to or enter the cluster name or the nodes.
  4. In “Testing Options,” select the option “Run all tests (recommended)” and then click “Next.”
  5. In “Review Storage Status,” select the option “Take the listed services or applications offline, so that the associated disks can be tested. These services or applications might be unavailable for a significant time. They will be brought online again after the tests.”
  6. Click “Next.”
  7. Review the “Failover Cluster Validation” report.

After you had the checklist items filled in and ran the cluster configurations validation check, you are ready for the installation of SQL Server on the cluster. Here we go:

  1. Insert the CD or on the installation folder, run setup.
  2. On the “SQL Server Installation Center” page, click “Installation” link on the left hand column, then click on the link “New SQL Server failover cluster installation” on the right.
  3. On the “Setup Support Files” page, click the “Install” button.
  4. The System Configuration Checker runs a discovery operation on your computer. Review the details by clicking on the “Show details >>” button if necessary. Click the “Ok” button to continue.
  5. On the “Product Key” page, key in the product key and then click the “Next” button.
  6. On the “License Terms” page, check the “I accept the license terms” checkbox and then click the “Next” button to continue.
  7. On the “Setup Support Rules” page, review and correct the errors and warning if there are any before proceeding. If all green, meaning successfully, then click the “Next” button to continue.
  8. On the “Feature Selection” page, select the components that you need not what you wish to have for your installation.
  9. On the “Instance Configuration” page, key in the SQL Server virtual name in the “SQL Server Network Name” textbox and also determine if you wish to have a default instance or a named instance. Make the selection for the corresponding radio button. If you selected the “Named instance” option, you must key in the name for the named instance. By default, the instance named is used as the Instance ID. You can leave as the default. Click the “Next” button to continue.
  10. On the “Disk Space Requirements” page, the installer checks if you have enough disk space on your local disks to install the SQL Server 2008 binaries. You should see the green check mark(s) indicating that you have sufficient amount of disk space for the installation. Click the “Next” button to continue.
  11. On the “Cluster Resource Group” page, select “SQL Server (MSSQLSERVER)” in the drop-down box for “SQL Server cluster resource group name:” or you can type in a new one as the cluster resource group. Click the “Next” button to continue.
  12. On the “Cluster Disk Selection” page, select the shared disks for the SQL Server resource cluster group. Click the “Next” button to continue.
  13. On the “Cluster Network Configuration” page, specify the “IP Address” and “Subnet Mask” for the SQL Server cluster. Deselect the checkbox for “DHCP” as you will be using static IP addresses. Uncheck “IPv6” if it is checked. Click the “Next” button to continue.
  14. On the “Cluster Security Policy” page, select the option “Use service SIDs (recommended).” This is the recommended option for Windows Server 2008 and later.
  15. On the “Server Configuration” page and in the “Service Accounts” tab, specify the “Account Name” and “Password” for the SQL Server Agent service, the SQL Server Database Engine service, and other services that you had selected in the components section page. Microsoft recommends individual accounts for each of the services in addition to following the principle of Least Amount of Privileges.
  16.  Still on the “Server Configuration” page, click the “Collation” tab and then specify the collation as per the application(s) requirements. Don’t select the default unless you are absolutely sure or else it will be a royal pain in the behind to correct this. Stop and confirm the correct collation before moving on. Seriously! Once you have selected the collation, click the “Next” button to continue.
  17. On the “Database Engine Configuration” page and in the “Account Provisioning” tab, select the option “Windows authentication mode” or the option “Mixed Mode (SQL Server authentication and Windows authentication).”  If you had selected the latter, then you must key in the “sa” password. In the “Specify SQL Server administrators” section, click the “Add Current User” button to add the user running the installation as an administrator. You can also add more users by clicking on the “Add…” button.
  18. Still on the “Database Engine Configuration” page, click the “Data Directories” tab to specify and default directories for the data, user databases data and log, tempdb data and log, and backup.
  19. Still on the “Database Engine Configuration” page, click the “FILESTREAM” tab to enable Filestream on this instance if needed. Click the “Next” button to continue.
  20. On the “Error and Usage Reporting” page, check the corresponding optional checkboxes if you wish then click the “Next” button to continue.
  21. On the “Cluster Installation Rules” page, the installation validation system validates the configuration and selection of the specified SQL Server components.  If everything passed, clicked the “Next” button to continue.
  22. On the “Ready to Install” page, you can see the components that were selected. Click the “Install” button to start the installation.
  23. On the “Installation Progress” page, you can see the installation progress status as the bar move to the right end.
  24. Cross your fingers and wait for the “Complete” page and hope to see the green check mark “Your SQL Server 2008 failover cluster installation is complete.” Click the “Close” button to end.

You will now need to add the second node in the failover cluster to enable high-availability.

  1. On the passive node, start the installation.
  2. On the “SQL Server Installation Center” page, click the “Installation” link on the left hand column and click on the “Add node to a SQL Server failover cluster” link on the right.
  3. On the “Setup Support Rules” page,  validate the checks return all successful results, then click the “Ok” button to continue.
  4. On the “Product Key” page, key in the product key and then click the “Next” button.
  5. On the “License Terms” page, check the “I accept the license terms” checkbox and then click the “Next” button to continue.
  6. On the “Cluster Node Configuration” page, select the virtual SQL Server instance name on the “SQL Server instance name” drop-down box. Click the “Next” button to continue.
  7. On the “Service Accounts” page, specify the “Account Name” and “Password” for the SQL Server Agent service, the SQL Server Database Engine service, and other services that you had selected in the components section page. Microsoft recommends individual accounts for each of the services in addition to following the principle of Least Amount of Privileges. Once done, click the “Next” button to continue.
  8. On the “Error and Usage Reporting” page, review and correct the errors and/or warnings if needed and then click the “Next” button to continue.
  9. On the “Ready to Add Node” page, review the configurations and selections you have made and then click “Next” to continue.
  10. On the “Add Node Progress” page, the status progress bar will move to the right end as per the installation progress.
  11. On the “Complete” page, nothing is sweeter than seeing the green check mark next to “Your SQL Server 2008 failover cluster add node operation is complete.” Click the “Close” button to end.

This is what the SQL Server cluster looks like in the Cluster Admin: Screen-shot.

After verifying that the latest services pack is compatible with the application(s) you can then apply the latest service pack or hotfixes on the Active/Passive SQL Server cluster with the following steps:

  1. On the passive node (Node2), apply the service pack or hotfixes.
  2. Reboot the passive node (Node2).
  3. On the active node (Node1), failover the SQL Server resource. The passive node (Node2) that you had already patched will become the active node. To failover the SQL Server resource, open the “Failover Cluster Management,” under “Services and Applications,” right-click on “SQL Server (MSSQLSERVER),” then click on “Move this service or application to another node >” and click on the option to move to the passive node.
  4. On the passive node (Node1), apply the service pack or hotfixes.
  5. Reboot the passive node (Node1).

You can verify the current service pack and version build number by running the following query:

-- Querying the SQL Server Instance level info
SELECT
	SERVERPROPERTY('ServerName') AS [SQLServer]
	,SERVERPROPERTY('ProductVersion') AS [VersionBuild]
	,SERVERPROPERTY ('Edition') AS [Edition]
	,SERVERPROPERTY('ProductLevel') AS [ProductLevel]
	,SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsWindowsAuthOnly]
	,SERVERPROPERTY('IsClustered') AS [IsClustered]
	,SERVERPROPERTY('Collation') AS [Collation]
	,SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [CurrentNodeName]

How to change the job owner for the jobs created by the SQL Server maintenance plans?

Last week, the SQL Server DBA for the Finance and Accounting department has left the company for a new opportunity. You were assigned to take on the extra responsibilities of supporting the SQL Servers for the Finance and Accounting department, until the replacement is hired. The Windows Administration team has received the request to disable the previous SQL Server DBA’s domain account today and had successfully done so in the past hour. You started receiving email alerts that the transaction log backup jobs are failing. You reviewed the jobs that had failed and all of them had returned the following error:

The job failed. Unable to determine if the owner (<Domain\Account Name>) of job <job name> has server access (reason: Could not obtain information about Windows NT group/user ‘<Domain\Account Name>’. [SQLSTATE 42000] (Error #####)).

The <Domain\Account Name> is the domain account that belongs to the previous SQL Server DBA. What can you do to resolve this problem quickly?

You tried to change the job owner to the ‘sa’ account and also tried changing it to the SQL Server Agent account. No avail. The job owner reverts back to the original job owner. You have plans to replace all the maintenance plan jobs that were created using the GUI to your customized stored procedure to backup, perform index maintenance, and update statistics. But, water that’s far away can’t help put out the fire that’s burning your pants and socks off right now. What can you do?

You know that all maintenance plan jobs are created as packages. DTS packages in SQL Server 2000. SSIS packages in SQL Server 2005, 2008, and 2008 R2. You can view all the DTS or SSIS packages in the system tables for the packages. To change the job owner, you must change the package owner and the job owner will be updated to the package owner. Their corresponding system table names are as follows:

  • msdb.dbo.sysssispackages for SQL Server 2008 and R2
  • msdb.dbo.sysdtspackages90 for SQL Server 2005
  • msdb.dbo.sysdtspackages for SQL Server 2000

To view the package(s) owner(s):

-- Querying the package names
-- Make a not for the ones you will need to update
SELECT name as PackageName
, suser_sname(ownersid)
FROM
[msdb].[dbo].[sysdtspackages90] -- SQL Server 2005: [sysdtspackages90], SQL Server 2008 and R2: [sysssispackages], SQL Server 2000: [sysdbmaintplans]
WHERE name in ('MaintenancePlanName', 'MaintenancePlanName') -- Replace with your maintenance plan names here

To update the package(s) owner(s):

-- Update the owner to the SQL Server Agent account
UPDATE [msdb].[dbo].[sysdtspackages90] -- SQL Server 2005: [sysdtspackages90], SQL Server 2008 and R2: [sysssispackages], SQL Server 2000: [sysdbmaintplans]
SET ownersid = SUSER_SID('DOMAIN\SQLServerAgentAccount') -- Replace with your SQL Server Agent account
WHERE ownersid = SUSER_SID('DOMAIN\Account Name') -- Replace with the account name reported from the job failure
	and name in ('MaintenancePlanName', 'MaintenancePlanName') -- Replace with your maintenance plan names here

The last and very important step is that you must open the maintenance plan and make a minor cosmetic change such as resizing one of the tasks and then save the changes for the job owner to take effect. Yes. No kidding. That’s why you should replace these GUI maintenance plans to customized stored procedures when you can.

REFERENCES:

sysssispackages
http://msdn.microsoft.com/en-us/library/ms181582(v=sql.105).aspx

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

SUSER_SNAME()
http://msdn.microsoft.com/en-us/library/ms174427(v=sql.105).aspx

SUSER_SID()
http://msdn.microsoft.com/en-us/library/ms179889(v=sql.105).aspx

What are the possible recovery models for the SQL Server databases?

Have you ever had a conversation with your colleagues where they keep on saying “recovery mode” instead of “recovery model” of a database? You might have even second guessed yourself if you were the one who is mistaken on the correct terminology. Make no mistake about it. You are certainly correct. But, there’s no need to argue with them or correct them. You can just keep on consistently refer to it correctly. Others will follow when they know you are correct. Next time, you should be more confident in yourself. It is “recovery model” not “recovery mode.” If you feel otherwise, please do share with us in the comment section. So, what exactly does recovery model mean and what are the possible recovery models that are available in SQL Server and when do you select one over the others? Here’s a brief explanation just to refresh your memory.

Recovery models control the transaction log maintenance. You choose one over the others based on your business rules and the recoverability that the databases must have in case of disaster. There are 3 different types of recovery models:

  • Simple
  • Bulk-logged
  • Full

Simple recovery model is usually selected for the databases that are in development or testing servers where the recoverability of the data is not critical to the business. In case of disaster, you can only recover up to the most recent full or differential backup as you can’t take transaction log backups in the simple recovery model.

Bulk-logged recovery model should be used only when you need to perform large bulk operations such as bulk importing over large amounts of data. It should only be used on a temporary basis and switch back to full when the bulk operations are completed. You can select it to reduce the transaction log space during a bulk transaction as they won’t be logged. In case of disaster, you cannot recover the database to the point of failure. On a side note: “Large amounts of data” is a fully loaded statement and can cause endless debates on what is considered as large. Many of the documentations have stay away from being specific just to avoid debates. But, I will say >= 5 GB should be considered as large. All of us are certainly entitled to our own opinions. Either way you spin it, others can pick it apart and get into debate with you if they wish to. Let them talk and hear them out. As per Stephen Covey, “SEEK FIRST TO UNDERSTAND, THEN TO BE UNDERSTOOD.” Let’s get into habit number 5.

Full recovery model should be the recovery model for all production databases until otherwise specified with good reasons. If full, differential, and transaction log backups are accounted for, it is the only recovery model that can recover to any point in time.

How can you find the recovery model for each of the databases in the SQL Server instance? Here’s the script:

-- Querying the recovery model for each of the databases
SELECT name as DatabaseName
, recovery_model_desc
FROM SYS.DATABASES

REFERENCES:

Recovery Model Overview
http://msdn.microsoft.com/en-us/library/ms189275(v=sql.105).aspx

Backup Under the Bulk-Logged Recovery Model
http://msdn.microsoft.com/en-us/library/ms190692(v=sql.105).aspx

Operations That Can Be Minimally Logged
http://msdn.microsoft.com/en-us/library/ms191244(v=sql.105).aspx