Robert's SQL Blog

If you are running SQL Server 2008 you might know this job. It’s created when you install SQL Server and maintains some of the policy subsystem. Some if you might have experienced errors with this job. Especially when you are running on a cluster. Some information on this error can be found in the following KB article: http://support.microsoft.com/kb/955726.

Recently I came across a failing syspolicy job on a cluster. Immediately I thought the KB article applied here, especially when the client mentioned the job had never succeeded. But this was not the case. The error was different and over time the error changed as well. Step 3, the PowerShell command, was failing with an “cannot create process because file is in use.”.

So, that’s all, no mentioning what file whatsoever. I took the statement from the jobstep and ran it in a SQLPS sessions. That worked. So, nothing wrong with PowerShell (how could it be?).

Next step was to identify what file was in use. For this I used Process Monitor from SysInternals, available on TechNet. I started Process Monitor and started the job waiting for it to fail. I then took the output from Process Monitor and filtered it on the SQLAGENT process, searching for clues. And I found this:

10:29:39.4536352 AM	SQLAGENT.EXE	5620	QueryOpen	C:\Program	SUCCESS	CreationTime: 5/21/2011 5:12:59 PM, LastAccessTime: 5/21/2011 5:12:59 PM, LastWriteTime: 5/21/2011 5:12:59 PM, ChangeTime: 5/29/2011 8:10:54 AM, AllocationSize: 0, EndOfFile: 0, FileAttributes: A
10:29:39.4539617 AM	SQLAGENT.EXE	5620	QueryOpen	C:\Program	SUCCESS	CreationTime: 5/21/2011 5:12:59 PM, LastAccessTime: 5/21/2011 5:12:59 PM, LastWriteTime: 5/21/2011 5:12:59 PM, ChangeTime: 5/29/2011 8:10:54 AM, AllocationSize: 0, EndOfFile: 0, FileAttributes: A
10:29:39.4541972 AM	SQLAGENT.EXE	5620	CreateFile	C:\Program	SHARING VIOLATION	Desired Access: Read Data/List Directory, Execute/Traverse, Read Attributes, Synchronize, Disposition: Open, Options: Synchronous IO Non-Alert, Non-Directory File, Attributes: n/a, ShareMode: Read, Delete, AllocationSize: n/a
10:29:39.4544379 AM	SQLAGENT.EXE	5620	CreateFile	C:\Program	SHARING VIOLATION	Desired Access: Execute/Traverse, Synchronize, Disposition: Open, Options: Synchronous IO Non-Alert, Non-Directory File, Attributes: n/a, ShareMode: Read, Delete, AllocationSize: n/a
10:29:39.4548114 AM	SQLAGENT.EXE	5620	WriteFile	F:\MSSQL10_50.KCCSQL\MSSQL\Log\SQLAGENT.OUT	SUCCESS	Offset: 10,784, Length: 464

So, the Agent was trying to get a handle on a file named C:\Program but couldn’t. I checked the file system and there was indeed a file Program in C:. Actually, Windows already warned about this file when I logged onto the machine. The file was 0KB in size and created some time ago. Next step was to identify the process that was creating this file. Since the file was in use according to the Agent it should be easy to get the process using another SysInterals tool, Process Explorer. I opened Process Explorer and searched for C:\Program. It returned the guilty program for me.

Next step was to find out why this program created this file. In this case it turned out someone forgot to put double quotes around a value in a configuration file. After I corrected this and restarted the service I was able to delete the file Program. I went back to the Agent and started the syspolicy job. This time with success.

Again, the SysInterals tools prove to be of great value when searching for problems beyond the obvious. Although it’s questionable why a program would create a file named Program in this folder but it shouldn’t fail the job.

In the past I have attended DevDayhs several times. A nice local event with great sessions about development. Either with .NET, Wp7, Blend, Lego or anything. This year I went in as a speaker. Which was fun to. I had a great time doing the session on SQL Server and PowerShell. As promised I have a link here to the scripts I used and the Powerpoint.

Add-Sql Script from BooksOnline
SqlServer Script with SMO functions like backup, create DB
Powerpoint slides

If you missed DevDays or want to view some of the sessions you missed check them out at Channel 9, http://channel9.msdn.com/Events/DevDays/DevDays-2011-Netherlands

The sessions are a mix of Dutch and English. All non-dutch speakers are English of course, but some of the dutch speakers did an English session as well.

If you have questions feel free to contact me through this site or twitter.

Sometimes when you patch SQL Server you get an error. Most times when you get an error it’s your own fault, or better someone else’s. I had such an experience recently and wanted to share some information on why this is happening and of course how to resolve it. There is already a great blog post on the subject. Although the error number is different it does apply; http://blogs.msdn.com/b/sqlserverfaq/archive/2009/01/30/part-1-sql-server-2005-patch-fails-to-install-with-an-error-unable-to-install-windows-installer-msp-file.aspx

So what’s the problem. In this case a SQL 2005 installation was upgraded to SP1 – SP2 – CUSomething and needed to be patch to the next CU. But applying this CU failed with the error in this title. First thing to check with installer errors is the summary.txt file in the Bootstrap\Log folder. So here is an excerpt of my log file.

Product Installation Status
Product                   : SQL Server Database Services 2005 (MSSQLSERVER)
Product Version (Previous): 3077
Product Version (Final)   :
Status                    : Failure
Log File                  : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB976952_sqlrun_sql.msp.log
Error Number              : 1635
Error Description         : Unable to install Windows Installer MSP file

As this is a summary there is not much detail about the actual error. All you can tell I’m coming from SQL 2005 build 3077. Luckily the location of the detailed log is mentioned. Now, here are some lines of interest from that file.

MSI (s) (A4:A8) [13:20:30:996]: Opening existing patch ‘C:\WINDOWS\Installer\3d482d64.msp’.
MSI (s) (A4:A8) [13:20:30:996]: Note: 1: 2203 2: C:\WINDOWS\Installer\3d482d64.msp 3: -2147287038
MSI (s) (A4:A8) [13:20:30:996]: Couldn’t find local patch ‘C:\WINDOWS\Installer\3d482d64.msp’. Looking for it at its source.
MSI (s) (A4:A8) [13:20:30:996]: Resolving Patch source.
MSI (s) (A4:A8) [13:20:30:996]: User policy value ‘SearchOrder’ is ‘nmu’
MSI (s) (A4:A8) [13:20:30:996]: User policy value ‘DisableMedia’ is 0
MSI (s) (A4:A8) [13:20:30:996]: Machine policy value ‘AllowLockdownMedia’ is 0
MSI (s) (A4:A8) [13:20:30:996]: SOURCEMGMT: Media enabled only if package is safe.
MSI (s) (A4:A8) [13:20:30:996]: SOURCEMGMT: Looking for sourcelist for product {4A35EF4A-D868-4B15-A84D-3E8925AA9558}
MSI (s) (A4:A8) [13:20:30:996]: SOURCEMGMT: Adding {4A35EF4A-D868-4B15-A84D-3E8925AA9558}; to potential sourcelist list (pcode;disk;relpath).
MSI (s) (A4:A8) [13:20:30:996]: SOURCEMGMT: Now checking product {4A35EF4A-D868-4B15-A84D-3E8925AA9558}
MSI (s) (A4:A8) [13:20:30:996]: SOURCEMGMT: Media is enabled for product.
MSI (s) (A4:A8) [13:20:30:996]: SOURCEMGMT: Attempting to use LastUsedSource from source list.
MSI (s) (A4:A8) [13:20:30:996]: SOURCEMGMT: Trying source e:\01dddf5653431568a309\HotFixSQL\Files\.
MSI (s) (A4:A8) [13:20:30:996]: Note: 1: 2203 2: e:\01dddf5653431568a309\HotFixSQL\Files\sqlrun_sql.msp 3: -2147287037
MSI (s) (A4:A8) [13:20:30:996]: SOURCEMGMT: Source is invalid due to missing/inaccessible package.
MSI (s) (A4:A8) [13:20:30:996]: Note: 1: 1706 2: -2147483647 3: sqlrun_sql.msp
MSI (s) (A4:A8) [13:20:30:996]: SOURCEMGMT: Processing net source list.
MSI (s) (A4:A8) [13:20:30:996]: Note: 1: 1706 2: -2147483647 3: sqlrun_sql.msp
MSI (s) (A4:A8) [13:20:30:996]: SOURCEMGMT: Processing media source list.
MSI (s) (A4:A8) [13:20:32:011]: SOURCEMGMT: Resolved source to: ‘sqlrun_sql.msp’
MSI (s) (A4:A8) [13:21:27:667]: Note: 1: 1314 2: sqlrun_sql.msp
MSI (s) (A4:A8) [13:21:27:667]: Unable to create a temp copy of patch ‘sqlrun_sql.msp’.
MSI (s) (A4:A8) [13:21:27:667]: Note: 1: 1708
MSI (s) (A4:A8) [13:21:27:667]: Note: 1: 2729
MSI (s) (A4:A8) [13:21:27:667]: Note: 1: 2729
MSI (s) (A4:A8) [13:21:27:683]: Product: Microsoft SQL Server 2005 — Installation failed.

There is one thing that you can notice here. Apparently 3d482d64.msp and file sqlrun_sql.msp are missing. One file from C:\Windows\Installer and one apparently from the temporary folder we are installing the hotfix from. The first answer is correct, the second not. During extraction of the hotfix I noticed the folder getting created and it was different then the one listed here. So, a file is missing. All you  need to find out is which file.

Now there are a couple of leads to get you to the actual missing file. It’s not sqlrun_sql.msp. This file is included with all packages and the 3d482d64 is actually a randomly renamed version of sqlrun_sql.msp at the time the patch was applied. We need to know what the original package was.

A search for the guid inside the registry failed (4A35EF4A-D868-4B15-A84D-3E8925AA9558). It turns out the installer saves the guid in a different format. There is some documentation on this. Basically the characters are reversed by sequence. The complete string in my case became A4FE53A4868D51B48AD4E39852AA5985 which can be found in the registry. Another key from the package was the instance key (this is one of the first guids in the logfile). With the same conversion trick you can find that in the registry under the key HKLM\Software\MicrosoftWindows\CurrentVersion\Installer\UserData\S-1-5-18\Products\<GUID>. This is the key for the installed instance, in my case SQL Server 2005. More interesting, underneath it is a subkey Patches with a value of the earlier guid (4A35EF4A etc.). One of the values there stated SQL Server 2005 Service Pack 2.

In the post mentioned earlier it stated to copy the correct msp file with the correct name to Windows\Installer. That works if you are only upgrading one part of SQL, eg the engine. If you are also updating Reporting Services or Integration Services more files need to be copied. What I did was download the servicepack 2 files, exctracted them by running the setup with the /x parameter. When asked for the path, I provided the path from the logfile (e:\01dddf5653431568a309). After the package was extracted I ran the hotfix that failed before again and it succeeded.

Lesson learned is to never throw away files in system folders. We all know this but in this case diskspace was getting tight and someone decided to remove old files. Even then, get bigger disks. Again, look at this post: http://blogs.msdn.com/b/sqlserverfaq/archive/2009/01/30/part-1-sql-server-2005-patch-fails-to-install-with-an-error-unable-to-install-windows-installer-msp-file.aspx. It’s somewhat different from my problem but the steps outlined are quite alike. There are some good resources mentioned there as well.

Inmiddels zit het  erop en ben ik weer veilig op nederlandse bodem. SQL Bits heeft een goede indruk op mij achtergelaten. Het niveau van de sessies was hoog en de keuze divers. De locatie was prachtig en goed bereikbaar. Het station was 10 minuten lopen en een treinritje naar Gatwick Airport kost rond de 9 pond. Een half uur later sta je op het vliegveld. Vliegen naar Amsterdam was in een uurtje gedaan dus voor je het weet zit je weer in Nederland.

Ik eindigde de tweede dag met een sessie van Allan Mitchell over StreamInsight. Er wordt veel over gepraat en iedereen heeft inmiddels wel de demo’s gezien over beurskoersen etc. Allan liet echter ook een voorbeeld zien dat DBA’s meer aanspreekt waarbij hij profiler tracedata via een StreamInsight adapter liet zien. StreamInsight heeft zeker toekomst maar het is mij nog steeds niet helemaal duidelijk waar en wanneer ik nu StreamInsight krijg. De dag werd afgesloten met een hapje en een drankje. Drankjes waren niet gratis maar vouchers kon je verdienen door met de sponsors te praten of tijdens sessies goede vragen te stellen. Of in de buurt van Andre Kamman te blijven die een soort voucher magneet heeft/is.

De laatste en gratis dag was ook het drukst bezocht. Deze dag was ook volgepakt met sessies waarvan de eerste al om 08:10 begon. Dus als je dacht dat SQL Zaterdag al vroeg was, het kan vroeger. De eerste sessie heb ik overgeslagen om echt even van een prachtige ochtend te genieten. Ik was op tijd terug om de sessie van Ross Mistry over het consolideren en virtualiseren van SQL Server. Een aantal kent Ross Mistry nog wel van de Euro Pass Conference waar hij ook regelmatig aanwezig was. Tegenwoordig werkt hij voor Microsoft. Hij heeft diverse boeken geschreven over diverse onderwerpen. Het was echt een goed verhaal waar goed de verschillen naar voren kwamen tussen consolideren van databases, instances of virtualiseren. Maar ook de kosten en het beheer dat hiermee gepaard gaat. Dit was ook een van de eerste sessies over dit onderwerp waarbij ik zag dat virtualisatie vaak ook meerdere verschillende Windows versies, verschillende SQL versies etc. met zich meebrengt. Ook zie je vaak dat het aantal virtuele servers behoorlijk toeneemt ten opzichte van het aantal fysieke servers.

Daarna heb ik een sessie gevolgd van Gary Short over ORM tooling en het beeld dat daarbij leeft. Traditioneel is er een mismatch tussen dba’s en developers. DBA’s willen alles netjes in stored procedures terwijl developers het liefst los gaan met LINQ en dynamic SQL. Hij had voor beide kanten goede punten en uiteindelijk is het beste van twee werelden mogelijk, stored procedures in combinatie met een ORM tool. Hij sloot zijn sessie af met een pleidooi voor NoSQL achtige toepassingen en liet een demo zien van RavenDB. Het zal zeker toepasbaar zijn en schalen maar niet overal en altijd. Net als met SQL, it depends.

De laatste sessie voor de lunch was van Klaus Aschenbrenner die een verhaal en wat demo’s had over de interne structuur van een datafile. Dingen die je wel gelezen of gehoord hebt maar hier ineens tot leven kwamen. Na de lunch was het weer tijd voor de sponsorsessies. Omdat de lunch bestond uit een ondefinieerbare lunchbag heb ik van de gelegenheid gebruik gemaakt  om in Brighton te gaan lunchen. Ik kwam 10 minuten te laat terug voor de sessie van Andre Kamman. Omdat ik deze gemist had op SQL Zaterdag wilde ik hem graag zien. Dit was echter jammer maar helaas. Andre had zeker niet de grootste ruimte maar het zat echt stampvol. Gelukkig was het ook vanuit de deuropening te volgen. Uiteindelijk werd de zaterdag afgesloten met een sessie over Performance Tuning en aansluitend weer een hapje en een drankje.

Terugkijkend was het een geslaagd evenement. Ook de prijs was zeker goed voor het niveau van de sessies. Zelfs de volgepakte zaterdag had voor iedereen iets. Ik nodig ook iedereen uit om op de SQL Bits site  (http://www.sqlbits.com) te kijken naar de opgenomen sessies en het volgende keer in levende lijve mee te maken.

Nu kijken of er een locatie is voor een SQL Zaterdag aan zee….

One of the new features in Denali is Availability Groups. We can fail-over databases as a group and as an extra bonus we can have multiple mirrors. Back in November Andre Kamman (@andrekamman) returned from the PASS Summit and showed the new features to the Dutch audience. One of the remarks was the need for Windows Clustering. I was pretty sure at that moment it was used to control the fail-over of a group and connect to a virtual name. Later at the 24 Hours of PASS there was another Denali HADR demo where the configuration was explained in more detail. From that moment I was really sure why they we’re using Windows Clustering.

Now, new versions are great but at this moment a lot of people are still using SQL 2000. So, it could be some people cannot use these new features simple because they do not use the latest version of SQL Server. This could be budget, company regulations or any other reason.

Given the techniques used in Denali I was sure some of these could apply to SQL 2005 and higher. SQL 2000 would be out of the picture simply because it doesn’t support database mirroring. I wanted to accomplish the following:

• Fail-over databases as a group
• Allow connections to be made to a virtual name instead of Principal and Mirror in connection string

I did some scanning and playing with the Cluster Administration and found it was possible to add a Generic Service as a cluster resource. This led me to a solution where a service running inside a Windows cluster would control the group fail-overs. Also, the cluster would register a network name and IP-address that could be used to connect to the databases. And this turned out te be a great solution. It was some work to get things connected but with the use of an extra table in MSDB and watching the WMI Database Mirror State Change it turned out to be easy. So, what’s going on.

• I created a table in MSDB. This one table holds the configuration. It contains the names and service-names of the two SQL Servers involved (Principal and Mirror), the name of the group to distinguish the group and a delimited list of the databases in this group. This table needs to exist on both SQL Servers and contain the same information.
• A Windows Service is created and installed. By default it runs as LOCAL SYSTEM and is set to manual. A configfile is included with this service and contains the connectionstrings for both nodes and the Virtual Server name. Different services can be installed for each group. Pretty much like SQL Server instances.
  
• Next up is a Windows Cluster. If you have the basic cluster running create a new Application. This is al straightforward. Choose Generic Service as the application and you will be presented with a list of all installed services including the one we just installed. We are not using shared disks or shared registries so we can skip these options. Provide a network name and IP-address when asked.
  
• When that’s done our service lives as a cluster resource. If the service is started it checks if the databases for this group are on that machine, if so, nothing happens. If the databases are running on the other node, it will fail-over all databases to this node.
  
• If all databases are on the same node as the service is running it just sits and waits until something happens. If you fail-over one of the databases in the group, it will be detected by the service and the service will failover as well. When the service starts on the other node it will again check if all databases are on that node and will fail these over if needed.
  
• A client can connect on the virtual network name, simply because this resolves to the active node as well thanks to the Windows cluster.

The code I used is at the end of the post. To install the service you run: installutil.exe /servicename <Name of the service> <path to the executable>. To run multiple instances of the service you must create multiple directories, as with SQL. Change the config files according to your situation, they are pretty self explanatory.

You need to create a table in MSDB as well on both SQL Servers and insert the configuration. A .sql file is included in the solution. In this example I used the LOCAL SYSTEM account for the service. This required me to add the computer account (DOMAIN\HOSTNAME$) as a login to be able to fail over. For simplicity I did put this account in SYSADMIN but that’s not required. I know this is very bad, but allowed for testsystems, at least in my environment.

So it needs some polishing and more shining here and there but I am satisfied with the first result. I am able to mimic the behaviour of Denali Availability Groups. Let me know if this is something you would want to use. If people are interested I might polish it.

Download Visual Studio 2010 solution here.

22 Februari was het weer tijd voor een avondje PASS. Ditmaal te gast bij Schuberg Philis en ik mag wel zeggen dat het uitstekend verzorgd was. Onderwerp van de avond was het Entity Framework, WCF Data Services en LINQ. Menig DBA krijgt hoofdpijn als hij deze termen hoort omdat dit garant staat voor ‘geen performance’. En dat is jammer omdat het voor ontwikkerlaars perfecte tooling is om applicaties te bouwen.

Ik hoop dat ik deze avond duidelijk heb kunnen maken dat de schuld niet volledig bij EF4 en LINQ ligt maar ook dat door verkeerd (of onjuist) gebruik van het framework onbedoeld veel data opgehaald wordt. Daarnaast konden we ook zien dat stored procedures nog steeds een plaats kunnen krijgen in EF4. Er werd wel een heel goed punt aangehaald over het gebruik van parameters. We konden in de profiler zien dat er geen parameters gebruikt werden en de statements niet in een sp_executesql gewrapped werden. Iedere DBA weet dat dit killing is voor performance.

Maar ook hier geldt, het hoeft niet zo. Het was/is mijn onwetendheid/onbekendheid met het framework dat het zo ging. Je kan in de nabije toekomst een post verwachten waarin LINQ gebruikt wordt in combinatie met parameters maar waarin ik ook laat zien waarom dit nu zo belangrijk is.

Ik zet ook de volledige code bij dit bericht. Ik heb e.e.a. in commentaar verwerkt en ook een voorbeeld van paging toegevoegd. De PPT voegt niet zo heel veel toe behalve dan dat daar een lijst met sites in stond maar deze zet ik dan hier neer. Mocht je echt de PPT willen hebben laat het dan weten via twitter of email (voornaaam@achternaam.nl)

Dan hoop ik iedereen te zien op SQL Zaterdag.

WCF Data Services
ADO.NET Entity Framework
LINQ
WPF en Silverlight

En tot slot kun je hier de solutionfile downloaden. Je hebt wel VS2010 en SL4 nodig.

Update: seems the link to the script was broken. This is fixed now.

After my previous post I had a comment if a sample could be provided combining this script and the one on @andrekamman his blog. It didn’t take me long to write this but it took me a long time to put it on the internet. A couple of days ago I got the same question so I tested and tweaked the script where needed. And of course I will share it with all of you.

I am using two functions now. One to read all Windows computers from a OU in Active Directory. This will be used as input for another function that reads the instance information. At a local PASS gathering @andrekamman told he found a defect in the function. Apparently SQL 2000 RTM machines we’re not picked up by the function. I have build a domain with a SQL 2000 RTM default and named instance on it. It turned out the registry keys are different on the RTM than on the SP installation for SQL 2000. They warn you about it but you never believe they will actually do that. The output is written to a csv file. Now import these functions in your PowerShell environment and with these commands you can run a scan of you’re network for SQL Server installations. Replace the sample CONTOSO OU with your own.

Remove-Item sqllist.csv
foreach($s in Get-Computers “LDAP://CN=Computers,DC=CONTOSO,DC=COM“) { Get-SqlInstances $s}
Get-Content sqllist.csv

Which results in my case in:
IIS1,MSSQLSERVER,8.00.194,NA,,MSSQLSERVER,Running
IIS1TEST2,TEST2,8.00.194,NA,,MSSQL$TEST2,Stopped
SQL1,MSSQLSERVER,10.50.1600.1,Enterprise Edition,MSSQL10_50.MSSQLSERVER,MSSQLSERVER,Running

You can download the file from my SkyDrive: Misc.ps1

Take notice of these two points as well:

There is an issue when running a 32-bit PowerShell and connecting to 64-bit machines due to a different registry hive. There is a solution if you tell PowerShell to use .NET 4.0 but I have not found a solution with .NET 2.0

If the machine is not available you will be waiting for a timeout.

It’s been quiet on the Denali front since the announcement of CTP1 back in November. I believe Microsoft is taking a small rest on the bench before they unleash new features to the public. Next week Microsoft employees will get a look at what’s new during TechReady and two weeks later the MVP Summit will be held. In April SQL Cat members will be at the SQL BITS event in the UK. It should be no surprise to see a CTP in the near future.

For now we have to settle with the current CTP and the lacking of announced features in it. I wanted to highlight one of the features because they are a big improvement albeit you might never realize this, until it’s too late. Oh, before you go and try this one out, it’s not in CTP1.

So, why do you want to assign a schema to a group. The best way to show this is by running an example. Imagine a normal company, somewhat like Contoso. This company has DBA’s and people who manage applications, like SharePoint, BizTalk. The DBA’s are all in one domain group called CONTOSO_DBA and all application administrators are in a group CONTOSO_SharePoint or any other. Now the DBA group is added to the SYSADMIN server role on SQL Server and hereby all DBA’s can manage all SQL Servers. The application groups are added to SQL Server and placed in the DB_OWNER database role for the appropriate database(s).

That doesn’t seem like an odd scenario does it? Perhaps you wish your company would work this way. If that’s the case, good luck. I have worked in a company where we worked this way and we ran into a few problems. You can easily demonstrate this, even without a domain controller. I have a local Windows 2003 EE installation with SQL 2008 R2 EE on it. On this box I created an application group called SpecialAppUsers. In this group I placed two users, John Doe (jdoe) and Jane Doenot (jdoenot).

Now for the fun part. As the DBA I will create an empty database called MySpecialAppDB (or whatever). I will then place the group SpecialAppUsers in the DB_OWNER database role. Now I will start up a fresh SSMS instance as John Doe (no need to logoff, just use Run As). John is the assigned application engineer who will fill the database schema. So opens up SSMS and runs the database installation script. This script creates two stored procedures. If the script runs successfully John also has a test script that tests a correct installation of the schema. He runs this script and it executes successfully.

So,John is happy. His work is done and he goes home. When he is out of the door the phone rings,Jane picks it up and it’s the customer. The application is not working! How is this possible? John ran the test script without any errors. Jane decides to test it herself. She opens up SSMS and runs the test script.

So, how is this possible? There is a stored procedure missing. And the screenshot John send clearly showed it ran successful. Someone must have deleted it. Jane takes up the create script and runs it again. She ignores the error about the already existing objects. Next she runs the test script and this time it’s successful. She sends a mail to the customer and get’s herself a nice cup of coffee from the machine. At the coffee machine she meets up with a member of the DBA team. This is of no surprise to Jane as most DBA’s are located next to a coffee machine. She shares her remarkable adventure and how she managed to solve it. The DBA responds politely with a ‘nice’ and ‘cool’. But when he’s back at his seat he checks the database.

The minute he sits down Jane calls. The application is still not working. She is most certain it has to do with something the DBA changed during coffee because it worked before. So, the DBA takes a look at the database and runs the test script from Jane. The script will fail here as well. So what happened. Well, take a look at the created stored procedures. This will give a clear indication of the problem.

If you take a closer look at the script you will see that Proc1 was created without specifying a schema. This caused SQL to take the default schema. Since this wasn’t defined it defaults to the username. This seems some legacy from the past where we didn’t use schema’s but owners. When you take a look at the schemas you will see the schemas are created for you. Which is funny because creating a stored procedure with a schema name that doesn’t exist fails but creating a procedure without a default schema creates a schema.

The root cause for this problem lies in the ownership of the database. If you are the owner of the database you will always default to the dbo schema (unless you change this manually). But this doesn’t apply to the DB_OWNER role. So, why not set the default schema to dbo then? Well, you cannot do this for a group. You can for an individual user but you cannot assign a default schema to a group, simply because it’s greyed out. Ok, then we will set the group as database owner. No luck there as well. You cannot set a group as database owner.

Also be aware the user schema will win over the DBO schema. If Jane would recreate the stored procedure like this CREATE PROCEDURE dbo.Proc1 AS select 3; this will succeed. But when she runs EXEC Proc1 the result will be 1 because there is also a Proc1 with Jane’s schema.

The key takeaway here is to always specify the schema, even if it is DBO. There is even a small performance gain there and I believe some SQL CAT members have showed there can be a big performance impact if you do not specify the schema. But you do not always have control over this. Take the installation of SharePoint 2007 for example. This will show similar behavior if you pre create the databases and set a group in the DB_OWNER database role. The installation will fail.

Good news, this is changed in Denali, SQL 11, SQL vNext or however you want to name it. In the next version of SQL Server it is supposed to be possible to assign a default schema to a group. This would totally solve this problem. But, as I mentioned it is supposed to do this because it’s not in CTP1.

So let’s all be patient for the moment and let the fine people at Microsoft work at the next version of SQL Server.

The subject for this month’s T-SQL Tuesday is Automation in SQL Server. Before I go crazy on one of my favorite automation tools, PowerShell, I will take a  moment to explain why I think automation is so important.

First off, we are IT professionals. This means we automate things. From filling out our timesheets, expense forms to making coffee in the morning, afternoon, late afternoon, early evening, evening, late evening (we drink a lot of coffee). Automation is nothing new. It has been done for years in large factories. Big machines build cars, press books and cook muffins. Not only because we don’t like repetitive work but also to keep the quality at a constant level.

And that’s why automation is important. I believe automation is a key element in high availability. One of the causes for downtime is human error. This can be prevented and limited by automation. Automation keeps your processes of a constant quality and predictable output.

PowerShell is a great tool available on all the current Windows platforms and goes beyond SQL Server and T-SQL. Here lies the true power of PowerShell. If you had to send a tweet after a full backup was taken and copied to a remote site, restored and add logins; PowerShell can do the trick. PowerShell doesn’t rely on a running SQL Agent,PowerShell can work with different credentials,PowerShell leverages the potential of the .NET Framework allowing you to connect to web services, do file copies with WebDav, FTP, WinSCP or whatever you’re favorite filecopyflavour is.

An example of predictive output where PowerShell can help is demo setups. Have you ever demoed SharePoint? To demo SharePoint you will need:

• a domain controller
• a database server
• at least one SharePoint server

And if you wanted to do it properly these would al be separate machines (for delegate constrained testing, load balancing, database mirroring). You would even throw in a client machine. Here is a shortlist of the steps to follow in this case:

1. Setup 4 Windows machines with your favorite virtualization platform.
2. Install the domain controller role on one of them.
3. Fill the domain with demo users and groups.
4. Fill the domain with the needed service accounts and proper SPN’s.
5. Join all the machines to the domain.
6. Install SQL Server on one of the machines.
7. Add the SharePoint setup user to the appropriate roles.
8. Install Office SharePoint on one of the machines.
9. Add the demo users to SharePoint.
10. Add some demo content to SharePoint.

And this is just an outline. Each steps consists of several steps within. All these steps can be performed with one PowerShell script. And to make it reusable you would create several scripts. One for each outlined step and one overall script. So, when you need a clean demo machine, open up PowerShell and tell it to Create-KillerDemoMachine. Take a look at all the resources mentioned below. These are all scripts somewhere on the internet performing some of these tasks. Combined well and there’s your runbook automation.

• Building a domain controller with PowerShell.
• Active Directory and PowerShell.
• Install SQL Server from the command prompt (or PowerShell).
• Create databases with PowerShell.
• Install SharePoint using PowerShell.
• SharePoint 2010 PowerShell reference.

If you introduce PowerShell in your environment you can create a new way of performing backups. Not just with the regular T-SQL statements, but now with PowerShell. And since PowerShell can connect from anywhere to anyplace you can truly centralize your backup scenario. Or you could backup, ftp, and restore if needed. You can even let non dba’s or developers take backups if they needed by providing them a backup cmdlet which accepts the database name and a target server and restore a copy only backup on that server.

As I mentioned earlier, automation isn’t new. If I have to press the same button every day I might get bored, or run it without thinking. And engineers do not tend to read manuals. So you’re carefully planned How to recovery from complete failure manual is probably not read because the engineer has done it before, somewhere.

It’s a different way of thinking than T-SQL, but it’s more powerful. It can make your life easier, your database maintenance predictable and your time spending more efficient. This leaves you more time to study on your favorite subject, SQL Server. And if you really want to use T-SQL there’s always the Invoke-SqlCmd in the SQL PowerShell snap in.