DevelopMENTAL Madness

Tuesday, July 25, 2006

Database Load Balancing Service for SQL Server

I have been working on a "load balancing" application for OLAP applications and one or two of my previous posts touch on some things I've learned in the process. But now that I've got a working beta version I've decided to write my first "formal" article based on what I've learned. I've posted the article on my favorite developer community site, codeproject.com. Here's the link:

http://www.codeproject.com/useritems/dbloadbalancerservice.asp



The article is written based on using an ASP.Net application as the client for the load balancer, but in reality the client could be windows based as well.

Labels: ,

Saturday, July 22, 2006

System.Diagnostics.PerformanceCounter Privileges Required to Monitor Remote Machines

I've been pretty busy and haven't had time to post in a while, but this week I ran into something I thought was worthy of posting. I'm working on writing a "poor man's" load balancer for our databases. I won't go into the background this time as I will likely post details on the load balancer once I've completed it. But when it came time to work on gathering data using the System.Diagnostics.PerformanceCounter I ran into the expected permissions issue. Most examples involved using ASP.Net to display performance data, and all the examples I was running into recommended using either a privileged account, or impersonating the authenticated user. The former was often discouraged and the latter was the recommended means. The application could use windows authentication to secure the page and then only privileged users could get access to sensitive processes.


Well, my load balancer is a windows service so I didn't have the choice of impersonating the user's credentials, plus I don't like the idea of using privileged accounts anywhere in the application. After digging I found the following post:



http://www.testingreflections.com/node/view/1032


which pointed me to the following MS article:


http://support.microsoft.com/default.aspx?scid=kb;en-us;Q300702


Based on the MS article here's how I implemented 'least privilege':


Create new domain group - I called it 'Performance Counter Monitors' - add it to the local group 'Performance Monitor Users'. The local group has already been granted some of the necessary privileges. Then grant read permissions to the local group on:


HKLM\System\CurrentControlSet\Services\ControlSecurePipeServers\WinReg

%SYSTEMROOT%\System32\Perfh.dat

%SYSTEMROOT%\System32\Perfc.dat


According to the article, if you need access to performance counter data for specific services you'll need to grant additional access.


If you're going to be displaying data using PerformanceCounter in your application I would of course recommend securing the page and only granting access to privileged users, but there's no need to impersonate when your doesn't need the additional permissions. But if your application needs access to the PerformanceCounter data on a wide scale use the above method to enforce 'least privilege'.


For those who are using a large web farm I'm sure there's a way to script the above changes, but since I've only got 6 machines I need to monitor at the moment I won't be taking the time to figure it out anytime soon.

Labels:

Wednesday, July 05, 2006

Adventures in Mirroring

SQL Server 2005 has introduced an exciting new feature - data mirroring. And the timing couldn't be better. This summer as we upgrade our infrastructure we're working to increase our uptime and start doing some things right. Since we don't have a DBA, I get the responsibility of improving the database availability and performance. Well mirroring is just what the doctor ordered. But as usual (is it just me?) I've hit a snag trying to get it set up and running, and got an error that either no one else is getting or no one else has posted. So as usual I am posting the specifics of my problem, and the solution in the hope of saving someone from the same problems I had.


As a note for anyone who is searching for specific instructions on setting up mirroring; this post touches mostly on a specific series of problems. However, I do touch lightly on the steps required to configure your servers. With that in mind, read on.


Because I work from home and there weren't enough development machines at the office I could work on remotely I used Virtual Sever to setup a domain on my home network to duplicate the configuration we would be using in production. I created 2 virtual servers - one was configured as a domain controller on a new domain with SQL 2005 installed, the second had only SQL Server 2005. My plan was for the latter to be the principal, the domain controller to be the mirror and my development machine to be the witness. (I understand that putting SQL server on the domain controller is a bad idea, but I've only got so much ram to be used for each virtual machine and they've all got to be running concurrently). I created a domain account for each SQL Server service and each instance was running under the same account as the other instances. For example, MSSQLSERVER was running under the same account on both new machines.


Then I made a backup of the database and restored it to the mirror with NORECOVERY. As a note, I got the following error later on, but as most are likely to get this error at this stage in their configuration I figured I'd go ahead and mention it. When I issued the SET PARTNER command from the principal server I got the following error from SSMS:


The remote copy of database "Advancement" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.


I found on a post (sorry, I forgot to save the URL) that this is a confirmed bug and while it's annoying the fix is simple. Just follow the directions in the error message. This is one of those times that you don't need to go searching for the solution, the error message is actually helpful. So just issue a BACKUP LOG at the principal followed by a RESTORE LOG....NORECOVERY at the mirror and run the SET PARTNER command again at the principal and you'll be fine.


However, my first time through I got hit with a different problem. When I ran SET PARTNER on the mirror, everything was dandy, but when I connected to the principal and ran the same command I got the following:


SQL Server Management Studio:

The ALTER DATABASE command could not be sent to the remote server instance 'TCP://HOSTNAME.FULLYQUALIFIEDDOMAIN:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.


Event Log (principal):

Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://HOSTNAME.FULLYQUALIFIEDDOMAIN:5022'


I forgot to mention that I used unattended setup on both these machines, so their SQL Server configurations were identical.


After hours of frustration I decided to install an additional instance of SQL server and run mirroring on the same server. This worked, so I was satisfied that there was a problem with the server I had planned on using as my mirror. So I attempted to configure it as the witness. This time when I ran the SET WITNESS command on the principal I got the same error from SSMS but a different, but similar message in the error log:


Event Log (principal):

Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP:HOSTNAME.FULLYQUALIFIEDDOMAIN:5022'.


According to most of the posts I found trying to investigate the problem this is a permissions error, but as I mentioned both servers were running under the same domain account. Nevertheless, I gave the domain account sysadmin priviledges on both servers and this did not correct the issue. I was able to telnet to the port 5022 (TELNET HOSTNAME 5022). I ran "netstat -a" on the target server and verified that the server was listening on port 5022. I checked the event log and verified that SQL Server was listening on port 5022. I downloaded TDIMon from sysinternals and tried to see if there were any TCP errors and nothing stood out to me. (Let me note here that I am not a networking professional and so when I say I didn't see any errors I mean that TDIMon indicated "SUCCESS" in the results column).


After a day of fighting this I couldn't find any other suggestions anywhere and I was about to post a question to a SQL Server newsgroup. But on a final hunch it occured to me that there was one thing configured differently on the target server (I'm ignoring the obvious that it was configured as a domain controller). I had setup each machine on my network with a primary and secondary DNS. The primary was the domain controller and the secondary was my router. However I had not done this on the domain controller. I had configured it with only a primary DNS, pointing to the router. I had assumed (naively) that it knew about itself. So I when in and configured the network adapter with itself as the secondary DNS. Then I went back to SSMS and ran the SET WITNESS command on the principal. Success!!!


Because of the network configuration with SQL Server installed on a domain controller this is not likely to be an issue for most. However, I think I have established that, unlike most have stated on other posts, these errors can actually indicate a network error. And in the event that the mirror or the witness cannot see the principal you are likely to see one of these errors. So make sure and verify your network layer in addition to ruling out a permissions error.


Good luck!

Labels: ,