Follow-up on Policy-based Management in SQL Server 2008 v1

I asked Dan Jones about getting the policies in SQL Server 2008 to be evaluated in SQL Server 2005 and SQL Server 2000. You can create policies in SQL Server 2008 instances and use those policies to evaluate and even implement them in SQL Server 2005 and SQL Server 2000 instances with a couple of things to consider. One, if you are targetting facets which only exist in SQL Server 2008, the policies won't get evaluated on previous versions. An example of this is creating a policy which checks whether or not FILESTREAM option is enabled on a database. This of course won't get evaluated on previous version instances. The same is true when you intend to run the policies on previous version. You cannot run those policies on schedule or on-demand unless you use a workaround using PowerShell to do that. I'll work on a couple of samples where I'll create a policy in SQL Server 2008 and evaluate previous version instances based on that policy.

Thinking of cloning your workstations and servers?Think NewSID.exe

How many times have I mentioned in my blog posts that I am indeed a lazy guy? Probably a lot of times. That is practically because I always think of ways to make my work, especially those repetitive ones, as easy as possible. This case is one of them. I regularly work with virtualization, whether using Microsoft Virtual PC/Virtual Server or VMWare Server or Workstation for my tests or even for simulations. And whenever I need to work with a few servers and workstations, I simply create a copy of the virtual machine to eliminate the time and effort to install the operating system and applying the service packs if they need to be on each one of those images. Many organizations use disk image cloning software such as Norton Ghost or Acronis both for mass deployment of operating systems and for disaster recovery. But if you are to simply use these tools (or my method using virtualization) for cloning purposes, you are in for a surprise if you do not change the computer security identifier (SID) of the different images. To validate what I mean by this, create two cloned images and deploy them on a network. Then, try logging in using a domain account on either of the machines and you'll get the error mentioned below

The name or security ID (SID) of the domain specified is inconsistent with the trust information for that domain

Fortunately, SysInternals have a tool called NewSID v4.10. This tool is a program that changes a computer's SID. It is free and is a Win32 program, meaning that it can easily be run on systems that have been previously cloned. NewSID works on Windows NT 4, Windows 2000, Windows XP and Windows Server 2003. You can download the tool from Microsoft and run it on the cloned machine to change its SID. It is recommended though that you run this tool on a machine that is not a member of a domain. You can choose to randomly generate a SID, copy an SID from another computer or simply specify one (which is not really recommended). You can also choose to rename the machine to a different one (in my case, I still needed to run this tool eventhough I've already renamed the cloned image since they are both going to be a part of the same domain). A rebot would be required after a SID change has been applied. Download the tool and try it out for yourself

...and I found SqlMetal.exe on my machine...

I was reading the online version of the SQL Server Magazine April 2008 edition when I chanced upon an article on LINQ which talks about this tool. This is the first time I've read about this tool so I read further. SqlMetal.exe is a command-line tool that generates code and mapping for the LINQ to SQL component of the .NET Framework. When I started working with LINQ, I was only using Visual Studio 2008 to generate the object-relational diagram by dragging-and dropping the database objects on my designer surface. You can find this tool inside the \Program Files\Microsoft SDKs\Windows\v6.0a\bin folder. It generates the necessary data entity and DataContext object for LINQ as either a .vb or .cs source file. You can run this tool using the Visual Studio 2008 Command-Prompt or navigating to the folder location using command prompt and execute from there. A sample code to use SqlMetal.exe is shown below

sqlmetal /server:localhost /database:Northwind /language:cs /code:Northwind.cs

The command generates a .cs file from the Northwind database running on my SQL Server 2000 instance. When you open the code in Visual Studio 2008, you will see the codes which may seem familiar if you have used the designer to create LINQ objects. The code includes all the database entities and their corresponding attributes, meaning the tables and their fileds with constraints and data types. If you need to include stored procedures and functions, you can simply add the /sprocs and /functions switches. If you're thinking of simply ignoring this tool since the designer is already available for you, think again. It only took me like 10 seconds to type the syntax and it generated the code for me. Compare that to using the designer and dragging and dropping the database objects which would take me at least a minute or two. This would be a great time saver if you need to generate codes for about 50 databases scattered in different SQL Server instances in your environment. Cool tool for developers

Policy-Based Administration in SQL Server 2008 (formerly known as Declarative Management Framework)

This was a long awaited blog post dating back last November 2007 when I did a session at the Singapore SQL Server User Group specifically for this topic. One of the most difficult task that DBAs are faced with in managing complex enterprise database environments is ensuring that all elements of the system conform to their company's established security and usage policy. Administrators need to constantly monitor security surface area of the entire database server along with permissions and settings for every database and server objects. DMF (as what is was formerly called in the previous CTPs) is a policy-based system for managing one or more instances of SQL Server 2008. To use the DMF, SQL Server policy administrators use SQL Server Management Studio to create policies that manage entities on the server such as the instance of SQL Server, databases, and other SQL Server objects. DBAs select one or more managed targets and specifically check that targets comply with the policies. Or they explicitly force the targets to comply with the policies. Let's take a look at a typical scenario. In the past, you might have used the SQL Server Best Practice Analyzer (there is also a version for SQL Server 2005) to check whether or not your SQL Server instances comply with Microsoft's best practices. The problem with this is that you need to manually run the tool which generates the report telling you which settings are running at best practice or not. Plus, you need to manually configure those settings not running at best practice. Imagine having to do this over and over depending on the number of SQL Server instances you manage. This is a very laborious task for DBAs working for an enterprise hosting services company like me. With DMF, you can create policies, similar to how you create policies in Active Directory, that are based on company's established security and usage policy, use these policies to either inspect a server and even change a group of server instances all at the same time with just a click of a button. Amazing how a DBA can do these maintenance tasks with ease using SQL Server 2008.

For more information on SQL Server 2008 Policy Based Management, you can check out a virtual hands-on lab at the TechNet Virtual Lab site so you can play around with how it works. The SQL Server Manageability Team also came up with a Level 300 webcast sometime February 2008 which digs deeper on this new feature in SQL Server 2008. Plus, I've written an article for MSSQLTips.com and created a video at BlogCastRepository.com on this as well - more on evaluating policies on multiple instances at the same time.