May 18, 2007 at 1:17 pm
I am in the process of migrating one of our SQL server 2000 to a new server. I copied all the system database files and user data base files to the the new server and started the server.
The new server name is different.
I updated the column originatingserver in table sysjobs, I have question on following:
1. I cannot drop the old server, it says the server has publications
2. How do I change the server name in all dts packages? which system table in msdb? (We have about 200 packages)
3. The old server is a distributor and publisher. How do I change the name to new server name?
Thanks in advance,
Rushdi
May 21, 2007 at 10:46 am
question: does you new server see your old server, because if it does, then I do not recommend doing operations like sp_dropdistributor, because it points at you real production is you have restored master database on the new server and go and modify system catalog directly, after all if your new server never have being a distributor, you do not have to clean up anything exept of system catalog. so all the below consider carefully if your new and old server can see each other. yank network cable from the new one if not sure, at least it will not be able to bust anything in real production.
you are going to have some interesting stuff to deal with, but not to bad.
some things will require direct modification of system catalog
if your server was a distributer then here is how you check it:
exec master.dbo.sp_MSrepl_check_server 'youroldservername'
/*
Server: Msg 20583, Level 16, State 1, Procedure sp_MSrepl_check_server, Line 30
Cannot drop server 'youroldservername' because it is used as a Subscriber in replication.
*/
after that try
exec sp_dropdistributor @no_checks = 1, @ignore_distributor = 1
/*
Server: Msg 945, Level 14, State 2, Line 183
Database 'MyPublications' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
*/
--means cleaning system catalog manually
sp_configure 'allow updates',1
GO
RECONFIGURE WITH OVERRIDE
GO
update sysdatabases set category=0 where name ='distribution' - or whatever is distributor's database name in your case
drop database distribution
update sysdatabases set category=0 where name ='MyPublications'
drop database "MyPublications"
--Allow updates to the system tables
sp_configure 'allow updates',0
GO
RECONFIGURE WITH OVERRIDE
GO
--clean up linked servers
exec sp_dropdistributor @no_checks = 1, @ignore_distributor = 1
/*
Server: Msg 208, Level 16, State 1, Procedure sp_dropdistributor, Line 91
Invalid object name 'msdb..MSdistpublishers'.
*/
exec master.dbo.sp_MSrepl_check_server 'youroldservername'
--The command(s) completed successfully.
here is how you build drop linked servers stuff
select 'exec sp_dropserver '''+srvname+''', ''droplogins''' from sysservers where srvname'Yournewservername'
renaming your server:
exec sp_dropserver 'youroldservername', 'droplogins'
EXEC SP_ADDSERVER @SERVER = 'yournewservername', @LOCAL = 'local'
here is how to kill publisher if the database does not exist (files do not exist)
exec sp_configure 'allow updates',1
GO
RECONFIGURE WITH OVERRIDE
GO
DECLARE @optbit int
SELECT @optbit = 32
UPDATE master..sysdatabases SET category = category ^ @optbit
WHERE name = 'MyPublisher'
SELECT @optbit = 1
UPDATE master..sysdatabases SET category = category ^ @optbit
WHERE name = 'MyPublisher'
exec sp_configure 'allow updates',0
GO
RECONFIGURE WITH OVERRIDE
GO
and here is how to check that you have clean database descriptions after that
DECLARE @retcode int
DECLARE @distributor sysname
DECLARE @agentname nvarchar(100)
DECLARE @distbit int
DECLARE @distribdb sysname
DECLARE @foundSubscriber int
DECLARE @proc nvarchar(255)
declare @optname sysname
declare @name sysname
DECLARE @transpublishdb_bit int
DECLARE @mergepublishdb_bit int
declare @job_name nvarchar(100)
declare @alert_name nvarchar(100)
declare @dist_rpcname sysname
declare @alert_id int
SELECT @transpublishdb_bit = 1
SELECT @mergepublishdb_bit = 4
SELECT @foundSubscriber = 0
SELECT @distbit = 16
SELECT name, N'publish' FROM master..sysdatabases
WHERE (category & @transpublishdb_bit) 0
UNION
select name, N'merge publish' from master..sysdatabases
WHERE (category & @mergepublishdb_bit) 0
-- should return no rows
repeat for every database that has publications
select * from sysservers where srvname'yournewservername'
most likely you'll find distpublisher in there
/*
srvid srvstatus srvname srvproduct providername datasource location providerstring schemadate topologyx topologyy catalog srvcollation connecttimeout querytimeout srvnetname isremote rpc pub sub dist dpub rpcout dataaccess collationcompatible system useremotecollation lazyschemavalidation collation
------ --------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------ ----------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ------------ -------------- ------------ ------------------------------ -------- ---- ---- ---- ---- ---- ------ ---------- ------------------- ------ ------------------ -------------------- --------------------------------------------------------------------------------------------------------------------------------
7 1609 repl_distributor SQL Server SQLOLEDB MyDistributor NULL NULL 2001-01-01 19:18:32.200 NULL NULL NULL NULL 0 0 Mydistributor 0 1 0 0 1 0 1 0 0 1 1 0 NULL
*/
sp_configure 'allow updates',1
GO
RECONFIGURE WITH OVERRIDE
GO
delete from master.dbo.sysservers where srvid=7
delete from master.dbo.sysxlogins where srvid = 7
sp_configure 'allow updates',0
GO
RECONFIGURE WITH OVERRIDE
GO
after that is done you just install distributer, script publications from your old server
here is how to kill a subscriber
-- dropping subscribers
--setting subscribers
--cleanup
exec sp_dropsubscriber @subscriber = 'Mysubscriber'
kill publication
-- Dropping the transactional publication
exec sp_droppublication @publication = N'mypublication'
in your script that creates you replication you'll need to alter server names where it comes from and goes to
-- Adding the transactional publication
use [Mypublication]
exec sp_addpublication @publication = N'mypub', @description = N'Transactional publication of Distribution database from Publisher Mynewservername.', @sync_method = N'native', @retention = 336, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'false', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @logreader_job_name = N'Mynewservername-mypublisher-2'
GO
as about DTS, I'm afraid, that it depends on your packages and how are they using connections. I'd suggest using dns names (aka alias), not real server names, makes it easier to handle migrations. you may consider going through your 2000 DTS's and altering them manually and testing that stuff (because it really depends on hoe are you using your connections in there)
May 21, 2007 at 11:04 am
Liliya,
Thank you so much for your information. With your information I could have migrated the replication to the new server , but our management decided to keep the old server name, and rename the old server mostly due to the number of DTS packages we have to modify. The migration was smooth and everything worked like a charm once we kept the same server name.
June 11, 2008 at 10:38 am
Liliya Huff (5/21/2007)
question: does you new server see your old server, because if it does, then I do not recommend doingexec sp_dropdistributor @no_checks = 1, @ignore_distributor = 1
above solved my problem
thank you! you are a god!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply