November 11, 2009 at 10:07 am
The scenario is as below
Prod1 - Primary (Production)
Prod2 - DR Site
We copy over .mdf and .ldf files of "master" database from the Prod1 instance to Prod2 instance and restart the Prod2 SQL Service. How it will impact the actual SQL configuration on Prod2 instance. The test which we performed on our server, it replace the master on Prod2 with Prod1 copy, when we looked at the servername on prod2 by firing " select @@servername " the servername is incorrect because in reality it should return Prod2 but it is returning Prod1.
As the matter of fact the services and applications such as DTS are working fine, but still its not correct config as it is reporting wrong instance name on master database.
November 11, 2009 at 11:06 am
First, What is the reason to replace the Master DB on the Prod2 Server, is there any specific reason?
And it is obviously display the server name of the Prod1 since it belongs to that Instance.
Any way I have done replacing MSDB and Model Databases when I needed the Jobs and Schedules, Maintenance Plans to be moved but not tried with Master. and I feel there is no such method described anywhere.
If available, some one would be welcome to include it.
I ask once again what is the reason for this action?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 11, 2009 at 3:01 pm
I don't think copying the master databse from one server to another is the best way at going at a DR scenario like you describe. Not unless this server is going to actually have the same name as the primary server.
If you are worried about master, it may be better to just ensure that any changes to master on the primary server are also done on the DR server. That way you can just worry about the user databases.
Joie Andrew
"Since 1982"
November 12, 2009 at 2:30 am
Hi Joie,
Actually proposal for DR is to implement replicated SAN. As you said both servers having same instance name. which is what i am trying to restore the Master db on DR site having the same instance name. we have named instance "XXXXX" on Prod1 and "XXXXX" Prod2, Prod1 has to be restored on Prod2.
I am fine on my user databases. I was just worried about why the instance name is returning on Prod2 as it is on Prod1, are there in hidden consequences if i have to replace master from Prod1, which is not a good practice but just for Instance name i have to do this. Or is there any better practice you guys have in your mind?
can we create same named instance e.g. "XXXXXX" on both server which are on the same network as well as the assuming the hostnames are the same.
Any further advise is much appericiated on the same.
November 12, 2009 at 2:47 am
A bit more information on the same i am not sure whether this will help to understand the scenario
Primary
Prod1\SAN_TEST
Master
MSDB
MODEL
UDB
DR
Prod2\SAN_TEST
Master
MSDB
MODEL
UDB
Prod2 is switched off SAN replicating .mdf and .ldf from Primary to DR. Primary crashes. Prod2 switched on ran "select @@servername" returned "Prod1\SAN_TEST". when trying to connect to server with "Prod1\SAN_TEST" it fails it needs "Prod2\SAN_TEST" which is another issue in restoring primary master to DR.
November 12, 2009 at 5:56 am
Hi, I got your requirement.
I am not sure 100% about the suggestion I am about to give, since I have not done this anytime.
You can have the Same Host Name and Instance name in the Prod2 (which will then be called Clone of Prod1),but it must be not present in the same domain. You can have that in another domain.
Then your Prod1 and Clone of Prod1 will have same SQLServerName, Host Name and all the Logins, Jobs User DB's etc at both the places.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 12, 2009 at 6:07 am
asif.2.mujawar (11/12/2009)
Hi Joie,Actually proposal for DR is to implement replicated SAN. As you said both servers having same instance name. which is what i am trying to restore the Master db on DR site having the same instance name. we have named instance "XXXXX" on Prod1 and "XXXXX" Prod2, Prod1 has to be restored on Prod2.
I am fine on my user databases. I was just worried about why the instance name is returning on Prod2 as it is on Prod1, are there in hidden consequences if i have to replace master from Prod1, which is not a good practice but just for Instance name i have to do this. Or is there any better practice you guys have in your mind?
can we create same named instance e.g. "XXXXXX" on both server which are on the same network as well as the assuming the hostnames are the same.
Any further advise is much appericiated on the same.
Yes i have tried to copy master mdf and ldf file of old server to new server but instance name and every was same. and i didnt face any problem.
Actually in my scenario i have was assigned a task to down grade the enterprise edition to developer edition so i have copied the master and msdb database files and uninstalled and installed developer edition stopped the sql service and replaced the master and msdb file. and make sure that you should install the SQL server which it was in earlier installation then and then it work fine. else it will not work.
regards
ramu
November 13, 2009 at 1:42 pm
When you copy or restore master from one server to another the new server will have the name of the old server when looking at Select @@servername. If you wish to change the servername in SQL run dropserver to remove current name, then run addserver to use desired name.
November 13, 2009 at 3:44 pm
Dave's suggestion is right on. Create it part of a job to change the server name afterwards so it's all automated.
Another suggestion is instead of copying the master database you could just copy the users/sid and import them into the new server.
Here is a script to do what you need if that is the only information you are worried about. Might be easier...
Would defiantly be quicker.
You could do this with a DTS/SSIS package.
----- Begin Script, Create sp_help_revlogin procedure -----
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END
ELSE BEGIN -- NT login has access
SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
PRINT @tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
IF (@xstatus & 2048) = 2048
SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
ELSE
SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END
IF (@xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @tmpstr = @tmpstr + '''skip_encryption_old'''
ELSE
SET @tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
----- End Script -----
run the above on your source server, and then take the results and run them on your destination server.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply