A first look at SQL Server 2008 (part 1 of 3)

During the last days I found the time to have a first look at SQL Server 2008. In this 3 parts blog (I’ll post the second one the coming week) I will summarize some of the most interesting features I discovered during my tour inside this new version of our popular database.

To start let me mention some of the new technologies that allow a better manageability of a SQL Server environment and therefore improve the life of developers and database administrators. In fact, today we are struggling more and more in quite complex environments; the product itself, SQL Server, has become more complex. So to help us working in such a complexity, SQL Server 2008 is coming with plenty of new interesting features.

One of these is the ability to designate a SQL Server instance as a configuration server (the feature is known as Configuration Server), and then bind it to a group of servers. The idea is that you can apply things or do things to the whole group of servers via the configuration server. It is for instance possible to execute a TSQL statement on all servers and obtain as a result the union of all the single servers result sets. Another interesting thing you can do is to apply policies to the configuration server (using the new Declarative Management Framework) and therefore having them applied automatically to all “child” servers. But, what are these policies? An example of policy could be a rule that defines that there must be a backup for each database that is not older than 2 days; another could define that the name of all created views must have “vw_” as root.

One thing to know is that all these policies can be organized into groups and that SQL Server 2008 is coming with some out-of the-box policy groups; one of these groups contains a set of rules which are considered to be best practice (if I remember the group is labeled “BestPractise”). So, instead of assigning each single policy to an object, you can easily work with groups.

But, how can you manage all these policies? As I said, policies can be organized into groups. Moreover, SQL Server 2008 is coming with the new SQL Server Management Studio, which also brings an improved UI. With this new UI you can monitor all objects inside a database and therefore be warned in case of policy violation. Of course the monitoring activity can be configured in different ways: you can avoid the violation of policies by preventing users to smash a defined rule or, on a regular basis, you can simply do a manual check to verify if your database conforms to specific policies. There is also another option, which gives you the opportunity to automate everything in a way so that you will be notified in case of rule violation.

Another interesting feature is the Resource Governor, which is an extension of the SQL Server 2008 engine, which allows you to solve problems like the runaway queries: somebody connects to the database, write a query in a wrong way (for instance forgetting a join condition), the query getting executed consumes all available resources and then the result is that we have our database server completely down. Thanks to the Resource Governor you can define resource groups, which are logical groups which identify a specific set of activities. You can for instance define a logic group that includes all backup activities and another group that includes all reporting activities and then assign each single group to a resource pool group, which defines how resources have to be used. You can for example configure the resource pool not to consume more than 40% CPU and more than 30% memory and so on. In simple words the Resource Governor can be seen as the arbitrator of resources.

Something that is more tied to the security aspects, are the improved Auditing functionalities: a solution that can be used to trace a wide range of activities occurring on a database server, including TSQL commands, and therefore more interesting, including SELECT statements with filtering support. This is really a nice feature, because in the past, in order to capture a select statement, you had to start and work with the SQL Server Profiler, which was also the case for tracing permission operations. So now, the full support has been completely integrated inside SQL Server 2008 and available as an out-of-the box solution with reporting capabilities.

This is the end of the first part. In the second part we will have a look to some of the new nice TSQL-enhancements. So Stay tuned!

Ken