How to move data files of the mirror database

Posted on by

The ideal scenario for database mirroring would be to have the principal and secondary server instances be exact clones of each other from a hardware and disk space standpoint. This would ensure that the performance on the mirror instance is the same as the principal instance. But due to multiple constraints, this is not always possible. I recently had a question on how to move a secondary data file to a different physical location on the mirror instance. The catch here is that the physical data file locations on the principal and mirror instances are not the same. Now this can be tricky if not attempted or tested in your environment. The Microsoft SQLCAT(Twitter|Blog) team has a blog post on how to move the transaction log of a mirrored database to a secondary location. We shall use the same logic for the data file as well.

Steps

  1. If the server on which the data file relocation has to be done is not the mirror server currently, then failover the mirrored database so that the server on which the OS File has to be physically moved is the current Mirror.
  2. Change to High Performance mode if running under High Safety.
  3. Execute the ALTER DATABASE statement to move the data file to the new location on the mirror instance.
  4. Pause Mirroring to prevent any automatic failover while OS File Copy is happening.
  5. Stop the Mirror instance.
  6. Copy the file across to the new location (OS File Copy).
  7. Start the Mirror Instance.
  8. Resume mirroring and switch back to High Safety if the mirroring was running under High Safety. The mirroring should continue without any issues. Failback to original server if needed.

If the data file locations are the same on the principal and mirror and the data files need to be moved to different locations on both the server instances, then this is much easier. You would need to fire the ALTER DATABASE command on the principal server instance and then move the data files to the new location after failing over the database. The new data file location would take effect only after the server instance is restarted.

How to use Reporter to analyze SQL Profiler traces

Posted on by

I had a question on the SQL Nexus discussion forum regarding the following error while using Reporter:

When I run Reporter, with the default settings (i.e. DB=PerfAnalysis), I get an error :
Server: (local) Error: 4060 Severity: 11 State: 1 Line: 65536 Source: .Net SqlClient Data Provider
Cannot open database "PerfAnalysis" requested by the login. The login failed.

When you launch Reporter which is part of the RML Utilities installation, the PerfAnalysis database is the default database that the Report will attempt to connect to. See illustration below:

image

You will need to change the baseline database to the database into which you imported the profiler traces using SQL Nexus. In my case, I had imported the SQL Profiler Traces captured into a database called nexus_blocking.

I shall cover a walkthrough on importing data into SQL Nexus sometime later.

Script to remove a constraint and drop columns

Posted on by

Recently there was a need to get rid of a replication column after replication was disabled on a SQL Server 2000 database. The customer wanted to get rid of a column for all the tables which replication had added. And the flip side was that this column had a constraint defined on it. I looked at the constraint names and found that they had a common sub-string “msrepl_tran”. So by joining the system catalogs syscontraints and syscolumns, we were able to create the necessary T-SQL scripts to drop the constraint and the column from the tables involved.

select 'ALTER TABLE [' + OBJECT_NAME(a.id) + '] drop constraint [' + OBJECT_NAME(a.constid) + ']' + CHAR(13) +

'alter table [' + OBJECT_NAME(a.id) + '] drop column ['+ b.name + ']' + CHAR(13) + 'go'

from sysconstraints a

inner join syscolumns b on a.id = b.id

where OBJECT_NAME(a.id) like '%msrepl_tran%'

NOTE: Use CTRL+T and F5 to generate the script above. Inspect the output to ensure that no unintended table gets affected.

How to find out BINN folder path using WMI

Posted on by

A reply to a Tweet on #sqlhelp prompted me to look this up. The question was on finding out the SQL Server BINN folder path. This can be done using WMI in the following manner for SQL Server 2008:


strComputer = "."

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\ComputerManagement10")

Set colItems = objWMIService.ExecQuery( _

"SELECT * FROM SqlService WHERE SQLServiceType = 1 and ServiceName = 'MSSQLSERVER'",,48)

For Each objItem in colItems

Wscript.Echo "-----------------------------------"

Wscript.Echo "SqlService instance"

Wscript.Echo "-----------------------------------"

Wscript.Echo "BinaryPath: " & MID(objItem.BinaryPath,1,InStr(objItem.BinaryPath,"sqlservr.exe")-1)

Next

The SQL Server 2008/R2 WMI namespace has visibility for SQL Server 2005 also. So the above snippet of code can be used to retrieve the BINN path (PathName property contains the fully qualified path to the service binary file that implements the service) for the SQL instance. You can change the SQLServiceType to a different value to get the Binary Path folder for other services as well. The value is the above code is set to 1 for the Database Engine. The ServiceName parameter can be used to filter down the results.

For a named instance, you would need to change the service name to MSSQL$INSTA if your instance is called INSTA.

The same is possible through the much talked about Powershell as well.

Other ways to do this would be to use the xp_instance_regread XSP which is not recommended as it is an undocumented command. An example is show here.

Thanks to @afernandez for pointing out another way through a CLR TVF.

Powershell script to calculate folder size

Posted on by

Just published a PS Script to calculate the TOP 5 folders for a UNC share. The need for this script arose when I needed to find out which folders were consuming the largest amount of disk space on a File Server. I know there are graphical utilities like DiskMon, Space Monger etc. but this script provides me the flexibility of automation like identifying the folder owners or deleting based on some criteria etc.

Click here to download the script.