Archive
Using Policy-Based Management to Update PAGE_VERIFY Settings – Part One
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.
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.
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.
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.
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
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 GO -- Shut off 'row(s) affected' messages in your output making it easier to copy and paste dynamic SQL SET NOCOUNT ON -- Create a temp table to hold the list of database names CREATE TABLE #dbcomp (dbname SYSNAME) -- Declare your local variables DECLARE @dbcount TINYINT DECLARE @loopcount TINYINT DECLARE @sqlstr VARCHAR(2000) DECLARE @dbname SYSNAME -- 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. INSERT INTO #dbcomp 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 BEGIN -- 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 EXEC(@sqlstr) -- Delete the TOP row from the temp table DELETE TOP (1) FROM #dbcomp -- Increment the loop counter SET @loopcount = @loopcount + 1 END -- Clean up the temp table when done DROP TABLE #dbcomp
SQL Saturday #239 – Another Presentation Under My Belt
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.
Chicago Data Professionals
