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 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.

Minimum requirements for starting SQL Server 2008 analysis services

In this article I will try to explain what are the minimum requirements to start SQL server analysis services instance in case of failure. Analysis Services is very sensitive on folder structure and file access but still basically depends on two components – registry and part of its original folder and file structure. I will start with the registry settings and their specifics (what you should pay attention to) and then proceed with the folder structure and what are the minimum required files and their permissions in order the service to start. So let’s begin.

1. Registry

As it comes to registry, SSAS instance information is kept in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<SSAS10.instance name>

Important sub-hives are:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS10.MSSQLSERVER\CPE

and

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS10.MSSQLSERVER\Setup

In the CPE you should pay attention to ErrorDumpDir registry key – this is the first important folder path, valuable for the instance to start

In the Setup part you should note SQLPath and SQLBinRoot paths as they are pointing towards binaries of the SQL Server installation and the context of which the instance is working. So far we have three registry keys important: ErrorDumpDir,SQLPath and SQLBinRoot. Those keys are one of the first read when the service is started. They also contain folders that should be accessible by the SSAS service account at any times (this means that the account should have at least read permissions on them in order to start)

2. Folder and file structure

Folder-wise SSAS needs the following:

  • Config folder – it is always <SQLPath>\Config
  • Log folder – HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS10.MSSQLSERVER\CPE\ErrorDumpDir
  • Data folder – HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS10.MSSQLSERVER\Setup\DataDir

Now SSAS does need the Config folder and the msmdsrv.ini file in it. The important thing here is that there is no need the file contain the actual settings – it can be copied from another (working) instance. It can be even a balnk file. If you leave the file blank, then when the service starts it will fill it with default settings.

Interesting behavior is noticed with the Log and Data folders – if the log folder is not accessible it is not crucial for service startup. Further errors probably will be raised but the service will be working. As for the data folder – if it not accessible or missing, then the instance will create a new one with defaults contents. Of course if the AS account has permissions to do so.

Having this said, when you start troubleshooting SSAS service instance startup failure (Error IDs 1069 and 1042 for example), you should follow these steps:

    1. Extract all folders from the registry and check their availability – if something is missing try to restore it from backup
    2. Check the Config folder if exists and it has necessary permissions set
    3. Check the msmdsrv.ini file if exists and its contents. Delete the contents if necessary.
    4. Check if something is using default SSAS TCP/IP port 2083 (you can use netstat /b in the command prompt)
    5. These four steps should cover most of the cases of SSAS startup failures.

In general there is not much documentation from Microsoft on the matter and the errors when the service fails to start are quite general. It is difficult to troubleshoot because there is also no logging started before the actual service startup. But having the service started once, even with issues it would be easier to troubleshoot as you will have error logging and access to configuration values from SSMS.