Joe Webb Blog

The old adage "An ounce of prevention is worth a pound of cure" can be applied to many topics and areas of life. And while it has its origins in personal health care, nowhere is it more true than in an IT shop. Whether you're talking about high availability solutions or starting with a good database design, planning ahead is well worth the effort. Anyone who has been through an IT crisis can testify to that!

But alas, we live in a dynamic world and we can only make calculated guesses at what the the ounce of prevention should be. Sometimes we cannot anticipate what the future holds in store and we must adapt.

For example in my consultancy, I was recently asked to move all SQL Server databases to another drive on the client's server. No problem! Using sp_detach_db and sp_attach_db this is straightforward. Even moving the Master database and the other system databases is relatively simple using the process documented in the MSDN article.

But what about moving a SQLNS database? Can those simply be moved using the normal detach/attach methodology?

The short answer is yes, but!

Although the move process works, subsequent updates to the instance will be ineffective unless we take some additional actions.

When we attempt to update an SSNS instance, the configuration settings in the ICF and ADF files are compared to the current settings of the instance so that SSNS will know what changes to make to the instance. The update process also checks to ensure the validity of the new and old configurations. Herein lies the problem.

When we created the SQLNS instance, we had the choice of whether to create a new instance and application database or to use an existing database. To have a new one created for us, we had to specify the location of the database files in the FileName element of the ICF and ADF configuration files. This is shown below.

    <!--Instance database information-->    
    <Database>         
	<NamedFileGroup>
		 <FileGroupName>Primary</FileGroupName>
		 <FileSpec>
		 	<LogicalName>PrimaryFG</LogicalName>
		 	<FileName>
				C:\MSSQL\Data\ABCPressData.mdf
			</FileName>
		 	<Size>2MB</Size>
		 	<MaxSize>10MB</MaxSize>
		 	<GrowthIncrement>10%</GrowthIncrement>
		 </FileSpec>
	 </NamedFileGroup>
	 <LogFile>
		 <LogicalName>PrimaryLF</LogicalName>
		 <FileName>
			C:\MSSQL\Data\ABCPressLog.ldf
		</FileName>
		 <Size>1MB</Size>
		 <MaxSize>10MB</MaxSize>
		 <GrowthIncrement>10%</GrowthIncrement>
	 </LogFile>        
    </Database> 

Later when we attempt to update the instance, the process will verify that the database files exist in the location defined in the FileName element of the configuration file. Since we just moved them, it will of course not be able to find them so the upgrade will abort with an error.

The obvious solution is to simply update the ICF and ADF file to reflect the new location of the database files. And that is indeed part of the answer. But according to Books Online, the FileName element of the ADF cannot be updated after the instance is built. So if you only change the FileName elements and attempt to run the update again, you'll receive another error.

You see, the location of each database file as defined in the original ICF and ADF is stored in the instance and application database itself. This can be seen using the following T-SQL and in the figure below, where ABCPressNSMain is the name of my Instance database and ABCPressPressRelease is the name of the associated application database.

SELECT 
      * 
FROM 
      ABCPressNSMain.dbo.NSDatabaseFiles;

SELECT 
      * 
FROM 
      ABCPressPressReleases.dbo.NSDatabaseFiles;

The update process compares the location as documented in the database itself with the information provided in the configuration files. If the two differ, the update aborts with another error.

So, how can we move the database files? In addition to updating the location in the ICF and ADF files, we need to update the information in the NSDatabaseFiles tables of the instance and application databases to reflect the new location. You can use the following T-SQL to do this. Of course you'll need to modify these statements with the name of your instance and application databases.

UPDATE 
      ABCPressNSMain.dbo.NSDatabaseFiles
SET 
      FilePath = 'E:\MSSQL\9.0\Data\ABCPressData.mdf' 
WHERE 
      FileId = 1;

UPDATE 
      ABCPressNSMain.dbo.NSDatabaseFiles
SET 
      FilePath = 'E:\MSSQL\9.0\Data\ABCPressLog.ldf' 
WHERE 
      FileId = 2; 

UPDATE 
      ABCPressPressReleases.dbo.NSDatabaseFiles
SET 
      FilePath = 'E:\MSSQL\9.0\Data\PressReleaseData.mdf' 
WHERE 
      FileId = 1;

UPDATE 
      ABCPressPressReleases.dbo.NSDatabaseFiles
SET 
      FilePath = 'E:\MSSQL\9.0\Data\PressReleaseLog.ldf' 
WHERE 
      FileId = 2; 

Once you've done these two steps - updating the configuration files and the FilePath column in the instance and application database - future updates should work without problem.

Now for the caveat. Microsoft strongly discourages direct manipulation of the underlying tables in a SQLNS instance. I generally try to follow that advice. And although I have not found the process that I've described here to have any adverse side affects, you'll of course want to backup the databases, configuration files, etc, just in case.

Happy Notifying!

Joe