How to move MSDTC from one SAN storage to other

Note: Please take backup of MSDTC folder .

From the current host server(the node where quorum drive visible) ,open the Failover Cluster Management. In the left pane expand the Services and Application group and select the MS-DTC resource. From the summary window note down the Server network name and IP address to use in our later steps.

The next step is to get  the new SAN disk in the cluster. To add the disk to the cluster follow the steps given below:

Ø  Open the windows disk manager.

Ø  Right click the new disk  in Windows Disk Management and select online.

Ø  Right click again and select initialize.

Ø  Finally format it by right clicking the disk and selecting New Simple Volume.

Ø  This will open Simple Volume Wizard. Provide the new drive letter and label for the disk.

Ø  In the Cluster Manager ,select the storage in the  left pane.

Ø  Select the Add Disk in the Action pane and select the proposed new disk.

Now we can see this disk under the available storage group.

Everything is ready to make the move.

Unfortunately we can't move existing MSDTC to the new disk directly. The only option is delete the existing one and add new MSDTC.

Follow the below steps to delete and add new MSDTC resource:

Ø  In the cluster manager ,expand the services and applications

Ø  Right click  the MSDTC  group and select Delete.

Ø  Now the existing MSDTC  disk (call it  X drive) will be available under the available storage.

Ø  Select the old disk  under the storage and click on change drive letter option available in the activity pane

Ø  Select None as new drive letter and click Ok button

Ø  Select the new disk  under the storage  and click on change drive letter option available in the activity pane

Ø  Select X as new drive letter and click Ok button

The last four steps are done to keep the drive letter for new disk as same as the old one. These four steps are not mandatory to perform.

Ø  Right click on the Services and Application and select Configure Services or Application option

Ø  This will open the high availability wizard screen.

Ø  Select next which will list Services or application that can be configured for the high availability. Select the Distributed Transaction Coordinator(DTC)

Ø  Select Next which will open the client access point configuration screen as given below. Enter the network name and IP address which we have noted down in the first step.

Ø  Select Next, which will open the storage configuration screen. This will list all the disk available under the available storage group. Select the appropriate disk and click Next

On clicking Next, it will directed into the confirmation screen and click again on Next will complete configuration. Now the MSDTC is configured in the SAN disk and can be verified by opening the disk in the windows explorer. Now we should be able to see the MSDTC  folder inside the new drive which confirms the movement.

Bring this service online. Set the preferred owner through the properties window and test the failover.

How to open a program in Windows 2008 and above using a different Windows account?

Before Windows Server 2008 there used to be an option to open a program like SQL Server Management Studio using different Windows account. You can open any program and say “Run as”.

This was helpful when you have domain accounts running SQL Services.

But in Windows 2008 and above "Run as" option has been removed.

So to open SSMS as some other domain account you can use following command in command prompt

runas /netonly /user:domain\user "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

Change domain\user and program file exe file

Steps to move system database files with scripts

TEMPDB

1.  ------get file details

use tempdb

go

select * from sys.database_files

2.  -----run following (MODIFY FILE NAMES AND PATH AS NEEDED)

USE master;

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = 'S:\MSSQL\TempDB\tempdb.mdf');

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME = 'S:\MSSQL\TempDB\templog.ldf');

GO

3.  -----stop SQL

4.  -----start SQL

5.  -----run following to vefiry

use tempdb

go

select * from sys.database_files

MSDB

1.  ------get file details

use msdb

go

select * from sys.database_files

2.  ------move files

ALTER DATABASE MSDB MODIFY FILE ( NAME = MSDBData , FILENAME = 'D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf')

GO

ALTER DATABASE MSDB MODIFY FILE ( NAME = MSDBLog , FILENAME = 'D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf')

GO

3.  -----stop services

4.  -----copy files

5.  -----rename old files

6.  -----start services

7.  -----verify

use msdb

go

select * from sys.database_files

8.  -----if SQL Agent does not start check if service broker is enabled

SELECT is_broker_enabled

FROM sys.databases

WHERE name = N'msdb';

--USE master

--ALTER DATABASE ServiceBrokerTest

--SET ENABLE_BROKER;

MODEL

-----use same procedure as msdb

MASTER

1.  Stop services

2.  modify start parameters to new path

3.  copy files

4.       start services

Find last time when transaction log backup

Find last time when transaction log backup

b.type = 'L' means transaction log

b.type = 'D' means full

b.type = 'I' means differential

SELECT   d.name,

         MAX(b.backup_finish_date) AS backup_finish_date

FROM     master.sys.sysdatabases d WITH(NOLOCK)

         LEFT OUTER JOIN msdb..backupset b WITH(NOLOCK)

         ON       b.database_name = d.name

         AND      b.type          = 'L'

WHERE b.backup_finish_date IS NOT NULL        

GROUP BY d.name

ORDER BY backup_finish_date ASC

Uninstalling SQL Server Service Pack

Starting form SQL Server 2008 SP1, you will be able to uninstall service packs from Add/remove Programs and Features. Uninstall wizard will launch through this.

For all versions of SQL before SQL Server 2008 SP1 please refer to following link

http://support.microsoft.com/kb/314823

SQL Server 2012 Database mirroring error (Microsoft SQL Server, Error: 1418)

While setting up mirroring between 2 servers I came across following error

The server network address TCP://<servername>.<domain>.com:<portnumber> can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

After setting up database mirroring security configuration I started mirroring between 2 servers and immediately I got this error. The error however does not tell you much about the actual problem. I tried changing firewall setting, checked that the service account is correct on both primary and mirror. I also tried ping from either machines and it worked fine.

But after checking the BOL I found the issue. Before setting up mirroring you have full backup database on primary and restore it on mirror as “WITH NORECOVERY”.

NORECOVERY specifies that roll back not occur. This allows roll forward to continue with the next statement in the sequence. In this case, the restore sequence can restore other backups and roll them forward.

After restoring with NORECOVERY I did a log backup on primary and restored it on mirror. Once done mirroring worked as expected.

Please note that in SQL Server 2008 & R2 mirrored copy is not readable. In SQL Server 2012 with help of Always On availability groups you can make mirrored copy readable.

SQL Server 2012 Failover cluster error (Windows Event ID 1119)

Recently I installed SQL Server 2012 RTM on a Windows failover cluster. After installation when I tried to fail over SQL server resources from N1 to N2 I got following error

Cluster network name resource 'SQL Network Name (MyServer)' failed to register DNS name 'MyServer.' over adapter 'Local Area Connection*' for the following reason:  'DNS server failure.'

I found the event ID and resolution on technet

http://technet.microsoft.com/en-us/library/cc773512(v=ws.10).aspx

But it was nothing to do with the solution mentioned on technet article.

My problem was SQL server service account was not part of the Local Administrators group. (Which I should have done before installing…dumb!!)

So here is how you add SQL service account to local

1)      Go to Start à Administrative Tools à Server Manager

2)      Expand Configuration

3)      Expand Local Users and Groups

4)      Select Groups

5)      Double click on Administrators

6)      Click on Add button

7)      Enter SQL Service account (Account under which SQL services are running)

8)      Click OK

After doing this I was able to failover SQL Server to passive node