SQL Server 2008 SP3 available for download

It is been a long time since my last post and my list for blog article candidates constantly keep growing Smile I hope soon I will have enough time to return back to normal blogging speed.

Now I would like to share the new Service pack release for Microsoft SQL Server 2008 – list of all fixes can be found here. Actually as far as I could see those changes actually include all fixes till CU4 of SP2. So if you already have CU5 in place then applying SP3 is not really of a use. The SQL Server build version of SQL Server 2008 SP3 is 10.0.5500 (neat one, right? Smile)

Download SQL Server 2008 SP3 RTM

And happy patching Open-mouthed smile

SQL Server performance and troubleshooting tools, tips and tricks

I’ve planned to write a series of post regarding different tools which you can use for performance analysis and troubleshooting but tonight I found that there is already such series posted. I have to say it is pretty neat and has a lot of amazing things to read and get to know. This includes RML Utilities, XPerf, Process monitor, Debug diagnostics and Crash rules. So instead of covering pretty much the same, I’d like to share the round-up post of the series so you can enjoy it.

Troubleshooting SQL: Tools Tips and Tricks round-up

I can only add to this that you can also consider PAL 2.0 (PAL@Codeplex) which  can help you directly analyze performance counters logs and prepare a template for counter collection. Also there is one more thing that I completely love about this tool – almost all counters have thresholds defined according to best practices and added explanation with reference links for each counter. The tool is pretty straight forward and after analysis it provides you with a report with marked problematic areas (IO, CPU, Memory or network).

So get introduced to the post I shared with you and all the tools, tips and tricks it provides and have a look at PAL 2.0 Tool which I think you will find very useful!

PSSDIAG and SQLDIAG Manager

Today I am going to share a completely new tool that has been released last couple of weeks. The tool is called “ PSSDIAG/SQLDIAG Manager”. The tools is aiming at providing graphical interface for configuring custom collection sets for SQLDIAG and PSSDIAG utilities. Those two utilities are used for collecting performance and statistic data by Microsoft. I will talk further more on those tools, their aim and how to use them, but it is going to be in another post. For now I will stick to the new tool I am completely thrilled by.

I have experienced a lot of difficulties configuring any of the predefined .XML files used for SQLDIAG collection and now when I have a graphical interface the life just became easier. So let’s start with the quick introduction. The tool has a single screen where you can configure everything:

image

On the top left we have the menu for choosing platform type – x86, x64 or Itanium. On the top of the screen you can choose the version of the SQL Server that you are preparing the configuration. Of course so far we have SQL Server 7.0, SQL Server 2000, 2005 and 2008. Hopefully we will have soon and SQL 11Winking smile

I was kind of amazed seeing on top left corner a small edit box for the MS Case that the configuration is prepared for. This will not be used by the general DBA however it surely shows that the tool is the same as the one MS Engineers are using (used or modified).

Next we have the connection part:

image

The important thing here is the Machine name and Instance name configuration. It is important to enter specific instances if you are troubleshooting a server with several instances. Machine name can come useful when you are collecting information for SQL clustered instance.

The other three sections of the screen are for configuring Performance counters collection, predefined diagnostics and traces for DB Engine and Analysis Services engine. I still have not played enough with those in order to share with you how and what but I believe its going to be easy enough for you to check that out by yourself Smile

Once you have the configuration in place you can proceed with saving it. The screen is as follows:

image

You have the possibility to just have the XML configuration file and use it directly or generate a CAB file which will include everything needed for immediate execution and starting of the collection on the specified instance. Personally I prefer using the CAB file as it comes with several other scripts/configurations.

Download SQLDIAG Manager tool.

Well from now on you can just enjoy configuring SQLDIAG and generate your own investigation templates and scripts. Even if you are not that keen on altering defaults you can still use the tool for entering Machine and Instance name if you need to.

I have to say that with this tool Microsoft have a complete set of tools for performance analysis that are easy to use configure and analyze.

– Configuration of analysis tools – SQLDIAG Manager, PAL Tool

– Analysis – PAL and SQL Nexus

I am planning to do another post for those tools so you can have the big picture in place (if you already don’t have it). So stay tuned!

List all full-text search catalogues on an instance

With the following script you can easily find all FTS catalogues and their state on the SQL Server instance.

  1. select sd.name as database_name,fts.name as fts_name,fts.catalog_id,
  2.        fts.is_importing,fts.is_paused,fts.status_description
  3. from sys.dm_fts_active_catalogs fts
  4. inner join sys.databases sd
  5.       on fts.database_id = sd.database_id

This could come handy when you are migrating a database as you should take care of rebuilding all FTS catalogues and for that – you will need a list of what should be rebuilt.

Database upgrade to SQL Server 2008–Tools and Approaches

In this post I’d like to share an interesting whitepaper on Database upgrade to SQL Server 2008. The document describes different tools and approaches that can be used.

“Performing an upgrade is a complex and often risky project. A successful upgrade can provide an organization with a modern platform for development and production. An unsuccessful upgrade can cause lost time and money, and it can create a bad perception for the future.

This paper documents approaches and tools that can help DBAs and developers to achieve successful and mostly painless upgrade of SQL Server databases from Microsoft® SQL Server® 2000 or SQL Server 2005 to SQL Server 2008 (or SQL Server 2008 R2).

In the methodology this paper discusses, a SQL Server workload is captured on customer production environment, and then this identical test workload is run on the old and new versions of SQL Server in a test environment to compare performance and isolate any problem queries. For this approach, we use components from the “RML Utilities for SQL Server” set of tools.”

For more details, please refer to this whitepaper:Database Upgrade to SQL Server 2008 Tools and Approaches

SQL Server CMS – complete solution

While I was preparing a small presentation on SQL Server centralized management I came across a very elaborate solution on the subject, posted in Codeplex. The solution incorporates three different techniques for gathering, storing and presenting information for a complete environment. The solution is based on Policy Based Management, Management DataWarehouse (Data collection), MAPS tool (Microsoft Assessment and Planning Solution Accelerator) and Report builder for reporting purposes.

The solution provided in Codeplex can be easily modified, expanded and/or rebuild according to the environment specifics but in general it provides you a quick way of implementing a Centralized Management Solution without having to invest in third party tools but use only already installed SQL Servers.

Here is the link to the solution. There is also a very good presentation provided with the .zip file, so enjoy.

SQL Server Centralized Management–registering multiple instances at once

I have decided to dig into the field of centralized management of large amount of server so I am starting a series of posts in which I will just share my struggles in the area. I do not pretend neither to provide you the best solution, nor to be the most useful, bullet-proof, etc.

The idea came while I was supposed to investigate numerous servers for existing backups and as it seems there is not an easy way to do so without using a centralized management technique. A have heard of SCOM, SCCM, different third-party tools but still SQL Server has also their own suggestion – Centralized Management Server (and its local version of Locally registered servers). It is not discovering the electricity, just as of SQL Server 2008 it really gets useful. Let’s suppose you administer a hundred instances and you want to check last backup of all system and user databases or to check what are the server memory settings or whatever else you want. The easiest would be to just write a query, connect to each instance, execute it, copy the result and after 100 executions – summarize the result. Well…I am not lazy but not crazy too! In SSMS 2008/R2 you can register more than one instance and then run a multiserver query. This means that you execute a single query and as a result you have merged results from all servers. Well…life is easy (I guessed)…

A good reading for an introduction is the following article: http://sqlserverpedia.com/wiki/Central_Management_Server (please, watch and the video as Brent Ozar is quickly explaining what is that SQL CMS, how to use it and some other good points of the subject)

In this post I will dig deeper into registering a large amount of server (as doing it by hand is not very desirable (especially for 100+ instances). So – the trivial task of registering a single server is (I will show it in local context, the same goes and when you have a dedicated CMS):

1. Open SSMS (SQL Server Management Studio 2008 or 2008R2)

2. Open Registered Servers window (Ctrl+Shift+G)

3. Right click on Local Server Group and click on New Server Registration

image

4. Fill in required information

image

5. Click Save and you have your server registered.

So far, so good, but what happens if you have 100 instances to register?!? Well you can export and import server lists into CMS or local server group so I decided to simulate an export file and then import it. The exported file is an XML file with .regsrvr extension. I wrote a query that generates the XML file based on a table, which contains all instances to be registered. Find below the DDL for the table:

CREATE TABLE [dbo].[AllInstances](

    [RegName] [varchar](50) NOT NULL,

    [Address] [varchar](100) NOT NULL

) ON [PRIMARY]

RegName is the name, that to be displayed in SSMS

Address is the instance name which to be used for connection

After inserting all instances, run the following query and the result save to an regsrvr file.

--initial header

PRINT

'    <?xml version="1.0"?>

    <model xmlns="http://schemas.serviceml.org/smlif/2007/02">

      <identity>

        <name>urn:uuid:96fe1236-abf6-4a57-b54d-e9baab394fd1</name>

        <baseURI>http://documentcollection/</baseURI>

      </identity>

      <definitions xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns="http://schemas.serviceml.org/smlif/2007/02">

        <document>

          <docinfo>

            <aliases>

              <alias>/system/schema/RegisteredServers</alias>

            </aliases>

            <sfc:version DomainVersion="1" />

          </docinfo>

          <data>

            <xs:schema targetNamespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">

              <xs:element name="ServerGroup">

                <xs:complexType>

                  <xs:sequence>

                    <xs:any namespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" processContents="skip" minOccurs="0" maxOccurs="unbounded" />

                  </xs:sequence>

                </xs:complexType>

              </xs:element>

              <xs:element name="RegisteredServer">

                <xs:complexType>

                  <xs:sequence>

                    <xs:any namespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" processContents="skip" minOccurs="0" maxOccurs="unbounded" />

                  </xs:sequence>

                </xs:complexType>

              </xs:element>

            </xs:schema>

          </data>

        </document>

      </definitions>

  <instances xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns="http://schemas.serviceml.org/smlif/2007/02">'

 --group information for locally registered servers

 PRINT

'    <document>

      <docinfo>

        <aliases>

          <alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup</alias>

        </aliases>

        <sfc:version DomainVersion="1" />

      </docinfo>

      <data>

        <RegisteredServers:ServerGroup xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">

          <RegisteredServers:RegisteredServers>

            <sfc:Collection>'

--list the info for the servers to be registered

DECLARE

    @RegName varchar(100),

    @Address varchar(100)


DECLARE List CURSOR

FOR

SELECT RegName, Address FROM AllInstances


OPEN List

FETCH NEXT FROM List INTO @RegName,@Address

WHILE @@FETCH_STATUS<>-1

BEGIN

    PRINT

'              <sfc:Reference sml:ref="true">

                <sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/RegisteredServer/' + @RegName + '</sml:Uri>

              </sfc:Reference>'



FETCH NEXT FROM List INTO @RegName,@Address

END

CLOSE List

DEALLOCATE List


 PRINT

 '           </sfc:Collection>

           </RegisteredServers:RegisteredServers>

          <RegisteredServers:Parent>

            <sfc:Reference sml:ref="true">

              <sml:Uri>/RegisteredServersStore</sml:Uri>

            </sfc:Reference>

          </RegisteredServers:Parent>

          <RegisteredServers:Name type="string">DatabaseEngineServerGroup</RegisteredServers:Name>

          <RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>

        </RegisteredServers:ServerGroup>

      </data>

    </document>'


--detail for all registered instances

DECLARE List CURSOR

FOR

SELECT RegName, Address FROM AllInstances


OPEN List

FETCH NEXT FROM List INTO @RegName,@Address

WHILE @@FETCH_STATUS<>-1

BEGIN

    PRINT

'    <document>

      <docinfo>

        <aliases>

          <alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/RegisteredServer/' + @Address + '</alias>

        </aliases>

        <sfc:version DomainVersion="1" />

      </docinfo>

      <data>

        <RegisteredServers:RegisteredServer xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">

          <RegisteredServers:Parent>

            <sfc:Reference sml:ref="true">

              <sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup</sml:Uri>

            </sfc:Reference>

          </RegisteredServers:Parent>

          <RegisteredServers:Name type="string">' + @RegName + '</RegisteredServers:Name>

          <RegisteredServers:Description type="string" />

          <RegisteredServers:ServerName type="string">' + @Address + '</RegisteredServers:ServerName>

          <RegisteredServers:UseCustomConnectionColor type="boolean">false</RegisteredServers:UseCustomConnectionColor>

          <RegisteredServers:CustomConnectionColorArgb type="int">-986896</RegisteredServers:CustomConnectionColorArgb>

          <RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>

          <RegisteredServers:ConnectionStringWithEncryptedPassword type="string">server=' + @Address + ';trusted_connection=true;pooling=false;packet size=4096;multipleactiveresultsets=false</RegisteredServers:ConnectionStringWithEncryptedPassword>

          <RegisteredServers:CredentialPersistenceType type="CredentialPersistenceType">None</RegisteredServers:CredentialPersistenceType>

        </RegisteredServers:RegisteredServer>

      </data>

    </document>'



FETCH NEXT FROM List INTO @RegName,@Address

END

CLOSE List

DEALLOCATE List

PRINT

'  </instances>

</model>'

After doing this you will be able to import the configuration and have all the server registered.

Please note that there are no groups defined and the script can definitely be optimized…developers are welcome to play with it and provide even a better solution including group information.

In another post I will show you some multiserver query examples, SSMS options for multiserver queries and anything else that I have come across while working with centralized management.

Introduction to Microsoft® Windows Azure

Yep. You’ve read the title correctly – Microsoft will soon provide and an OS in the cloud. After starting SQL Azure now it seems that the company is heading for the pioneering place in cloud computing and cloud services. It would be a difficult transition, though, as first these two products should prove that they are stable, reliable and worth the investment. And…the IT guys to realize that there is no escape from “the cloud” and in the near future they will have to learn that new stuff as it is inevitable when it comes to support and development of new applications.

A simple whitepaper from Microsoft could describe in short what is that Windows Azure: Introducing the Windows Azure Platform

Believe it or not soon more and more companies will head for cloud computing including outsourcing companies so that’s what is worth learning from its very beginning.