Search results matching tags 'SQL Server' and 'Management'

The SQL Server Health Check

BuckWoody — Thu, 11 Feb 2010 14:16:05 GMT

My friend Brent Ozar, who is a top-notch SQL Server Professional, mentioned on his blog (http://www.brentozar.com/archive/2007/04/sql-server-health-check/comment-page-1/#comment-17520) that he brought in Microsoft Support to do a “Health Check”. There were some questions about what this actually entails – so I thought I would post that description here.

A SQL Server Health Check from Microsoft is an offering provided through our support and consulting branches. If you’ve purchased a Premier support agreement, you can use the hours you pay for to have someone come out and check out your systems using two basic vectors: standard best practices, and the best practices for your environment. Let me explain that a little further.

Microsoft Consulting Services (MCS) has an advanced set of tools and techniques to reach deep into your systems to evaluate them against a set of criteria that we establish for the best performance, safety and reliability for SQL Server. But your SQL Server Instances will be different than someone else’s, so the consultant will also spend some time in an interview with various teams to find out how you’re using the system. They’ll consult with experts in that area back at Redmond, and at the end of the engagement you get what I feel is the most valuable part of the exercise – the report. The report shows what was done, how it was done, what the findings are and what recommendations the consultant makes. It’s a thing of beauty.

You don’t have to have a Premier agreement to get a SQL Server Health Check, and companies other than Microsoft have versions of this as well. The prices vary, so if you want a Microsoft person then contact your local Microsoft office and ask for the Consulting Manager for your area. They will get you to the right person and you can work out the costs from there. If you take a hard look, most of the time the cost is well justified.

On a related note, I think it’s fantastic that Brent has the foresight to bring in “another pair of eyes” on his systems. Brent is actually in the program to gain a SQL Server “Master” certification – something only a few people in the world have. He works for a software development firm that creates fantastic products for – wait for it – SQL Server! He could evaluate his own systems, and of course he does. But he realizes that there’s always something more to learn, and someone out there may know one more little detail that he doesn’t. This is the mark of a very bright person.

How Does Microsoft Do IT?

BuckWoody — Wed, 03 Feb 2010 14:17:24 GMT

Microsoft is a big company – and of course we have a lot of IT infrastructure that we have to manage. It might surprise you to learn that we have an IT group, just like at your company. We have a networking team, a server hardware team, software teams, DBA’s, the whole bit. In fact, we have more Mac computers than just about anyone (other than that company down south from here) and we write some of the best-selling Apple software. We have a Linux lab.

How do we do that? How do you manage 80,000+ seats, especially when most of your company are a bunch of tech-savvy geeks? It’s a tough job, but the neat thing is that we tell you how we’re doing it – everything – right here: http://technet.microsoft.com/en-us/library/bb687780.aspx. If you want to focus in on just SQL Server, just check here: http://technet.microsoft.com/en-us/library/bb687798.aspx.

(By the way - I *totally* should be doing our marketing – isn’t that title catchy? My catch-phrases and product names would be a lot better than what we normally come up with. I’m just sayin’.)

Tools and Processes for “Fitting it all in”

BuckWoody — Mon, 18 Jan 2010 14:42:05 GMT

Most data professionals I’ve met work in two modes: we plan for our day, and we react to the situations around us. I’m staring at my list of things that I need to do today right now, which is my planned work. Of course, I have no idea how much of that will really get done – it’s optimistic to be sure. On the other hand I have several systems I manage, and at any moment one of them or the people that interface with them may “change state” such that I need to give them some attention.

So how do I meld the two? Sometimes it can be quite difficult. I’m constantly working through my list in my mind, re-arranging what I’m focusing on based on what I perceive as the highest need. There are, however, some tools that I use each day to help me manage the workflow.

I use Outlook for tracking everything, since it has a task list (my primary tracking), a calendar, mail and so on. Also I can share the information, it’s on-line so I can see it anywhere, and I can even take it offline onto the plane this week when I fly out of town.

For the “ad-hoc” work, I rely on a script library, which I keep as SQL Server Management Studio projects. I keep those scripts and projects backed using Microsoft Live Mesh, which synchronizes those files (along with a few other critical files and my IE Favorites) across not only my laptop and primary systems, but even with my Virtual Machines.

Also for my SQL Server systems I use the Standard Reports I’ve blogged about here. I also use Greg Larsen’s Database Dashboard, and a series of PowerShell scripts that work across my systems, alerting me to any problems. Of course I’m using SQL Server Agent Jobs quite a bit, and I also use Alerts and some Perfmon automation for my monthly baselining.

So – is this your experience as well? Do you get driven by both planned and unplanned work? What tools and processes do you use to keep it all straight with your SQL Server Instances?

T-SQL Tuesday: Easy Extended Properties

Mike C — Tue, 12 Jan 2010 05:18:00 GMT

Ahhh, attention to detail. I misread Adam's T-SQL Tuesday rules and posted early. So here I go again :) This time the puzzle is how to add and update metadata via extended properties in the database with minimal headaches.

SQL Server supports a great feature for storing database object metadata in the database in the form of extended properties. The main benefits of extended properties over custom solutions are: (1) extended properties are managed and stored internally by SQL Server; (2) many third-party applications retrieve/use extended properties. Perhaps the best known extended property is the "MS_Description" property, which is set by the "Description" field in the SSMS designer pages (shown below):

Extended properties are created as user-defined key/value pairs assigned to database objects. You can define the extended propery keys and their associated values to be anything you like, although there are a few commonly used ones (like "MS_Description"). The values are defined as sql_variant data types. Here are some sample key/value pairs:

Key	Value
MS_Description	This table represents the culmination of a lifelong dream.
Caption	GrandTotal Column
Last_Update	2009-07-09
Version	1.0.1

Extended properties allow you to store metadata in the database closely tied to database objects. So what's the downside to extended properties? Basically the only real issue with them is the nonintuitive syntax. In order to use extended properties you have to use stored procedures like sp_addextendedproperty. The following stored procedure call sets the MS_Description extended property on the bar column of the dbo.foo table:

EXECUTE sys.sp_addextendedproperty
@name = N'MS_Description',
@value = N'This is the bar column',
@level0type = N'SCHEMA',
@level0name = N'dbo',
@level1type = N'TABLE',
@level1name = N'foo',
@level2type = N'COLUMN',
@level2name = N'bar';

As you can see in the example, this syntax requires you to specify not only the extended property name/value pair and the individual components of the column's fully-qualified name, but also the types of each object. Updating and deleting extended properties using the system stored procedures requires the same type of information. A painful process to say the least.

The attached code sample creates an extended properties management schema named ExtProps. The ExtProps schema contains stored procedures that make it easier to add, update and delete extended properties. With these procedures you specify the object name using the standard SQL Server 1-, 2-, or 3-part naming convention along with the extended property name/value pair. The procedure will automatically figure out what type of object you're referencing. The following sample performs the same function as the previous example:

EXECUTE ExtProps.PropInsert N'dbo.foo.bar',
N'MS_Description',
N'This is the bar column';

The ExtProps.PropInsert, ExtProps.PropDelete and ExtProps.PropUpdate procedures use the object name you pass in to narrow down the type of object you're referencing. They then use the SQL Server catalog views to figure out exactly which object it should modify the extended properties on. The following table lists the types of object and the names expected by the procedures:

Name	Object Type
NULL	Current database
1-part	Schema
2-part (schema.object)	Table, View, Procedure, Function, Aggregate, Synonym, Queue
3-part (schema.object.column)	Column

Note that these procedures do not yet handle all the different objects to which you can assign extended properties. For instance, it does not currently handle extended properties for procedure parameters, event notifications, and a handful of others.

When you want to retrieve extended properties from the database you can use the standard system view, sys.extended_properties, or the fn_listextendedproperty system function. As an alternative, the ExtProps schema includes a new view -- ExtProps.Properties. This view has two advantages over the built-in system views:

In addition to all the data returned by the sys.extended_properties view the ExtProps.Properties view also includes schema, object, and column names.
The ExtProps.Properties view has triggers built on it that allow you to insert, delete, and update extended properties using standard SQL DML statements. The triggers actually call the relevant stored procedures, once for each row of extended property data being modified.

The figure below show sample output from the ExtProps.Properties view:

The script attached to this posting creates the ExtProps schema, procedures and view.