I’m using LinqToSql as a quick way to access my database for some brownfield testing. I needed to execute a stored procedure that returned a result set, but after adding it to the LinqToSql design surface, I noticed that the return value was set to none, and I was unable to change it.

This stored procedure had two exec statements, and a Set Transaction Isolation level statement as well.  After commenting these out and re-adding the stored procedure to the design surface, I was still faced with a return value of none. I ended up deleting the stored procedure, rebuilding, and then re-adding. This finally resulted in a auto generated return value. I then uncommented the lines I had previously commented out.

While researching this, I found some other people with a similar problem, and rebuilding helped a few people, but not all, so your mileage may vary.

Earlier this week, I had a request to write a query, which required comparing one row to the row that followed. While I had something like this before using sub queries with MIN/MAX, I just didn’t have the time to hack thru it. Today, I got an e-mail from SQL Server Central (.com) from their mailing list, and one of the featured articles was “Linking to the previous row”. This sounded like what I needed for my query, so I decided to check it out.

The opening of the article describes my situation exactly, and by using the method described, I was able to write my query in a couple of minutes using CTE’s and the Rownumber function. I highly recommend the article.

Today, Jeff Atwood posted about a deadlock issue he was having. He had a couple of ideas as to how to solve his issue, including the nolock statement. What I found most interesting were the comments that followed. Here is a summary:

• SQL Server sucks
• NoLock is ok
• NoLock is a hack
• I can’t believe you don’t/didn’t know how to fix a dead lock

I can’t believe how critical people were of Jeff. I guess I was a little surprised that the author of the great Coding Horror didn’t know the answer to something, but that’s no reason to be critical of the guy.

A couple of the more decent links included:

• http://weblogs.sqlteam.com/mladenp/archive/2008/07/18/Immediate-deadlock-notifications-without-changing-existing-code.aspx
• http://developmenttips.blogspot.com/2008/08/cure-for-deadlocked-learning-to-use.html

We have a couple of deadlock issues in our current database that I’d like to sit down and fix, so these might come in handy.

Update: Someone who knows allot more about SQL posted a blog entry in response to Jeff’s original post. The author believes that using nolock is never a good idea and goes on to give some advice on how to start solving deadlock issues.

With the release of all Visual Studio 2008, TFS 2008, and SQL 2008, I set about upgrading I set about upgrading my main development laptop. Currently I have VS 2008, Team Explorer 2008 and SQL 2005 client tools installed. I opted to download the full installer for the SP1 updates as opposed to the bootstrapper options.

• .NET Framework 3.5 Service Pack 1 (Full Package)
• Visual Studio 2008 SP1 (Full Package ISO) - Change List 
• TFS 2008 SP1 (Full Installer)

I knew I needed to install VS 2008 SP1 prior to TFS 2008 SP1, but for some reason decided to go with the SQL upgrade first. As I mentioned before, I currently only have the client tools installed, as I like to keep my SQL servers on virtualized development servers.

The SQL 2008 installer starts off by checking for .Net 3.5 SP1, and if it does not exist, it will install it on your machine, along with a hotfix for windows installer. After this is complete, you are required to reboot your machine. I opted for the upgrade option, hoping that everything just works. I’m not sure if you can do a side by side install with SQL 2005 client tools and 2008.

After selecting my options, an upgrade check is preformed, and it failed because I have not installed VS 2008 SP1 yet. OK, I wanted to install that anyway, so I canceled out and launched the VS 2008 SP1 installer. As with most service packs for Visual Studio it takes awhile to apply, but my experience was more positive then the service packs for VS 2005. No reboot was required, so I moved onto the TFS 2008 SP1 install.

So I must have mis-understood or did not read something with the TFS 2008 SP1 installer. It looks like it’s for the server components only, and VS 2008 SP1 handles upgrades to Team Explorer as well. This blog post makes reference to this, and even says that if you are installing Team Explorer on your TFS server, to install VS 2008 SP1. Looking at Help/About Microsoft Visual Studio, I see the version number for VS 2008 is now 9.0.30729.1 SP, and looking at the details for Team Explorer shows the same version number.

Jumping back to the SQL 2008 installer, I proceeding with my update. However it wasn’t really an update as much as it was a side by side install. Remember, I was just doing the client tools, had I had the database components installed, I assume that those would have been upgraded. I have a couple of SQL 2005 instances I can try an upgrade on to see what happens. The install went very smoothly and there were no issues.

First impressions of the new SQL Management studio are extremely positive. I saw the intelli-sense demo at the launch event, but can’t really test it as I already have SQL Prompt installed. My job as a Pseudo-DBA just got easier.Here are some new features I found.

• When running queries, you can view the results in traditional grid view, but also as text (with options to choose your column delimiters), or export to a text (.rpt) file.
• The new activity monitor actually provides useful information (well alot more information then the old one ever did). It reminds me of the task manager in Windows Server 2008. The new activity monitor does not work with SQL 2000, but I will be eliminating all SQL 2000 boxes by the end of September. As 4 line graphs across the top you get % Processor time, Waiting Tasks, Database I/O and Batch Requests/Sec. On the bottom you get 4 collapsible lists: Processes, Resource Waits, Data File I/O and Recent Expensive Queries.
• The database publishing wizard from Visual Studio is now included. Actually there are a lot more options to control how your scripts are generated.
• There are some very nice default reports built in now as well. The reports were an add-on for SQL 2005, but they are now included (and you can of course write your own). Stuff like Disk Usage, Index Usage, etc. I hope I can find a way to schedule these to be emailed.
• New option to generate Create and Drop Script (along with the original separate options of generate create and generate drop). There is also an option to generate as SQL Agent job.

Finally I went ahead and upgraded our TFS server to SP1. First thing I did was install this month’s Windows Updates, as I was going to have to reboot anyway. Next up was Visual Studio 2008 SP1, as I have VS and Team Explorer installed. Unfortunately I was out of room on my system drive so I had to take the extra step of expanding my virtual hard disk first.

I rebooted the server, and then proceeded to install TFS 2008 SP1. It looked like everything was going good, but it failed at some point. Turns out the SQL server did not survive the reboot, VS 2008 SP1, or the system drive expansion. Somehow my mssqlsystemresource.ldf file got messed up at some point. The event log error stated “One or more files do not match the primary file of the database.”. I got better information out of the SQL error log which pointed me right to the System Resource ldf. I copied the ldf file from another SQL 2005 server and was back in business. The TFS update proceeded without incident after that. In case you are wondering, you can determine your TFS version by looking at the version of Microsoft.TeamFoundation.Server.dll in %Program Files%\Microsoft Visual Studio 2008 Team Foundation Server\Web Services\Services\bin. For SP1, the version number matches that which is displayed in Visual Studio 2008, or 9.0.30729.1.

I spent the last day and a half trying to figure out why a query using FOR XML AUTO was returning the wrong result set. Some background is in order. I’m migrating a database from SQL 2000 to SQL 2005, as well as running the same database created from scripts on SQL 2005 but in a different environment. Both databases contain the exact same SQL in the stored procedure that was the source of the problem, but they were returning different result sets. I even restored the database that worked to the server that had the non-working database and saw that the database created from scripts was still working as expected.

I figured there was a database level setting causing the issue, so I opened up the database properties for both databases and compared them side by side, except I missed the 3 drop down boxes at the top, specifically the compatibility mode. I finally decided to run the SQL 2005 Update Advisor, which comes on the install media, or available online as a download. After about 10 minutes of checking my database (it was across a remote connection), it found the exact problem I was having, and the fix was to get rid of my derived tables, or set the comparability mode to 90.

From the Upgrade Advisor help, here is an example of my original problem.

Consider the following table:

CREATE TABLE Test(id int);
INSERT INTO Test VALUES(1);
INSERT INTO Test VALUES(2);

Now run this query, which produces different results under different compatibility levels.

SELECT * FROM 
   (SELECT a.id AS a, b.id AS b 
    FROM Test a JOIN Test b ON a.id=b.id)
AS DerivedTest 
FOR XML AUTO;

Under Compatibility Level 80 you get:

<a a="1"><b b="1"/></a><a a="2"><b b="2"/></a>

Under Compatibility Level 90 you get:

<DerivedTest a="1" b="1"/><DerivedTest a="2" b="2"/>

The XML under Level 90 is what I was looking for. Remmeber, under SQL 2000, the same query worked fine, it was only under SQL 2005 with Level 80 that I started having problems.

Of course after switching to level 90 I found another bug, and wouldn’t you know it, the compatibility wizard told me about this one as well, I just felt like ignoring it at first. This time it was a problem with a table prefix in an order by clause. Funny thing is, the table prefix didn’t even exist in the stored procedure, but under SQL 2000 it worked fine. Turns out someone else had already fixed the issue in the SQL 2005 version.

While looking for the stored procedure in error (since at first I ignored the warning in the upgrade advisor which even told me what stored procedure), I found a quick way to search stored procedures and functions in SQL 2005.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%SearchString%' 

Lesson of the day, try using and paying attention to the tools that Microsoft gives you.

Late last year I posted briefly on the SQL Publishing Wizard. I haven’t had a need for this since I reformatted and installed Server 2008 64 bit as my main workstation at work, and wouldn’t you know it, it’s not there now.

As usual I headed off to Google and found some posts that pointed to this install path, C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\1.2. Seeing as I am running a 64 bit Windows OS, my path is actually C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing\1.2. Now you could just run SqlPubWiz.exe, or run the VSInt.reg file to add the context menu option in Visual Studio 2008.

Not so fast, the registry file doesn’t seem to work as is on a 74 bit OS due to the new subkey, Wow6432Node. I modified VSInet.reg to include the keys the Wow6432node, and in the end got my Publish to Provider context menu option back.

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VWDExpress\9.0\Menus]
"{40d75537-ce10-4311-a7b0-6b164d80405d}"=",1000,1"
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\9.0\Menus]
"{40d75537-ce10-4311-a7b0-6b164d80405d}"=",1000,1"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\VWDExpress\9.0\Menus]
"{40d75537-ce10-4311-a7b0-6b164d80405d}"=",1000,1"
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\VisualStudio\9.0\Menus]
"{40d75537-ce10-4311-a7b0-6b164d80405d}"=",1000,1"

Just found a blog post by the Microsoft SQL Server Development Customer Advisory Team  entitled How can SQL Server 2005 help me evaluate and manage indexes?. This post goes over 5 ways to analyze your index usage in SQL 2005. There looks to be some good technical information on this blog, worthy of any DBA in training.

I wanted to try out SQL 2008 for the upcoming Madison DotNet Code Fest. I had downloaded the February CTP last week, and started out with that. I actually got everything installed and went out to find the sample databases on CodePlex, and discovered that RC0 had been released this week. Oh well, just revert the virtual machine to it’s pre sql state and start over with RC0.

Pre-Installation

Pre Requisites

The following pre requisites were installed without issue as part of the setup process. All pre requisites were included with the ISO.

• .Net Framework 3.5
• .Net Framework 3.5 SP1 Beta 1
• Hot fix for Windows Server 2003 (KB942288-v2)
• Reboot

My base Windows 2003 VM has only an 8 GB system drive, so I install everything to a second virtual hard drive which usually has a drive letter of E, as it does in this case. So all of my install paths are to E:\, but the folder structure remains the same.

SQL Server Installation Center

The SQL server setup has been completely revamped from SQL 2005, and there are some noticeable additions from the February CTP as well (the most prominent is the addition of a GUI option to use an existing configuration file).

Planning: Requirements, documentation, system configuration check, upgrade advisor

Installation: New stand alone, new fail-over cluster, add node to cluster, upgrade from SQL 2000 or 2005, search for production updates

Maintenance: Edition Upgrade (like changing from developer to Enterprise), repair, or remove from cluster

Tools: System configuration checker, installed SQL server features discovery report, upgrade integration services packages

Resources: Documentation, community, CodePlex samples

Advanced: Install based on configuration file, Advanced cluster prep and completion

Options: Platform (x86, x64, ia64)

Installing

I went ahead with and chose the stand alone installation. Five items were checked before I was allowed to proceed: Minimum OS version, setup administrator, restart computer, WMI service, validation for SQL server registry keys. All items passed and I proceeded to the product key.

The installation media seems to have all of the files for evaluation, express and express with advanced services. I didn’t have a product key, so I just went with the evaluation version, which should give me 180 days to evaluate. After selecting the edition, I had to accept the EULA and then have some setup support files installed.

Another system check was run, and everything passed except I received a warning notification because I had enabled the windows firewall. A message indicated I should check out a page at Microsoft, but the page had no mention of firewall configuration. Clicking next, I was brought to the Feature Selection page, and the main portion of SQL 2008 setup.

Feature Selection

These are the options I chose, and is not an all inclusive list.

• Instance Features
  • Database Engine
  • Reporting Services (I’m interested to see how the non-IIS reporting services works)
• Shared Features
• Business Intelligence Development Studio (to create some sample reports)
• Client Tools Connectivity
• Integration Services (Was required in Sql 2005 for maintenance plans)
• Client Tools Backwards Compatibility
• SQL Server Books Online
• Management Tools – Basic (Management Studio, SQLCMD, and SQL Power Shell provider)
• Management Tools – Complete (Support for Reporting Services, Analysis Services, and Integration Services, SQL Profiler and DB Tuning Advisor)
• Microsoft Sync Framework (interested to see how this works with mobile devices)

Instance Configuration 

• Default instance
• Instance ID: Default of MSSQLSERVER, however you can change this, and it automatically updates the install paths
• Instance root directory: E:\Program Files\Microsoft SQL Server\
• Computed SQL Server Directory: E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER
• Computer Reporting Services Directory: E:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER

I do like how you can change the instance id for the default instance, as well as choosing a named instance (which you’ve always been able to do).

Disk Space Requirements

After selection your options and installation drives, you are presented with a screen that shows drive space requirements. Unfortunately, even with changing all install options to E, half of the required disk space is still targeting my C drive.

• C:\windows: 1188 MB Required
• Shared Features (E): 724 MB Required
• Instance Directory: 267 MB Required

Server Configuration

Here you configure the user accounts for each service. Sql Server Browser defaults to Local Service. Sine I am not running in a domain, nor do I need access to any domain resources, I created a local user account and used that for the Sql Server Agent, Database Engine, Reporting Services, and Integration Services service.

You can also change the collation, but I left it as the default: SQL_Latin1_General_CP1_CI_AS

Database Engine

Account Provisioning: Windows Authentication or Mixed mode. I went with integrated, and added two users to the server admin role.

Data Directories:  Here you can choose the root data directory, as well as where user data files, log files, and the system databases are installed to, wow, that is a nice change. I’m using the default of E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data, but in our production systems, this would defiantly change. Being able to specify this stuff at install time is awesome, and I can defiantly see the need for some install scripts.

Filestream: Here you can enable FILEStream for T-SQL access. I have no idea what this means, so I will leave it off for now. You have to specify a windows share name when you enable to, so I am curious to find out more. You can also allow remote clients to have access (via the share?).

Reporting Services

You have three options, Native mode with default configuration, SharePoint with default configuration, and install but do not configure. I’m going with the native with default configuration in the essence of time.

Installation Progress

Prior to starting the install, a final system check is performed, and all checks passed. Before clicking install, you are presented a summary of what will be installed, as well as given the path to the .ini file that contains the configuration. I have included my path below, but it will very from install to install as the path includes a date stamp: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20080618_142128\ConfigurationFile.ini

The installation process took longer then I remember to install the February CTP, but other then that, the install wen smoothly and no errors were encountered.

Post Install

I went into the SQL server Configuration manager, and under Sql Server network Configuration, I enabled TCP access so I could remotely connect to the SQL server. I also opened up port 1433 to my local subnet in the Windows Firewall.

First Thoughts

I tried connecting with SQL Management Studio 2005 to my new SQL 2008 instance, and it failed, so that is a little disappointing, but not unexpected. Same goes for Visual Studio 2008 pre-SP1 (since SP1 is still beta I have not installed it on my main machine), but SP1 beta 1 fixes this issue.

Management studio looks pretty much the same, just that there are new options in the navigation tree such as auditing and resource governor. I was able to connect to a SQL 2000 database without issue. I noticed a new publish to web service option when right clicking on a database, but I’m not sure if this is part of SQL or the Sync Framework.

The report manager site came up ok, but the reports site threw a permissions error indicating my service account did not have the proper permissions.

Continuing from my problems with installing reporting services, I now turn to configuring reporting services running on Server 2008. I have a total of four front end web servers, two for staging and two for production. Each pair points to the staging and production databases respectively. I was following this Microsoft document on how to install and configure for server 2008, but still ran into some errors. Both errors where displayed in IE when trying to browse to http://localhost/reports (the report manager site).

Error #1: rsReportServerNotActivated

This turned out to be an error with the encryption keys. I searched for documentation on setting up a web farm, and from what I found, there really was no mention on syncing the encryption keys, but ultimately that's what I ended up doing to get both front end web servers to show as initialized.

Starting with my staging servers, I looked in the ReportServer.Keys table, and saw that I had two entries, one for each web server. However, run of them was missing data in the SymmetricKey column. I exported the key using the Reporting Services Configuration tool from one server, and imported that key (again using the Configuration tool).  This fixed the error on my staging server, but led be to Error #2 (see below).

On my production servers, I had web100 showing as activated, but web101 showing as not activated (nor would it activate). There was a row in the keys table for web100 and web101, but I couldn't export the key from web100 like I could in staging. Attempting to export the key from web101 gave me an error saying it couldn't export the key, which made sense since it didn't have one. I tried using TSQL to copy the contents of the row for web100 in the Keys table to web101, but while it executed, it didn't fix the problem. I was a little frustrated at this point and just deleted all three rows from the keys table.

Wait a minute, where did the 3rd row come from? Back in the configuration tool, neither server was shown as activated, in fact, neither server was shown at all! Turns out that 3rd row has some dummy data in it that must be used somewhere. In case anyone did what I did, you can just add the row back in. All columns are null except InstallationID = 00000000-0000-0000-0000-000000000000, and Client = -1.

I went back to the configuration tool on web101 and under encryption keys, I hit delete. Looking back in the Keys table I saw I had a row for web101 again, and I was able to export this key. I promptly imported this key in on web100 and both servers activated.

After a quick reboot to verify that everything worked still, I was able to move onto the staging servers and Error #2. Yes, I did backup both encryption keys to a safe location for DR purposes.

Error #2: The report server is not responding. Verify that the report server is running and can be accessed from this computer.

I was getting Error #2 on web200 (figure out our naming convention yet?). This server was also showing an error in the event log: Report Manager cannot create the trace log %Program Files%\Microsoft SQL Server\MSSQL.1\Reporting Services\LogFiles\ReportServerWebApp__06_06_2008_14_40_25.log. This indicates a problem with permissions somewhere I'm guessing.

For all of my web servers, I've created a new SqlRsAppPool set to classic mode, which runs as a domain account. This domain account is specified in the configuration tool as the account to use when connecting to the ReportServer database as well. The web service setting in the configuration tool is configured to use the Local Service account. Had I not had 3 other servers running correctly, I would have tried setting the web service to the domain account as well.

Reporting Services uses to local groups named SQLServer2005ReportingServicesWebServiceUser$MachineName$Instance and SQLServer2005ReportServerUser$MachineName$Instance, and I compared those groups between my two staging servers, and the users were the same (WebServiceUser had my domain account and ReportServerUser had Local Service). I next comapred the permissions on the folders in %Program Files%\Microsoft SQL Server\MSSQL.1\Reporting Services between both machines, and they were the same.

I decided to reboot the problem server while I went and downloaded the latest version of Process Monitor, which happens to be at v1.33. I wish I could say I found some whacky permission that didn't get set by the configuration tool, but, rebooting the server seems to have fixed the problem. I know I restarted the Reporting Services window service, but I don't think I tried restarting IIS and the app pool. Oh well, I'm sure my download of Process Monitor will not be in vain.

Other Notes

Overall the how to install and configure for server 2008 guide was pretty good. The registry keys that say that you should have don't seem to be all that important, as I was missing some of them on my working servers from the get go. I did follow the steps to enable my account to login to the report manager without using the Run As Administrator command.

Even though I have no intention of directly exposing reporting services to the Internet, I still felt that it made the most sense to install the web front end components for SQL RS on our web servers located in our presentation DMZ. However, this proved to be somewhat of a challenge.

Apparently, there is a check in the setup file that limits your installation options on Server 2008 web edition to the client tools only. It took awhile of searching on Google, but I finally found my first lead, and am embarrassed because I didn't think of it myself. All of the various SQL components have their own MSI installers on the install media, located in either Tools or Servers. For reporting services, you need to run the SqlRun_RS.msi located in Servers\Setup.

I was still running into a problem however, soon after the MSI was launched I was getting a cryptic error message, followed by a second error message stating I didn't have access to a directory. Even though I had launched windows explorer as an administrator, I still couldn't get it to run. I decided to turn off UAC, reboot and try again. This time, the install was successful. I followed up the Sql RS install by installing SQL SP2, and then turned UAC back on.

Microsoft has a good document on how to configure SQL RS on Windows Server 2008, http://support.microsoft.com/kb/938245, which is what I used for the rest of the install. The document does state that under More Information that  you can install SQL RS "On any edition of Windows Server 2008".

Don't ask....

• Shutdown your SQL Server service, from the services MMC
• Stop all services and applications from connecting to your SQL server. If they are connecting via the network, you could use the windows firewall to restrict access. The reason for this, is once you are in single user mode, another application may connect and consume the one connection.
• Open a command prompt window and navigate to the location of SqlServr, which should be %Program Files%\Microsoft SQL Server\MSSQL.1\MSSQL\Binn.
• Type sqlservr -m to start SQL server in single user mode
• Open Sql Server Management Studio (SSMS) and connect to your SQL server
• Fix your security
• Go back to the command prompt window and press ctrl+c, and then Y to stop SQL server
• Restart SQL server, and any dependent services (like SQL agent) in the services MMC.

If you have UAC enabled (Vista or Win2k8) you have to run the command prompt, and SSMS as an administrator.

Question...does it really matter if you deny local computer and domain administrators access to the SQL server, if any local administrator can restart SQL server in single user mode? The reason I ask, is there are a lot of "Best Practices" that say you should deny access to normal computer administrators. I've even heard that in some industries and sectors such as legal, healthcare, etc, you have to restrict who has access to the data. Maybe it's just a you need to make a good effort.

So I have been continuing on my new assignment of setting up servers for our new infrastructure, and it's somewhat boring and repetitive after the first one. I was hoping to knock out the last 3 SQL servers today, but I couldn't get our schedule maintenance plans to run. I kept getting an error stating that "The owner (Domain\User) of job <Job Name> does not have server access." Well, I am the owner, and I have SysAdmin permissions, and I am the one who created the jobs, so what could it be?

Originally I had imported the maintenance plans from a file share that I had exported the maintenance plans from a non-member server, and then imported those maintenance plans into two other servers. I tried creating a new job on one of the servers, thinking that was something wrong with the import process, but nope, I get the same error on all three servers. Another member server, configured exactly same, just setup the day before, works fine, with my account as the job owner.

Off to Google I go. I found some references to the error, but allot of them were for a KB article relating to SQL 2000, and it had a service pack fix. I did find a command ( exec xp_logininfo 'username' ), which is supposed to help you verify if your account is setup correctly, and apparently mine is not. I get an empty result set on the 3 servers I am having problems with, and one row with the correct information on the working server.

I was actually happy to see the empty row set on the three non-working servers, as now I can search on exec xp_logininfo instead of the error I was getting with the job, to hopefully find a solution to my problem. Unfortunatly, I didn't get many hits that applied to my situation. Running the xp_loginfo on both the working and non-working servers yielded some interesting information. I decided to add a domain group that my account belonged to, to both a working and non-working server. Re-running the xp_logininfo showed 1 row on the non-working server, and 2 rows on the working server. The permission path column on the working server showed null for the first row (which was present before adding the group), and the 2nd row shows the group I just added (which is the same as the non-working server after adding the group).

Somehow, the one working server has some extra permission set somewhere. I think this may be because I might have manually added my domain account on the working server, but I let a MS tool that auto-runs after SQL SP2 add my account. Sure enough, adding my domain account manually fixed the original problem, and the results of the xp_logininfo now match between all servers.

I've been working on setting up servers the past week, and will be continuing for the rest of may. Included in that are BizTalk and SQL servers. Part of our setup procedure is to document everything so that we have consistency between servers. It's boring and tedious but necessary.

While looking up some info on DB Mail in SQL 2005, I came across a good post by Brent Ozar on some post install steps.  It covered a few things I hadn't thought about, but have since added to our build documentation.

Brent's post also had links to the SQL 2005 Dashboard Performance Reports. One of the links was to an excellent write up of the installation and use of the reports by Brad McGehee.

Today I downloaded and installed the Sql Server 2005 Express Advanced edition for a test project I was working on. I needed to enable remote connections, so I when to the surface area configuration wizard and enabled remote connections thru tcp, and I opened port 1433 on the computer's firewall. However, I could not connect.

Running a netstat -an showed nothing listening on port 1433. Doing some searching I found an outdated post on the sql express blog that helped me fix the problem.

1. Open Sql Server Configuration Manager
2. Expand Protocols for SQLEXpress under SqlServer 2005 Network Configuration and click on Tcp/Ip
3. On the IP Addresses tab, scroll to find the IpAny entry.
4. Clear out the field for dynamic ports and put in 1433 for the TCP Port
5. Restart SQL Server.

The reason you have to do this, is by default the SqlBrowser service (port 1434) is not running by default. When clients try to connect, they must know the port to connect to. The default sql port is 1433, so once we have that setup, even if the SqlBrowser service is not running, or is blocked by the firewall, we can still connect.

I attended the Hero's Happen Here launch event held in Madison, WI yesterday. The event was held at the Westside Marriott, and featured a morning and afternoon session, each with multiple tracks. Future planning note, the local McDonald's was not prepared for the rush between the two sessions ;)

I was registered for the IT Infrastructure track in the morning session (I think), but it didn't really matter. Once you got in, you could float between tracks, and I ended up standing on the back wall for a couple of the presentations so I could move more freely and answer phone calls (my on call week).

I'll start off with the free stuff I got.

• Microsoft branded lunch cooler - These were given away for the morning session and actually had food in them! They are really nice lunch coolers, with a main compartment with a separated top and bottom (bottom is for your ice pack and really cold stuff).
• Visual Studio 2008 T-Shirt - I got this from AngelaB because I made a comment while visiting the booth she was at. I said something to the effect that I was looking to learn how to leverage the power of Team Foundation Server to manage the complete application development life cycle.
• Training DVD's - Also from the VSTS booth, I picked up two training dvd's. One on web deign and development (SilverLight, windows live, etc) and the other one was on WPF. 
• Software Bundle which included VS 2008 standard edition, Windows Server 2008 enterprise 1 year eval, and SQL Server November CTP. Also included was trial editions of Microsoft Forefront, Microsoft System Center and a Windows Mobile 6 developers resource kit. Finally I picked up my 4th copy of Vista, this being a 32 bit Ultimate version with SP1.

Now onto the actual content. As I mentioned above, I kind of floated between tracks, so my notes jump around a bit.

SQL 2008

• New built in auditing mechanism
• No more need to write triggers and manage audit tables
• Granular auditing
• Write events to various sources including the event log. The event log source was highlighted because Server 2008 has a new feature where you can gather all event log data from all of your servers and view
• Peer to Peer replication
• New visual representation
• No longer need to start/stop databases (or servers? I missed the last part of this comment).
• Compression
• Two types of compression, backup and data.
• Backup compression enables faster backup and restore times and smaller backup files. A 2x to 7x reduction in backup size was reported, but it varies on your actual data.
• Data compression allows you to compress the data within your databases (including OLAP) taking up less space on disk, and in memory (allowing you to fit more pages into RAM), at a slight increase in CPU utilization (presenter said 2-3%). While I will need to evaluate this in various environments, it seems like a good tradeoff, as disk IO is usually the biggest bottle neck.
• SQL Server is still in CTP, and the Presenter was unsure which compression options would be available in which editions of SQL 2008.
• Resource pools and the Resource Governor
• Assign users (and it sounds like you can assign or configure this at a stored procedure level as well) to roles and then assign the roles to resource pools. The resource governor will limit, or assign a certain amount of CPU cycles to each pool.
• A typical usage of this is making sure your production applications get the CPU they need, even when someone is running a resource intensive reporting operation.
• New OLAP query optimizer
• Define policies (presenter compared this to GPO, or rather that's where the idea came from) which manage your SQL server, and then apply those policies to all SQL servers in your organization
• Performance improvements in database mirroring, along with automatic page repair.
• SQL Management studio now adds intellisense, but it's only for select statements. Several people complained upon hearing the limitation on select statements, but something is better then nothing, and you can always go by Sql Prompt from RedGate.

Follow-up questions

• What is the differences between clustering, mirroring, log shipping and peer to peer replication?
• With all this talk about server vitalization, what are best practices for maintaining high performance in a virtualized environment.

Virtualization

• Next version of Virtual Machine manager for System Center will be able to control virtual machines from MS Virtual Server 2005, Hyper-V and VMWare
• Hyper-V actually virtualizes the host OS. This is what is meant by the whole Parent partition terminology I guess. The presenter made it a point to show how after he enabled Hyper-V, he no longer had power management options on his laptop. So what does a virtualized host really mean? I'm still able to play games (World of Warcraft, Call of Duty 4) on my Hyper-V enabled Windows Server 2008 workstation, so it's not the same thing as a virtual machine that's for sure.
• The import/export feature in the Hyper-V manager is for moving VM's between hosts, not for importing VMware from Virtual Server 2005. To do an import from Virtual Server, you just create a new VM in Hyper-V, add the disk file from Virtual Server, and install new drivers.
• There is a differencing disk option I overlooked on my install, which allows you to setup a master disk, allowing you to save on space.
• You can setup virtual com ports to talk to the host computer, or remote computers via named pipes. I just setup both com ports on one VM to use the same named pipe, and was able to send data back and forth using two hyper terminal windows. That will come in handy for some testing I have to do. Note, this option is not unique to Hyper-V, but I thought it was worth pointing out.
• Backups of running VM's are supported (Live Backups)

Two things I wanted to touch on quick concerning Hyper-V, specifically live backups, and mounting VHD's in the host (a feature that has been posted on more then one blog).

To backup a running VM, it takes a little more then just an XCopy. Most people have stated that you have to backup the entire volume in order to backup a running VM properly. I found this post that provides an example of using a script to select the Hyper-V VSS writer and hopefully not have to back up the entire volume, but I haven't tried this yet. Since none of my VM's are 24/7 critical, it might be easier to just shut them down once a week and use the export function.

On the topic of VHD mounting, I found another post which provides a script to add context menu support for mounting VHD's on the host machine. The VHD must be not be in use, otherwise you will get an error.

Server 2008 Security

• Server and Domain isolation using network access protection
• Create zones, such as unsecured (customer laptop plugged into your corporate lan), secured (most company desktops and servers), and sensitive (source code servers, production databases), and policies on which computers can access each zone.
• DHCP and Certificate based, with DHCP easier to setup, but not as secure as Certificate based.
• You can define policies which classify what zone a computer belongs in. For example, your policy can check for AV, anti-spyware, etc.
• Rights Management Service, allows you to attach authorization to a document and limit what you can do with it, such as email it to an external email address or use the print screen feature. Of course you could still take a picture of your monitor with an actual camera (as pointed out by the presenter).
• Granular Active Directory Auditing will allow you to edit very specific details such as when a computer is moved from one OU to another, or a specific field in a users AD record is changed

VS 2008

The CSS support shown in the demo was very nice. The demo had a plain old web page with some basic formatting, to which the presenter did a drag and drop operation of some CSS styles he had download from CSS Zen Garden. The page in the designer was updated automatically and completely transformed the look and feel of the page.  There is a view which shows what styles are applied to what page elements, and how styles are inherited. It really seems like VS 2008 makes CSS a whole lot easier to work with.

There are obviously allot of new features in VS 2008, but I've been using it since beta 2, so the rest of the stuff presented (mostly linq) was review to me and I didn't write it down. The presenter's blog is up at BenkoTips.com and is supposed to have more videos and demos on VS 2008.

Office Development

• Outlook web forms allow for you to create an application easily within the confines of outlook.
• Sharepoint workflows are just WF workflows. New features in VS 2008 allow you to deploy directly from VS 2008 making this once painful operation much easier. I am not sure if custom work flows are available in WSS, or just MOSS
• MOSS has a business data catalog feature which allows you to pull together data from various sources, like ERP, CRM, etc.

So if you have been following along, I am currently at the BizTalk RFID Solution Days Conference and Training. Yesturday I noticed that some of my databases on my training VM were marked as suspect and the BizTalk services such as SSOP and the actual BizTalk service would not start. Upon further investigation in the event log, I was getting alot of errors indicating a corrupt msdb database.

Now the easy way would have been to ask for a new VM hard drive image, but this was too good of a learning opportunity to pass up. Better to figure out how to fix this in a low stress environment then on my production environment at 3am.

My first step was to fix the msdb database. I found this article on how to rebuild your msdb database from a script. The script is called instmsdb.sql, and you can run it if you have or do not have a msdb database defined. In my case my database was defined so it just rebuilt it.

That fixed all of the corrupt index errors for msdb in the event log and was left with the SSO service failing to start. Here is another case of where you need to read more then the first event log message. I thought the issue was with the SSO DB, but in fact, it was with the BizTalkDb database (being suspect). Once I isolated the actual problem, I could move on to repairing the database.

I found several posts on different work around's for fixing a suspect database, but this one, was the one that finally worked. In essence, you run the following sql script:

   1:  EXEC sp_resetstatus 'yourDBname';

   2:  ALTER DATABASE yourDBname SET EMERGENCY

   3:  DBCC checkdb('yourDBname')

   4:  ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

   5:  DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)

   6:  ALTER DATABASE yourDBname SET MULTI_USER

There were some errors displayed when fixing both the BizTalk and BamAlerts database, but in the end, everything is working now.

Jeff Atwood wrote about databases under version control in a recent blog posting. In it he advocates that you should put the goal of getting your database into source control above things such as what tools and processes to us as per the Agile Manifesto.

We have met this goal, and our database is under version control, however, I feel it has become a cumbersome and time consuming process (although my I have a lot shorter time working with the process then the other developers so maybe I'm missing some tricks). I have been thinking of ways to improve upon the process.

Also in Jeff's post, are links to a series of 5 posts by K. Scott Allen in which he explains how he does database version control. Some of his points match up with what I have been thinking, some don't. That, in and of itself further proves a my idea that no process is perfect, and what works for someone may not work exactly for you.

K Scott Allen's Posts:

1. Three rules for database work
2. The Baseline
3. Change Scripts
4. Views, Stored Procedures and the Like
5. Branching and Merging

I recommend that interested parties take the time to read the posts, as what follows is a brief overview.

• 3 rules for databases: Never use a shared database for development work, Always have a single schema source (source control), Always version your database.
• Create a baseline script. This is a single script for your tables. Your derived objects (stored procedures, views, etc).
• Change scripts are used to update the baseline, and should include an insert statement into your custom version table.
• Derived objects (stored procedures, views), are updated, by making changes to the individual file corresponding to the objects. These changes are checked into source control. All derived objects are dropped and recreated from the script files. If you add a new object, create a new file, if you need to delete an object, delete the file. Once thing I don't understand, is how are these "versions" related to the schema version? One of the points made is that you should be able to create a database from any point in time. This process covers the schema well, but you would have to hunt thru source control to find the collection of files that relate to that version. Perhaps this is a necessary trade off.
• Branching and merging depend on how you branch your application. The example given, is that when it gets close to a release, functionality is frozen and a branch is made for the release. Only bug fixes go into the branch and new features for the next version go into the main trunk. For the database, this means a new baseline script is created for the new version, which is a point in time snapshot of the database at the time of the branch. If there are changes in the branch, those changes are duplicated as a change script in the main trunk. You just have to be careful that the main trunk script is written in a way that it checks to see if the branch script has been run. (See the actual post for a better explanation).

Finally, I somehow got to this post by Phil Haack, in which he describes how using the information_schema views (see his post, and Books Online for a listing of what you can get from the views) will help future proof your scripts, because as Phil points out, Microsoft makes no guarantees on the schemas of the system tables.

As I posted before, I'm now heading up our embedded device project, so I won't be working on our uber back-end database as much. While we have a need for a database in the embedded project, I'm strongly considering using DB for Objects (db4o). While there is a bit of a learning curve, it looks pretty easy to use.

My plan is to use db4o to start out and then re-evaluate our database needs at the end of the project, hopefully saving the time it takes to manage a data access layer, scripts, etc. This would also make a cost benefit analysis of db4o easier, as I can simply estimate the time it would take to implement a traditional RDBMS and compare that to the cost of db4o.

So I was updating another computer from Visual Studio 2008 Beta 2 to RTM over Christmas and noticed something during the install process. You know, the install process, where they show pictures of people I never saw in my computer science classes, interviewed, or seen at any of development/industry events. Got to love marketing.

Back to what I saw though, the Sql Publishing Wizard. A quick Google search showed me this was previously an add in which has made it's way into Visual Studio 2008 as a default component. You can download and read about it on CodePlex. From the home page on the CodePlex, the Sql Publishing wizard supports two modes of operation:

1. It generates a single SQL script file which can be used to recreate a database when the only connectivity to a server is through a web-based control panel with a script execution window.
2. It connects to a web service provided by your hoster and directly creates objects on a specified hosted database

I tested out the single SQL script mode on our new database, which is quite complex and has challenged other scripting tools. I am extremely happy to report, that the single generate script reproduced our database perfectly. What's even better, is that it is an open source project, so you can make some changes, maybe add some schema object filters, or separate the objects into separate files.

I thought I was missing something in SQL Management Studio, as I couldn't believe the functionality to export or script database diagrams wasn't included. I went off to Google to search for something, either how to script it myself, or some missing function.

First I came across this post, but wasn't to inclined to copy/paste the script and clean it up. I later came to find out it was for SQL 2000, and would not work in 2005 (good, saved some time there). I continued my search and found an article on Code Project that provided SQL scripts to do exactly what I needed. The scripts are actually 2005 updates of the 2000 scripts I found on my first search.

Even if my Google search fails, I need to remember to hit up Code Project, it's a wealth of information.

I must make a confession, I am not a DBA. I think I know quite a bit about SQL, but when it comes down to it, there is just too much to know without specializing in SQL. I will probably sound like a SQL rookie with this post, but that's the price to pay for getting better, acknowledging weaknesses, and working to improve on them.

A query in one of our batch processes was timing out, and it was up to me to fix it. You pretty much have two options, the easy way, just increase the timeout, or the harder, tune the query. I personally feel the second option is more rewarding, but ended up spending more time on it then I expected, and exposed myself as the DBA imposter that I am.

I have tried to reproduce the general form of the query. It basically consists of several derived tables joined together and returned as an XML document.

SELECT table1.column1, table1.column2, table2.column1, table2.column2,
       table3.column1, table3.column2, table4.column1, table4.column2
FROM (SELECT sub1.column1 as column1, sub2.column2 as column2
      FROM sub1 INNER JOIN sub2 ON sub1.id = sub2.id
      WHERE Clause) table1
FROM (SELECT sub1.column1 as column1, sub2.column2 as column2
      FROM sub1 INNER JOIN sub2 ON sub1.id = sub2.id
      WHERE Clause) table2
FROM (SELECT sub1.column1 as column1, sub2.column2 as column2
      FROM sub1 INNER JOIN sub2 ON sub1.id = sub2.id
      Where Clause) table3
FROM (SELECT sub1.column1 as column1, sub2.column2 as column2
      FROM sub1 INNER JOIN sub2 ON sub1.id = sub2.id
      WHERE Clause) table4
ORDER BY table3.column1, table3.column2
FOR XML AUTO, ELEMENTS

I ran the query thru the DB Engine tuning advisor, and it came back with no improvements, as there were existing indexes which it probably thought was adequate. I also looked at the estimated execution plan, and noticed 2 sorts, each with a 25% weight. According to the comments in the stored procedure, one of the last things to be added was the ORDER BY table3.column1, table3.column2, so I commented that out, and the query ran in 1 second (original execution time was ~50 seconds). The first thing I thought was a missing index, or an index that needed to be rebuilt. There was an index already on table3.column1, so I tried adding column2 and that didn't help at all.

Let me take a step back and offer an opinion on how to classify the level of knowledge a person has with SQL.

• Level 0: Just starting out. Doesn't understand even the basics of optimization and performance.
• Level 1: Some experience in writing good queries. Understands concepts such as normalization, not doing much with indexes at this point.
• Level 2: Starting to place an emphasis on writing good queries, and creating good schemas. Starting to create indexes
• Level 3: Can start to spot performance issues in other peoples queries. Knows how to use the DB engine tuning tool and estimated execution plan
• Level 4: Adds indexes, hints, etc without needed the DB engine tuning tool for most queries
• Level 5+: I haven't gotten there yet, but it probably involves, among other things, intelligently using the skills from level 0-4, as just adding indexes isn't always the solution, and can cause issues themselves.

I was basically stuck at level 3 witch my problem, and needed to move to level 4. I would suspect that allot of people who list SQL experience are at the same level. The SQL engine does a pretty good job of optimizing queries, and throwing something into a GUI tool isn't that hard to do once you know about it. For smaller projects, the database isn't going to be too complex, and on really big, enterprise level projects, you'll probably have a dedicated DBA.

I knew of Index hints, it just wasn't clicking at this point that, that is exactly what I needed to do. I turned to everyone's favorite helper, Google and started searching for ways to increase performance on sorts. The first hits were the obvious things, add an index, but then I started to see glimpses of things like, index hints. I redid my search for index hints, and started to get more specific hits, and realized this was what I needed, and it finally clicked with other things I have read in the past. When you never really have to do this level of DBA, you don't think of everything you should, you get rusty compared to say, someone who has just read a book on SQL.

Armed with what "tool" I needed to do, I set out to add an WITH INDEX (ix_Table1_Column1_Column2) statement to my query, but even this was a humbling experience. I just couldn't figure out where to stick it, I kept getting syntax errors. I finally looked up the syntax for a select statement in books online, and very carefully examined the syntax and compared it with my query. I finally realized that the statement has to go between the "FROM sub1" and the "INNER JOIN" statement in the table3 sub query. After adding my index hint, the query completed in ~2 seconds, or a 48 second improvement.

This was a learning experience on several levels for me. First, I fixed my query, and realized that when the DB tuning tool fails to provide an answer, you got to switch to "manual" mode. I was lucky enough to have know to try removing the ORDER BY statement, and with out the comment in the stored procedure, I'm not sure if I would have been able to figure out exactly where to look. I'm sure the information provided in the estimated execution plan, would have pointed a more experience DBA in the right direction.

I recently came across an article on UTC to store date/time values. A decision made on a current project I am working on was to use UTC for client applications. However, the dates stored in SQL currently not in UTC.

I guess I've just gotten used to the GetDate() function in SQL.  A more appropriate function for our application would be the GetUtcDate(), which will return the current date/time in UTC format.

From the article:

The primary advantage of storing date/time values in UTC is that it makes the data transportable. To see what I mean, imagine that following scenario: you have an eCommerce website that is being hosted in a web server located in the Pacific time zone (UTC -8) and this application stores the date and time orders were placed in server time. Say a user, Bob, makes an order on August 1, 2007 at 9:00 AM UTC -8. After many months of phenomenal growth, you decide to switch to a larger web hosting company, one on the east coast where the time zone is UTC -5. Since the date/time is stored in server time, Bob's previous order still shows that it was made on August 1 2007 at 9:00 AM. But since we are now in UTC -5, it is as if Bob's order was made three hours earlier than it really was (since when it was 9:00 AM on August 1, 2007 in the west coast it was really 12:00 noon on the east coast).

 The author then goes into explain how you can fix the above issue by doing an update on the data to correct the time, which he refers to as "Ick", and I will have to agree with that. You don't want to, nor should you have to ever update a time stamp type property.

The next example uses various time zones to illustrate the problem of converting from one time zone to another (not to mention DST and SDT). Since we plan on eventually offering localized versions of our Web Application, displaying all times in CST/CDT is not idea. Not to mention the change from daylight savings time to stand time makes the way we display data currently, very confusing. Things seem out of order at best.

 Well, I'm off to update my use of GetDate to GetUTCDate. Be sure to read the full article.

Note: I found this article originally from ScottGu's RSS feed. However, the actual blog doesn't have the UTC article listed under Asp.Net, or listed at all.

Today I came across 2 articles relating to SQL server. The first, talks about determining events that take place during a time slice. I can see possible uses for this in scheduling, and definite use in reporting.

The second article, discusses some new features and functionality in SQL 2008. Below are some quick notes that were important to me.

• Transparent DB encryption - I'm more in interested in transparent table encryption, including indexes (in case the index contains sensitive data). Further research is needed. I know SQL 2005 has some encryption functionality, so I'm interested in what has exactly changed.
• Built in support for auditing data. Configured thru T-SQL.
• Data Compression.
• MS says slight processor performance hit on compression, but made up due to less I/O.
• It will be an interesting exercise to see how this will work on VMWare.
• Resource Governor
• Prevent users or groups from consuming high levels of resources.
• Since everyone is going to be coming in as Network Service (a rather large assumption), be interesting to see if we can still take advantage of this
• HotPlug CPU
• Don't know if we can take advantage of this. Probably only if we were using blades.
• Performance Data
• Sql Dashboard to display
• Suggestions for improving performance
• Current and historical data
• Installation sounds allot easier
• Configuration data separate from engine, so you should be able to setup once, deploy multiple servers and then configure.
• Should help with cloning.

So I got around to adding Database rollback functionality to my MSTest unit tests by using an article I read in MSDN magazine back in 04, before switching to MBUnit at my last job.

Everything was working great on my local computer (Vista even), and then I checked in my tests, and the failed, horribly on the build server. Fist issue was MSDTC network access was not enable, so I fixed that, but Then the build wouldn't even complete.

I dropped down to a console window and ran the tests manually using the MSTest command line tool. When running all of the tests, it hung after about test #9 of 17. When running the tests individually I was getting MSDTC errors. After spending too long just randomly trying DTC settings, I remembered the trusty MSDTC testing tool I had stashed away for problems like this. Got that copied over to the build server and.....a generic error message that had me searching on Google to no avail.

Around this time, I noticed that the event view was conveniently located in the same MMC windows as Component Services, and had meant to check it for the past 30 minutes, I finally opened it up and BAM, there it was, a DTC error, but would it give me a clue as to what the problem was? YES! Not only a clue, but a god dam solution to boot (a welcome change).

The problem? My build server was from the same clone as my Dev Server that had the SQL DB on, and since I did not use sysprep, but SysInternals NewSid app, the DTC was not setup correctly (they both had the same ID).

The solution? Run msdtc -uninstall and then msdtc -install from the command line to reinstall msdtc. I had to run msdtc -install twice and click around in the Component Services windows a couple of times before i could get DTC configured for using a local coordinator, but in the end, it was running.

Back to dtctester, which passed, back to my command line unit tests, which passed, back to the build server, which passed.

Now because I like the extra challenge, I thought I'd enable windows firewall on the build server as well. Whooops, broke it again, but I knew I had gotten DTC to work before so it was just a matter of comparing settings.

It's pretty easy to get DTC to work with windows firewall, just enable an exception in the firewall for c:\windows\system32\msdtc.exe, and don't forget to restart MSDTC service for changes to really take affect.

Bam, everything works, and now, I can go home.