June 14, 2004 at 9:19 am
Its been a whil since I touched replication, so can I have a little advice please?
Database A is replicated to Database B (Transactional replication)
I would like to change the data type of a column on Database A, however due to replication - I cannot.
What is the best way to go about changing the datatype?
Presumably this would be to drop the subscription, make the change to Database A and then create a new subscription? If I do this, presumably the new subscription will sync any data missed during the time that the subscription was deleted? Or will this require an entirely new snapshot etc?
Thanks in advance.
June 14, 2004 at 10:49 am
You'll have to drop the subscription and the publication, make the change to the table, recreate the publication and subscription and resnapshot.
Best way to do this is script out the drop and create of the replication, run the drop, alter table, create in one go and then start the snapshot job.
June 15, 2004 at 2:16 am
Nicholas,
Thank you for your reply - I have never scripted replication before, how should I do this?
June 15, 2004 at 4:52 am
I'm no expert on replication, but can't you do a sp_repladdcolumn to create a temp column, copy the data over, sp_repldropcolumn to remove the old one, then do another sp_repladdcolumn to recreate the column with the new datatype, move the data back and drop the temp column.
I know it's a lot of work, but isn't it better that recreating an entire publication, especially a big publication with lots of subscribers
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 15, 2004 at 4:57 am
Scripting replication is a very simple process. Just drill down to the publication in EM, then right click and select "Generate SQL Script", this will then allow you to create both the drop and create scripts for the publication which includes dropping and creating the subscribers.
June 15, 2004 at 6:07 am
Nicholas,
I tried your method - however, after the snapshot agent has finished, the Distribution agent stops with an error: Log on failed for 'sa'
Any ideas?
June 15, 2004 at 6:10 am
Are there any post snapshot scripts associated with the snapshot for that publication?
June 15, 2004 at 6:16 am
I dont think there are, how would I check for, and delete these?
June 15, 2004 at 6:18 am
You can look at the script, or look in the properties of the publication, under the snapshot tab.
Be careful removing any of these as you might be removing something of large importance.
June 15, 2004 at 6:20 am
Ok, have looked - there is nothing to remove - there are no other files.
June 15, 2004 at 6:21 am
You may get into this situation, when you configure your replication agents to log into the publisher and/or subscriber using "SQL Server authentication" (where you specify a specific SQL Server login, to connect to the publisher and/or subscriber). It will work fine initially, but when you change that particular login's password, you get the above mentioned errors. To solve this problem, whenever you change that particular login's password, make sure you change the password in the enabled publisher's and/or enabled subscriber's properties on that distribution server.
To change the publisher's properties:
- Open Enterprise Manager
- Connect to the Distribution server
- Go to Tools -> Replication -> Configure Publishing, Subscribers, and Distribution...
- Go to Publishers tab
- Click on the properties button (...) against the publisher's name
- Enter the new password for the SQL Server login.
To change the subscriber's properties, repeat the same steps as above, but in step 3, go to Subscribers tab.
Restart your agents, and they should be able to connect to the publisher/subscriber without any errors.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply