Recently, I was pulled into a gig to help troubleshoot an windows failover cluster service issue that was hindering an availability group. Just to give a little background information its a two node windows cluster with two replicas the availability groups are using synchronous mirroring. Once, this was resolved we had a critical database that was behind on synchronization because one of the two replicas (cluster node) was down. This lead to the following question. When will my replica by synchronized?
This question was easy to answer with database mirroring. We could pull up the database mirroring monitor. We don’t have an database replica monitor GUI tool built into SSMS. Lucky for us, its not that hard with availability groups. We just have to use performance monitor. You will see below there is an perfmon collection called “SQLServer:Database Replica” that comes in handy for us.
When will replica be synchronized?
Above, you can see that this secondary replica which is back online is synchronizing as its receiving log bytes and the Recovery Queue shown in the performance monitor is also reducing as the synchronization process is catching up. According to MSDN Recovery Queue is the Amount of log records in the log files of the secondary replica that has not yet been redone.
Welcome to the Policy-Based Management myths finale. Do to some great questions at SQL Saturday # 61 in DC I am going to focus on some myths that come up with Policy-based Management. Today is the final installment, you can catch the other myths below.
You cannot use Policy-Based Management with SQL Server 2008 R2 Express
Just like the two other myths the answer is true and false. This one, really through me for a loop mainly because I don’t normally use SQL Express. With that said, if you want to store or use SSMS to create a policy while connected to SQL Express instance the answer is false. If you want to evaluate an existing SQL Express instance the answer is true.
Why can’t I create policies or store them in SQL Express 2008 R2?
When I read the following article on SQL Server 2008 R2 Express site I would think that the flexible section make it seam like you could store and create policies with that edition of SQL Server. Unfortunately, you will notice that Policy-Based Management is disabled. This isn’t too bad, lets just enable it. Oh snap………. You get the friendly error message that states, “Policy-Based Management cannot be enabled on this edition of SQL Server.”
How do I evaluate policies against SQL Express 2008 R2?
So, I cannot enable Policy-Based Management on SQL Server 2008 R2 Express edition can I still evaluate and apply changes to this instance of SQL Server? Yes, you can, once you have your policies built you can use Central Management Server to evaluate and apply changes.
Below is a screen shot of me evaluating the built-in “Last Successful Backup” policy against an instance of SQL Server 2008 R2 Express. You will see that it fails because well I don’t use Express so I don’t have the databases backed up.
Note: There will be more to come in a future blog post on setting up Central management Server and using it to execute Policy-Based Management policies.
How can I build a policy to work against SQL Express 2008 R2?
Connect to another instance of SQL Server using, Standard, Enterprise, or Developer to generate your policies via SSMS and store the policy on a central instance of SQL Server (not express) or on a file share.
A few weekends ago I gave an “Introduction to Policy-Based Management and Central Management System” at SQL Saturday 61 in DC. Do to some great questions I am going to focus on some myths that come up with Policy-based Management this week. Check back through the week to catch all three myths. Today we will cover the first of the following three myths.
You cannot execute and apply policies against an instance of SQL Server 2000.
The answer is yes and no. You can execute and apply policies against an instance of SQL Server 2000 if the policy executed runs against features supported in SQL Server 2000. This is easy to do if you have a central management server setup. I will walk you through the steps below assuming a CMS exists.
1. Right click on the node in your Configuration Management System that hosts your instances of SQL Server 2000 and right click and select evaluate policies as shown below.
2. Click on the ellipse next the source and select the policy files you would like to execute or select a sever where the policies reside. In this example we will execute the Database Auto Shrink built-in best practice policy. By default they are located at C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033\. Once you have loaded the Database Auto Shrink policy click execute and the policy will be evaluated against every server in your CMS group.
3. The following are the results of executing the “Database Auto Shrink” policy against a group of development SQL Server 2000 instances. You will noticed that the policy failed as there are databases that have auto shrink equals true. We can click on the checkboxes and apply the policy to force the databases to be compliant with our policy.
4. Click yes on the next popup screen will apply the policy to modify all selected targets that do not comply. Make sure you want to do this before you apply the policy.
5. After selecting apply you should see that you were able to apply the changes to make your SQL Server 2000 databases are compliant with the policy.
Can you show me an example of a policy that will not work on SQL Server 2000?
Database Mirroring is not supported in SQL Server 2000 as it was delivered in SQL Server 2005 SP1. If you have a policy that checks to see if mirroring is enabled and you run it against a SQL Server 2000 instance the policy will fail. In fact, you will receive an “Property ‘IsMirroringEnabled’ does not exist error.
Check back during the week to catch the next Policy-Based Management myth.