DBA Sensation

August 28, 2012

attached database with single MDF file

Filed under: 3. MS SQL Server — Tags: , , — zhefeng @ 12:15 pm

i need to attach some database and don’t want to copy over the log files, here is how:
EXEC sp_attach_single_file_db @dbname=’TestDb’,
@physname=N’C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf’

After run it, sql server will make a log file for you.

March 17, 2010

How to migrate sql cluster between domains

Filed under: 3. MS SQL Server — Tags: , , , — zhefeng @ 4:54 pm

Migrating SQL Server 2005/2000 cluster from one domain to the other domain is not supported by Microsoft. However, here is the way how to do that.
i used this way to migrate our production cluster sucessfully.

1. Current settings (active – passive mode)
Cluster name: clustest
Cluster management IP: 10.0.0.142
Virtual server Name: clustestvip1.test.com
VIP: 10.0.0.144
Service account: test\service.product
Cluster group: test\sqlcluster
Node1:
Name –dbnode1.test.com
Physical IP: 10.0.0.140

Node2:
Name – dbnode2.test.com
Physical IP: 10.0.0.204

2. Objective
(1). To change the domain from test.com to test1.com
(2). To change the service account from test\service.acc to test1\service.act
(3). Make sure the sql cluster is still functional after changing

3. Testing steps
(1). Take all cluster resources offline (except the quorum, which cannot be taken offline)
(2). Stop the cluster service on both nodes and change startup type to “manual”
(3). Change the domain of each machine to the new domain and reboot
(4). After reboot, on each machine, change the cluster and SQL service accounts to accounts in the new domain (test\service.acc ->test1\service.act).
Note: according to MSKB 915846 it should be “On one of the cluster nodes, use SQL Server Configuration Manager to change the service account to the new account.”
That’s not true, and you got error from there, have to do it on both nodes in services.msc.
(5). Add the cluster and SQL service accounts to the local Adminstrators group.
(6). Run gpedit.msc to grant the following rights to the new service account:
Path: “Local Computer Policy” -> “Windows Settings” -> “Security Settings” -> “Local Policies” ->”User Rights Assignment”
*Act as part of the operating system
*Adjust memory quotas for a process
*Back up files and directories.
*Debug programs
*Increase scheduling priority
*Increase quotas (not in windows 2003).
*Load and unload device drivers.
*Lock pages in memory
*Log on as a batch job
*Log on as a service
*Manage auditing and security log
*Replace a process level token
*Restore files and directories
7). Update the domain groups SID for cluster services (sql 2000 skip this step)
There are domain groups related to old domain name as below which couldn’t be changed in AD(you can’t find them! They only appear in register as SID at this branch: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\Setup).
So what we need to do is just find the old group sid in registry and replace it with new group sid. Here are the steps:
a. find old cluster group sid by logging in as old service account, then run “whoami /groups” in dos prompt
b. in this case, the service account is service.acc, and cluster group is : test\sqlcluster
c. find the sid for test\sqlcluster is:
S-1-5-21-1679026800-3574736516-1101542067-14559
d. same way to find the new group sid for test1\sqlcluster is:
S-1-5-21-2789037367-2132044359-1364708090-24619
e: replace the old sid with new sid under branch (do a small search there) : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\
8). Reboot both nodes
9). Start the cluster service on both machines (put them back to “automatic” mode again)
10). Bring cluster resources online

Reference:
1. Changing the Domain for a SQL 2005 Cluster
http://nyc-dba.blogspot.com/2007/01/changing-domain-for-sql-2005-cluster.html

2. How to move a Windows Server cluster from one domain to another
http://support.microsoft.com/kb/269196

3. Best practices that you can use to set up domain groups and solutions to problems that may occur when you set up a domain group when you install a SQL Server 2005 failover cluster
http://support.microsoft.com/kb/915846

July 29, 2009

MS SQL Server 2005 alias doesn’t work on 64bit platform

Filed under: 3. MS SQL Server — Tags: , , , , , — zhefeng @ 10:04 am

we recently upgraded our 1 cluster from 32bit platform to 64bit platform.
In order to launch the replication monitor we have to use sql alias name which has been set up on “sql server configuration Manager” -> “sql native client configuration ” -> “alias”.

for 32bit sql client, there is only one option to set up alias. so from 32bit client to connect to 64bit server by using alias, not a problem!

However, if you like me are using 64bit sql client, and happen to set up the alias under “sql server configuration Manager” -> “sql native client configuration ” – “alias”, you will fail to connect to the server by using this alias.
The right spot you setup the alias should be “sql server configuration Manager” -> “sql native client configuration(32bit) ” – “alias”.

i have no idea why on 64bit client you still need to use 32bit client configuration for alias connection. But that is bloody truth:)

Note: if you are using sql server 2005 cluster then you have to keep the same alias in both of 32bit and 64bit client configuration. Otherwise, by missing alias in the 64bit one, you will get trouble with all the jobs and logs

March 2, 2009

sql server cluster re-ip best practise

Filed under: 3. MS SQL Server — Tags: , , , — zhefeng @ 1:36 pm

Changing IP addresses for sql server 2005/2000 cluster basically bases on two steps:
1. changing virtual IP addresses in cluster administrator ( and registry for sql server 2000)
2. changing physical IP addresses

1. Changing Virutal IP addresses in cluster administrator
1).connect to cluster administrator and take all the group resource offline.
2).changing the virtual IP addresses by right clicking every item with ip address resource type, then “property”->”parameter” to change.
Usually for an active-active sql cluster environment, you need at least change three virtual ip address:
Two for each node’s virtual IP, one for cluster management IP.

Caution: for sql server 2005 cluster, after you changing the IP address in cluster administrator, the registry entries for these ip also get updated automatically.
But for sql server 2000 cluster, you have to update these in registry manually in these locations on both nodes:
For a default instance of virtual SQL Server, the following registry value points to a previous IP address-> HKLM\Software\Microsoft\MSSQLServer\Cluster
For a named instance of virtual SQL Server, the following registry value points to a previous IP address->HKLM\Software\Microsoft\Microsoft SQL Server\Instance Name\Cluster\ClusterIpAddr

Reference:
“when you change the IP address on a SQL Server failover cluster node: “Bind failed”” http://support.microsoft.com/kb/319578/EN-US/

2. changing physical IP addresses in windows network properties.
Nothing new here just regular IP changing process.

3. reboot the server.

4. bring the resource online.

5. Verify all the database connections and applications.
Tips: to verify the port binding after ip changing, you can do port scan for the virutal server name of both nodes to see if the designated port are opened for connection.
For unix: nmap -p1433 hostname
For windows: netstat

Blog at WordPress.com.