changing primary key in transactional replication

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 Administration

changing primary key in transactional replication

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2010-12-20 : 15:07:15

Hi Team,
i've configured transactional replication in my site, and for a sngle table, i need to change primary key from one column to another column

how can i do that...
pls help

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-20 : 15:09:55

You will need to drop replication to make a change like that.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2010-12-21 : 12:42:02

just to clarify, you need to drop that article form subscription, make your change and add it back. You dont have to remove repllication completely.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-21 : 13:05:36

Yes thank you for clarifying. I meant to say the article only and not the entire thing!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2010-12-29 : 11:06:24

hi dinakar,
in this way, do i need to generate a new snapshot? and will it take the entire database snapshot?

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-29 : 11:09:52

It will take the entire database snapshot, but only the new article will get rebuilt.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2011-01-12 : 08:42:24

Actually... if you do it via sp_addarticle, only the snapshot for the new article will be generated.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-12 : 11:23:10

That's interesting as that's not what Microsoft told us recently when we asked this same question. We even saw the entire snapshot get generated.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2011-01-13 : 00:30:53

I havent setup replication in 2008. but When I setup replication from 2000 to 2005 on a 5 TB database we were getting errors on a few tables. I was able to create snapshots for only those tables.

Check out the FAQ from Paul Ibison: http://www.replicationanswers.com/Transactional.asp

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2011-01-13 : 00:45:01

an MSDN forums member has similar issue: http://social.msdn.microsoft.com/forums/en-US/sqlreplication/thread/f01a1fef-dcf8-4673-a172-4063888a046c/

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources