Category Archives: SQL

Monitoring SQL Server – in praise of MOM!

Posted on by
Reply

We’re currently building an SQL 2008 server cluster which will use resilient SAN-attached storage. This will provide the University with a leading edge hosted SQL service on which to safely manage its databases as well as making available a whole host of new SQL 2008 Business Intelligence services. More news about that to follow in future blogs…

In order to decide upon the best RAID configuration for the new service (i.e. whether to configure the disks to get biggest capacity or best performance) I’ve had to gather some stats about the transactions per second (TPS) on the current (SQL 2000/2005) servers. To this end, I started monitoring with SQL Profiler in conjunction with Performance Monitor. Now, I don’t know if you’ve ever done this, but let me tell you, it is something of a black art and unless I’m very much mistaken, it does come under the category of Rocket Science if only because it makes you want to hurl your computer at the moon! Fortunately, all was not lost – to my rescue came MOM: Microsoft Operations Manager. You can Google for MOM and get all sorts of info but, for a quick overview of what it is, this covers it really:

http://en.wikipedia.org/wiki/System_Center_Operations_Manager (and yes they changed the name from MOM to SCOM but we all still call it MOM out of habit…)

So, how did MOM help in this case? The MOM servers run by the Windows Infrastructure Team have (amongst a lot of other things!) a MOM SQL Management Pack installed which means that when they connect to SQL servers, they are able gather SQL specific data in addition to the other more generic monitoring of disks, memory, network connectivity, etc. So, MOM has been quietly monitoring TPS and storing the data into MOM reports for some time. All I had to do was request the report and hey presto! Here’s a couple of examples:-

This image shows a top-level report which gives a broad view of various SQL-specific data over the last quarter of 2008 for a range of performance data on one of the SQL servers:-

SQL report details

And then… you can drill into, for example, the Transactions per Second data and obtain a more detailed view….

SQL report details

A lot prettier (in so many ways!) than Perfmon and Profiler. These are just 2 basic reports but there is a wealth of other information that can be got from MOM, and not just for SQL – and not just in retrospect. MOM carries out service-specific monitoring for Exchange, IIS, Terminal Services, ISA… in fact all of the Windows Server services run by our team, keeping an eye on services – 24/7 – and alerting us to any problems or errors as soon as they arise.

Money for nothing and your learning for free!

Posted on by
Reply

[Disclaimer: Though the title may suggest it, this post has no information about gaining money for nothing; only learning for free. If you have a fool-proof way of getting money for nothing, please add it as a comment.]

You may already know that Microsoft have compiled a huge collection of self-paced learning materials at the Microsoft Learning site. What you might not know, is that they regularly publish courses for everyone’s favourite price – FREE!

These free courses and clinics are usually introducing new features in new versions of products, or giving giving overviews rather than digging into deep technical detail, but a lot of the time that may be just what you want.

Some examples of free content that are currently available are:
Database Fundamentals in Microsoft SQL Server 2008
Introducing Windows Server 2008
ASP.NET for PHP Developers: Introduction to ASP.NET

They handily provide a catalogue of free products, which I suggest you have a look at. If you want to take up one of the free offers, you don’t have to complete the course right away; typically you just activate a subscription to the content, which may give you access to it for 3, 6 or 12 months.

Happy learning!

Monday II: Keynote

Posted on by
Reply

The keynote was given by Brad Anderson the General Manager of Microsoft’s Management and Services division and focused on ‘Dynamic IT.’ One of the main elements was Virtualization and its management. The video of the Keynote will be available online soon if it is not already but here are some notes that I jotted down.

Some interesting figures were mentioned.

  • Most servers across the word are running at less than 10% utilisation
  • ‘In the future’ a predicted 5% of the worlds energy consumption will be by the Datacenter
  • Microsoft’s use of Virtualization has seen energy savings of up to 90%.

We saw a demo if System Center Virtual Machine manager including the live migration feature in Windows Server 2008 R2. Application virtualization was also mentioned and we were told that this will make application compatibility issues a thing of the past. Brad Anderson also said that Microsoft had observed a trend in enterprises towards only running server services on physical machines ‘by exception’.

A demo of Microsoft System Center Operations Manager 2007 R2 Beta then followed which supports cross platform extensions and will be able to monitor Solaris, Suze, Redhat, MySQL, Oracle on top of the services it can currently manage.

The keynote then went in to detail on Windows Server 2008 R2 (M3 available for download) but I will post separately on this.

The Keynote finished with an overview of Microsoft Online services focusing on a mixed local and hosted implementation of Microsoft Exchange. The service is due for release in EMEA during spring 2009.

There were also demonstrations of features of the next version of SQL server ‘Kilimanjaro’ and some other areas which Jonathan may like to discuss.

SQL Server 2008 arrives

Posted on by
Reply

At the moment WIT run a collection of SQL 2000 and 2005 servers that host around a hundred databases of varying size and importance to the institution. The lion’s share of those databases are currently on the older SQL Server 2000, so several months ago, with the end of mainstream support for that product approaching, we started making plans for migration.

We’ve been keeping a close eye on the development of the latest version, SQL Server 2008, since it was announced, and trialing pre-release versions. SQL Server 2008 offers a number of advantages over previous versions and the migration path from SQL 2000 to 2005 or 2008 is much the same, so we’ve opted to take those databases that are currently on SQL 2000 straight to 2008, rather than moving them twice.

SQL Server 2008

Last week, we were fortunate to have Microsoft’s Andrew Fryer spending a day with us, discussing our migration plans. Since none of our databases do anything especially odd (not that some of them aren’t complex), SQL Server 2008’s comprehensive Upgrade Advisor was able to tell us that we didn’t need to make any changes to the databases before moving them to the new version.

There are some things that Upgrade Advisor suggests for after the migration, such as re-writing DTS packages using the SSIS technology that replaced DTS in SQL Server 2005, but existing DTS packages will work in SQL Server 2008, so our advice is that the time to migrate from DTS to SSIS is when you need to alter a package.

This week SQL Server 2008 has been released to manufacture, so we’ll be moving forward with building production and test systems with the finished code. We’ve planned a setup which provides higher availability and better disaster recovery than we’ve previously implemented, and we’re looking forward to taking advantage of some of the new features (I’m especially looking forward to working with the SQL Server PowerShell functionality!).