how to alter the data type in a replicated databas

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to alter the data type in a replicated databas

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-06-11 : 04:36:10

Dear All,
i've one database replicated from production server.
now i need to change one perticular table column datatype.
what steps i need to follow to do this?

thankyou very much

Arnav
Even you learn 1%, Learn it with 100% confidence.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-11 : 04:41:02

Use ALTER TABLE ALTER COLUMN. look for syntax and usage in books online.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-06-11 : 04:44:20

You will need to remove the table from replication, make the change and then add it back to replication, then run the snapshot agent and the distribution agent. DO NOT do this if you require high availability and the table is large as it will need to repopulate the table.

The snapshot should only pick up the changes (unless the old snapshot is out of date), although initially it will look like it is going to do everything, just make sure this is done out of hours the first time you do it as there is huge scope for getting it wrong.

Or use sp_changearticle (or sp_changemergearticle if merge replication), not sure how well this works in 2005, but it basically didn't properly in 2000.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-06-11 : 04:55:55

we are using transactional replication...
please provide me steps to implement this as i never did like this in replication database. please give me steps RickD....
thank you very much

Arnav
Even you learn 1%, Learn it with 100% confidence.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-06-11 : 05:33:19

please guide me in this regard
i need the column should be changed in the subscriber database

Arnav
Even you learn 1%, Learn it with 100% confidence.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-06-11 : 05:40:52

i've read the article
http://www.sqlservercentral.com/articles/Replication/alteringacolumnonareplicatedtable/1666/
please let me know in a step by step manner

Arnav
Even you learn 1%, Learn it with 100% confidence.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-06-11 : 05:52:11

Thank you very much friends fianlly i got the clear idea...
i'm following this way.

but still small doubt that....if the publisher and subscriber are on different servers, then how can we give the publisher name and subscriber name to this coding?...please help me

exec sp_dropsubscription @publication = 'tTestFNames'
,@article = 'tEmployees'
,@subscriber = 'RSCOMPUTER'
,@destination_db = 'testrep'

exec sp_droparticle @publication = 'tTestFNames'
,@article = 'tEmployees'

Alter table tEmployees alter column Forename BIGINT null

exec sp_addarticle @publication = 'tTestFNames'
,@article = 'tEmployees'
,@source_table = 'tEmployees'

exec sp_addsubscription @publication = 'tTestFNames'
,@article = 'tEmployees'
,@subscriber = 'RSCOMPUTER'
,@destination_db = 'testrep'

Arnav
Even you learn 1%, Learn it with 100% confidence.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-06-12 : 01:09:41

please let me know here. answere me.

thank you very much in advance

Arnav
Even you learn 1%, Learn it with 100% confidence.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-06-12 : 01:32:34

The subscriber is set up as an internal linked server to the publisher, so just giving the name of the subscriber will drop the subscription. The problem comes if you have many subscribers.

As I aid, make sure you do this out of hours as it may take you some time to get right.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-06-12 : 02:21:04

Thank you rickD for your help and as you said, there are 5 databases in main server which are publishers. and same 5 databases in replication server. each database is replicated to same database in another server. can i proceed in this scenario?

Arnav
Even you learn 1%, Learn it with 100% confidence.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-06-12 : 03:52:05

Yes, but as I said, it will push out the changes, so if these systems are live, be very careful and make sure you have a robust backup plan.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-06-12 : 05:52:07

Thank you very much RickD....

i need to stop the replication? or can i do directly on the production machine? and at the same time is it necessary to run the script on subscriber also?......

Arnav
Even you learn 1%, Learn it with 100% confidence.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-06-12 : 06:33:43

http://www.replicationanswers.com/AlterSchema2005.asp

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-06-12 : 07:38:17

Great link RickD....
thank you very much

Arnav
Even you learn 1%, Learn it with 100% confidence.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources