SQL 11 HADRON Learning Series – part 5

Microsoft CSS team released a new post of their learning series on SQL Server 11 HADRON. In this article they share how is the synchronous commit request processed and what is behind the scenes of that process. You can read the article here.

Advertisements

How to check who performed a SQL Server database backup

In this post I would like to share a simple way of extracting latest database backup including the user that has initiated it. Generally SQL Server engine does not store such info unless you have some custom piece of code (such as triggers) or you are using auditing in SQL Server 2008 and above. As you know each backup triggers an event that is stored in the application log of the operating system. This event includes the message that is written in the SQL Server error log and additionally has an user name, that triggered the event. Based on that and using PowerShell here is a simple list that returns newest 2000 backup events (full, differential or log) for all databases, sorting them descending by time. Columns returned are Time (the time the backup completed), Username (the user performed the backup) and Message(the message, which includes the type of backup and the database).

Get-EventLog "Application" -newest 2000 | Where-Object {$_.EventID -eq 18264 -or $_.EventID -eq 18265 -or $_.EventID -eq 18270}  |sort -descending TimeWritten |
Format-Table TimeWritten,UserName,Message -auto

As you can see I am querying directly the OS Application log and filtering three specific events:

  • EventID 18264 – Database full backup event
  • EventID 18265 – Database log backup event
  • EventID18270 – Database differential backup event

Also keep in mind that Backup and Restore events are not written to SQL Server Error log and NT Application event log if trace flag 3226 is enabled. So before trying to find out who performed a certain backup, check if those events are logged at first placeSmile

SQL 11 HADRON Learning Series – part 4

CSS SQL Escalation Services are continuing their learning series on SQL Server 11 HADRON. In their latest article they write about page level repair and its enhancements in the latest release. It also shows and some new features of the SSMS GUI introducing the “Resource Assistant” and the colorful timeline thingy Smile

The article can be read here. Enjoy! Winking smile

SQL 11 HADRON Learning Series

Here is a beginning of a series provided by CSS Escalation Services that targets the new feature of SQL Server Code Name “Denali” or SQL 11. The series is aiming at providing description of HADRON from supportability perspective.

Here is the link to the article: http://blogs.msdn.com/b/psssql/archive/2010/12/07/alwayson-hadron-learning-series-what-is-hadron.aspx

It is definitely something you would like to follow so you can get familiar with the support of all new coming with the new SQL Server version.

Concurrent Administrative operations

Have you ever wondered which administrative tasks on a database can be performed concurrently? Below is a simple schema where the black dot indicates that those two tasks cannot run simultaneously.

File shrink operations spend most processing time reallocating pages into areas retained after the shrink has completed; it then attempts to change the file size only as the last step. File shrink operations can be started while a backup is running, provided that the backup finishes before the file shrink operation attempts to change the size of the files.

Reference: http://msdn.microsoft.com/en-us/library/ms189315(v=SQL.90).aspx

By Ivan Donev Posted in DBA Tagged

How to write multi-server queries in SQL Server Management Studio

This post will show you in few quick steps how to write multi-server queries and what are the pitfalls where you should pay more attention.

Multiserver queries are something introduced in SSMS since SQL Server 2008 and can be very useful in many cases, especially in large environments with more that 50 instances for example.

Before you start using multiserver queries you should make sure you have SSMS 2008 or SSMS 2008 R2 installed. Then you should register all servers you wish to manage (find a related post here). If you have already done that, then go to Local Server Group (for sake of easiness of my demonstration I will not use a registered management instance and you should consider everything written here to be valid for both cases – with management server and with locally registered servers), right click on it and click on New Query button. Then a new query window is open which is the same as the one used to query a single instance but with one difference – before being able to execute a query, the management studio will try to connect to all registered instances so be careful and patient.

image

After writing a query and execute it what happens is that the query is executed on every instance and when the result is returned, the studio is merging it, adding one additional column to the returned dataset – the one with the server name as registered in the studio (not @@SERVERNAME but the name in the Registered Server window of the studio)

Common pitfalls and points to be noted:

1. Write queries valid for all engine editions of the group of server against you are running the multiserver query. If it is not compatible and error is raised and result form this server is not included (the query is returning results after all but not from all servers)

2. If you are creating objects in your query/script, be careful and perform a clean-up because this object is created in all servers!

3. If the first server from the list to be queried returns error, then the studio will not show any results (this is addition to point 1. above)

 

There are some properties that can be edited in the studio concerning returned results. Go to Options –> Query Results –> SQL Server –> Multiserver Results

image

There are three options which basically speak for themselves so no need of additionally describe them.

So, after writing so much theory, it is time to simply provide you a quick query, which can quickly provide you an overview of the environment you are managing.

 
   1: SELECT     

   2:        SERVERPROPERTY('ProductVersion') as [Product Version]   

   3:       ,SERVERPROPERTY ('ProductLevel') as [Product Level]   

   4:       ,SERVERPROPERTY ('Edition') as [Edition]   

   5:       ,SERVERPROPERTY('EngineEdition') as [Engine Edition]   

   6:       ,SERVERPROPERTY('IsClustered') as [IsClustered]

Enjoy and stay tuned for my next posts where I will summarize my research on extracting information about database backups, integrity checks, maintenance plans and SQL Server configuration.

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.