Deep dive into SQL Server 2008

Deep dive into SQL Server 2008

Microsoft's 'Katmai' is packed with performance and management features enterprises will love, and even small shops will have reasons to make the upgrade

Still, this is Microsoft's first stab at granular resource management, and while it's a nice start, it's not going to be as immediately useful as many shops hope. First of all, you can specify only two resource measures to define a resource group: CPU and memory. You can specify min and max values for each one, but that's not nearly enough to define a rogue process on a busy system. Rogue processes take many forms, and capturing them requires additional metrics (number of I/Os, amount of time, etc.) beyond CPU and memory usage.

Further, once you define a process to be in a particular resource group, it stays there. Misbehaving queries cannot be moved dynamically to a different group, as you can do in Oracle Database. In order to assign a query to a different resource group, you would have to kill the process, assign it to the group, and then restart the Resource Governor. Something tells me you won't want to do that often on a really busy system.

Finally, you have to be careful with how you set up your groups. For example, if you have three groups and they all have a maximum 50 percent of the memory defined, then there's obviously going to be a problem. So there have to be rules that define how much everyone gets; you just have to be careful about how you define your groups and monitor them to make sure they're giving you what you want.

It's difficult not to compare Microsoft's Resource Governor with Oracle's. Oracle Database has had the feature longer, and it's certainly richer and more mature. But there's one advantage to Microsoft's you just can't ignore: It's included in the license, while Oracle's is a fairly expensive add-on.

Change Data Capture and nonlogged inserts

Change Data Capture (CDC) and nonlogged inserts are two nice enhancements to the SQL Server engine that will make a world of difference for some shops. These enhancements mainly revolve around warehouses, but can be useful on some OLTP systems as well. CDC will alter the way developers write ETL processes for warehouses. CDC employs the same log reader used by replication to write the specifics of DML (Data Manipulation Language) operations to a table instead of a distribution database. This allows warehouses to be very easily loaded from tables that only contain changed data -- no more having to modify legacy applications or ETL processes with update columns in all the tables to provide this functionality. And you can track with perfect precision the changes made to data in your tables without altering your schema or performing a messy and expensive query to filter out the differences.

Retrieving the changes from the CDC tables is very easy also. Microsoft has provided a set of functions that retrieve the data, so all you have to do is call them. As you would expect, both Type 1 and Type 2 data can be stored, so you can have the exact level of change data you want.

Follow Us

Join the newsletter!


Sign up to gain exclusive access to email subscriptions, event invitations, competitions, giveaways, and much more.

Membership is free, and your security and privacy remain protected. View our privacy policy before signing up.

Error: Please check your email address.

Tags databasessql server 2008software applications

Brand Post

How to become the best IT MSP

This article provides guidance for managed service providers (MSPs) that want to grow their business. It is also useful for any IT service provider looking to move from the break-fix model to managed IT services.



Reseller News Innovation Awards 2019: meet the winners

Reseller News Innovation Awards 2019: meet the winners

Reseller News honoured the standout players of the New Zealand channel in front of more than 480 technology leaders in Auckland on 23 October, recognising the achievements of top partners, emerging entrants and innovative start-ups.

Reseller News Innovation Awards 2019: meet the winners
Malwarebytes shoots the breeze with channel, prospects

Malwarebytes shoots the breeze with channel, prospects

A Kumeu, Auckland, winery was the venue for a Malwarebytes event for partner and prospect MSPs - with some straight shooting on the side. The half-day getaway, which featured an archery competition, lunch and wine-tasting aimed at bringing Malwarebytes' local New Zealand and top and prospective MSP partners together to celebrate recent local successes, and discuss the current state of malware in New Zealand. This was also a unique opportunity for local MSPs to learn about how they can get the most out of Malwarebytes' MSP program and offering, as more Kiwi businesses are targeted by malware.

Malwarebytes shoots the breeze with channel, prospects
Show Comments