Archive for July, 2013

Using Policy-Based Management to Update PAGE_VERIFY Settings – Part One

July 31, 2013 Leave a comment

After my last post on using Dynamic SQL, I tweaked the script to check for databases with a PAGE_VERIFY setting other than CHECKSUM. If you are running SQL Server 2005 or higher, CHECKSUM should be set for all of your databases. With PAGE_VERIFY set to CHECKSUM, when SQL Server writes a dirty page back to disk, it first calculates a checksum value for the contents of the page and stores the value in the page header. When the page is read back into memory, the checksum is recalculated and compared with the values in the page header. If they are different, you have corruption. This is the best method for detecting corruption caused by the IO subsystem.

If you want a second opinion, here is Microsoft's recommendation. If you need a third opinion, here's Paul Randal's take on the subject.

To determine which databases have PAGE_VERIFY values other than CHECKSUM, you could query sys,databases and change the offenders manually. Or you could write a dynamic SQL script like the one from my previous post to identify the right databases and update them. I was planning on posting the script that I wrote to do just that, and then I remembered Policy-Based Management. Policy-Based Management (PBM) first shipped with SQL Server 2008 and is available in 2008 R2 and 2012. PBM allows you to define policies for your environment, audit your environment for adherence to those policies and make changes to ensure the environment is compliant. Policies can be run against SQL Server 2005 instances, but some functionality is not available. You can get more detailed information about PBM from Books Online.

Create a New Policy

For this example, I will set up a policy to check the PAGE_VERIFY setting for each user database in my environment. To start, expand the Management node in Object Explorer, followed by the Policy Management node. Right-click on Policies and select New Policy.

1 - Define Policy

Enter a name for your policy in the Name field of the Create New Policy dialog. I've called mine PageVerify. Next click the Check Condition dropdown and select New Condition.

1 - Define Condition

Enter a name for your condition and click the Facet dropdown, selecting Database Options. Click the lookup button next to the Field text box and select @PageVerify. Click in the Operator text box and select the equals sign. Finally, click the Value dropdown and select CHECKSUM. Your dialog should look like the one below. Click the OK button and you will return to the Create New Policy dialog.

3 - Set Policy

4 - Policy Defines

From here, you can set the policy to run against a subset of databases or servers. You also have the opportunity to set the Execution Policy. For this example, I will leave it set to On Demand. The other options for this example are On Schedule and On Change: Log Only. A fourth option, On Change: Prevent is available for some policies. It will roll back a change that violates the policy and output an error message to the user. Click okay, and you've created your first policy.

I will cover evaluating this policy in my next post.

Updating SQL Server compatibility_level Dynamically

July 29, 2013 Leave a comment

My company is starting to look at upgrading to SQL Server 2012, so I've installed it on a server here and am working to get an application set up. I ran the Upgrade Advisor against the existing server, which is running SQL Server 2005. It errored on a number of databases because they were set to compatibility_level 80 and it can only processes 90 or higher. I put together a quick dynamic script to update the compatibility mode to 90 for anything set below that. In the spirit of Ted Krueger's (b|t) post on throw-away SQL from last year, here's the code:

USE master

-- Shut off 'row(s) affected' messages in your output making it easier to copy and paste dynamic SQL

-- Create a temp table to hold the list of database names
(dbname SYSNAME)

-- Declare your local variables
DECLARE @sqlstr VARCHAR(2000)

-- Get a list of database names that are online and in a compatibility mode less than 2005.  This can be adjusted to look for other compatibility_level values.
SELECT name FROM sys.databases
WHERE compatibility_level < 90
AND state_desc = 'ONLINE'

-- Get a count of those database names for a loop
SET @dbcount = @@ROWCOUNT
-- Initialize your loop counter
SET @loopcount = 1

-- While the loop counter is less than the count of databases, execute your loop
WHILE @loopcount <= @dbcount
	-- Get the top database name from the temp table
	SET @dbname = (SELECT TOP (1) dbname FROM #dbcomp)
	-- Build your ALTER DATABASE statement to update the compatibility_level
	SET @sqlstr = 'ALTER DATABASE ' + @dbname + ' SET COMPATIBILITY_LEVEL = 90;'
	/*PRINT @sqlstr - Uncomment if you want to print the SQL statement for future use*/
	-- Execute the ALTER DATABASE statement for each database
	-- Delete the TOP row from the temp table
	DELETE TOP (1) FROM #dbcomp
	-- Increment the loop counter
	SET @loopcount = @loopcount + 1


-- Clean up the temp table when done
DROP TABLE #dbcomp
Categories: SQL Server, T-SQL Tags: ,

SQL Saturday #239 – Another Presentation Under My Belt

July 28, 2013 1 comment

I just got home after a great weekend in Iowa City at SQL Saturday #239. I presented Interrogating the Transaction Log for the fifth time, this time with an extended section on virtual log files. Each time I've done the presentation, people have asked questions about VLFs and I always try to give the people what they want. Thanks to Ed Leighton-Dick (t), Sheila Acker (t) and everyone else who put on a great event. And a big thank you to everyone who attended my presentation. You can find the slides and demos here.

P.S. - You can also find the scripts and slides at the SQL Saturday #239 site as well.