Blog: SQL Server

I needed to move a database from a SQL 2005 server to SQL 2008 server.  I had no problems backing up and restoring the database to the other server, but the database had a local user account created for services on the application server with which to authenticate.  I did not know the user’s password to manually recreate the user on the new database server.  [more]

What I found from this helpful article http://www.techrepublic.com/blog/howdoi/how-do-i-transfer-logins-from-one-sql-server-2005-instance-to-another/140 is a script that you can run to get all of the user accounts displayed in a nice SQL syntax that you can copy and paste into the new database server.

After you recreate the user account, the next step is to resynchronize logins to fix permission settings.

Even if you do not have the password for local SQL user, you can recreate the user on another server using this method.

SQL Server Access from remote client machines:  If you have a named instance of SQL Server, then there are specific things you need to do to allow clients to access the SQL database remotely. The default instance of SQL Server uses port 1433. Even for the default instance you will probably want to open an Inbound Rule on the SQL host that allows any inbound communication on port 1433 or allows the sqlsvr.exe access via this port.  For named instances, the ports that are used to talk to the SQL box are by default dynamic. The SQL browser can advertise the exact ports used by the named instances if UDP is allowed through the firewall on port 1434. A complete explanation of these issues can be found in this document: [more] http://blogs.technet.com/b/nexthop/archive/2011/04/12/using-lync-server-2010-with-a-custom-sql-server-network-configuration.aspx . This document specifically addresses the LYNC client connection, but the principals are the same regardless of the application.

I was working on updating servers when I came across SQL Server 2005 SP4 patch failing to install.  I was able to locate the installation logs in the SQL Server folder.  The reason for failure was that it could not add user NT AUTHORITY\SYSTEM to local group that just happened to be a domain group.  I wasn’t sure if this server used to be a domain controller, but it can have an effect on SQL Server installations as indicated by http://support.microsoft.com/kb/925976. [more]

I started combing through the registry under the Microsoft SQL Server path as listed in the above KB article to see if I could spot any of the keys.  Inside of HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.3\Setup, I saw registry key SQLGroup with a SID that belonged to the domain group listed in the SQL install log.  The way you tell what the SID is mapped to is using a tool called PsGetSid from sysinternals.

I then decided to look up the SID for the local Administrators group using the PsGetSid utility, and then I changed the SQLGroup key data to the SID of the Builtin\Administrators group.  I restarted the SQL services to make sure they could restart after the change.

This time, the install worked and the log was clean.  I did see that NT Authority\System shows up in the local Administrators group on the server.

I was working with a third party vendor to set up SQL Reporting Services. The reporting services install was on a remote server, while the database for SQL Reporting services was on a remote SQL Server.  Once SQL Reporting Services was installed and I was using the Reporting Services Configuration Tool, I kept getting an error when trying to create the Reporting Service database.  The error was non- specific and said I should look into permissions.  I checked permissions and determined that was not the problem.

One interesting note in this case was that there was already a database for another SQL Reporting Services install on the SQL Server named ReportServer, which is the default name. Because of that, I had to change the default name of the database for this install to a different name other than the default – in this case it was EdgeSightReportServer.

The configuration tool would create the databases as requested on the SQL server, but would give errors when running the scripts post creation. In looking at the logs, I found errors stating that the database could not be found – the name of the database in the log was ‘EdgeSight’. The ReportServer portion of the name had been removed when the script ran, and of course was nowhere to be found. [more]

The error was that ‘EdgeSight’ database did not exist. Indeed it did not. The big question is why did the script say Use EdgeSight to start with? I then set out to try the following:

• Try to configure reporting services with a database named XYZReportServer
• Try to configure reporting services with a database named XYZ

What needs to be kept in mind is that the default name for a SQL Reporting Services database is ReportServer.  Generating scripts for these two different database names gave some interesting results.  No errors with the database name as XYZ.  But XYZReportServer will generate the error.  Further testing showed that [AnyName]ReportServer generated this error.  Any other database name worked.

So what’s with this?  I briefly searched and did not find anything on this subject.  Is it a bug?  a feature?  Is it something that only I have experienced? Who knows, but in the meantime if you are getting an error creating a reporting services database check the name.

I’ve been upgrading our internal Office Communication system to the new Lync 2010 environment. Everything I had been reading showed the two servers can run side-by-side, albeit with different pools created. Running side-by-side allows for easy testing and migration rather than switching everyone over and hoping it works. Unfortunately, what I didn’t realize was the two servers use some of the same database names. While Microsoft has documented this, you have to dig a little through the documentation to find it.

I discovered this travesty soon after I hit the magic “go” button. This button (also known as “Publish topology”) started the deployment of the Central Management Store into my SQL instance. This process involves taking existing databases and placing them into restricted mode. Then the installer attempts to drop the database and recreate it. Since these were OCS R2 databases, however, the Lync installer had problems recreating over the existing table layout. The whole process choked, leaving the databases in a funky, inconsistent, restricted state and communicator non-functional. I was able to connect as ‘sa’ and remove the restriction, but the databases were pretty much a lost cause. Restoring from the previous night’s backup allowed everyone get back online.

Moral of the story: Here’s yet another Microsoft product that does not warn you before dropping databases. Be wary when installing applications that automatically set up databases as part of the installation procedure.

We have noticed some problems when SQL grabs all the memory on a machine and leaves no memory for other processes. This is especially true if you are running multiple instances (named instances) of SQL Server on the same box. There is an MSDN article that describes the issue and the steps to remedy the problem (http://msdn.microsoft.com/en-us/library/ms178067.aspx). Here is a blurb from that article: [more]

Running Multiple Instances of SQL Server

When you are running multiple instances of the Database Engine, there are three approaches you can use to manage memory:

• Use max server memory to control memory usage. Establish maximum settings for each instance, being careful that the total allowance is not more than the total physical memory on your machine. You might want to give each instance memory proportional to its expected workload or database size. This approach has the advantage that when new processes or instances start up, free memory will be available to them immediately. The drawback is that if you are not running all of the instances, none of the running instances will be able to utilize the remaining free memory.
• Use min server memory to control memory usage. Establish minimum settings for each instance, so that the sum of these minimums is 1-2 GB less than the total physical memory on your machine. Again, you may establish these minimums proportionately to the expected load of that instance. This approach has the advantage that if not all instances are running at the same time, the ones that are running can use the remaining free memory. This approach is also useful when there is another memory-intensive process on the computer, since it would insure that SQL Server would at least get a reasonable amount of memory. The drawback is that when a new instance (or any other process) starts, it may take some time for the running instances to release memory, especially if they must write modified pages back to their databases to do so. You may also need to increase the size of your paging file significantly.
• Do nothing (not recommended). The first instances presented with a workload will tend to allocate all of memory. Idle instances or instances started later may end up running with only a minimal amount of memory available. SQL Server makes no attempt to balance memory usage across instances. All instances will, however, respond to Windows Memory Notification signals to adjust the size of their buffer pools. As of Windows Server 2003 SP1, Windows does not balance memory across applications with the Memory Notification API. It merely provides global feedback as to the availability of memory on the system.

You can change these settings without restarting the instances, so you can easily experiment to find the best settings for your usage pattern.

One of our new customers using VMware has not been happy with the performance of some of their virtual machines that had been setup before they were our client. Specifically, a couple of their Citrix VMs and a SQL Server 2005 VM have been “sluggish since they were built” according to the IT staff. I did some basic diagnostics on the SQL Server VM and it did seem to have some performance problems. However, since they had already bought a new physical server and started moving the databases to the new install we didn’t spend much time trying to make the VM run well. We decided to upgrade to VMware vSphere v4.1 upgrade before attempting to address any of the performance issues, since less than stellar performance on virtual terminal servers was normal on VMware v3.5.

During the upgrade, I needed to vMotion some of the VMs around to take down one of the ESX hosts. I kept getting a very generic error on several of the VMs and the migration would fail. I lmust have ooked at every setting a dozen times until I finally just shut the VM down and opened up the VMX file to see what might be causing the issue. The problem was that the person who had built the VMs originally had included processor affinity settings in the VMX file. This binds a VM to a specific subset of the physical processors/cores on the ESX hosts. For example, on the SQL Server VM, it was bound to cores 0 & 1. With this setting, ESX was forced to schedule core 0 & core 1 for all operations even though the server had 8 cores. Additionally, on ESX, the service console has processor affinity on cores 0 & 1, but it holds the highest priority. So basically the SQL Server VM (and the other VM I found that was co-scheduled on cores 0 & 1) were fighting with the service console for processor cycles. After removing the processor affinity, the CPU wait time counter in vCenter for that VM dropped 6x. I ended up finding 10-12 VMs with processor affinity set, so that explained why the performance was terrible. 

The moral of this story is to not manually schedule the processors when you configure ESX or Virtual Machines.  Chances are the ESX schedule will be much better than any manual configuration you could put together.

I recently ran into a problem trying to restore a SQL Server 2005 database to a Windows XP machine.  The database back up was created on a Windows Vista machine and I thought that the different OS versions was the culprit.  However, it turned out to be related to SQL server instances and folder paths.  Here is full error I received when I attempted to restore the database using SQL Server Management Studio:

Restore failed for Server 'localhost\sqlexpress'.  (Microsoft.SqlServer.Express.Smo)

Additional information:
System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'c:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\[my database name].mdf'. (Microsoft.SqlServer.Express.Smo)

I searched the Internet using the error message and found several posts stating that it was a problem with the privileges of the user account that my SQL Server Express service was running as. I hadn't changed the account it was running as and I had restored other databases in the past, but I checked the SQL Server Configuration Manager anyway.  As I suspected the service was still running as the default account (Network Service), so that wasn't it.   [more]

After I couldn't find a quick fix on the Internet I decided to look around the options in the Restore Database window.  It turns out the problem was with the paths under the restore options.  The backup was trying to restore the .mdf and .ldf files to the c:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\ directory.  The instance of SQL Server I was working with was storing all it's data files in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\.  Once I changed the paths for the database file and log file to be the same as where my other database files were being stored the restore ran fine with no errors.  For additional reference, here is an article that explains the naming of folders for SQL Server 2005 instances: http://weblogs.sqlteam.com/tarad/archive/2006/06/07/10114.aspx