| Author |
Topic |
|
jmh9403
Starting Member
6 Posts |
 Posted - 2014-07-01 : 19:51:53
|
| I am setting up 3 servers for P2P replicationCMC-A server 1CMC-B server 2CMC-C server 3All are running windows 2008R2All are running SQL 2012 Inter prizeServer 1 has the primary database and tablesI know I have to set on each table the IDENTITY (1,1) key Col on the servers have to be different The primary server 1 is being used all the timeThe problem I have is setting up P2P replication to servers 2 and 3Replication requires I make a backup of server 1 and restore it to 2 and 3.Now all IDENTITY Col are the same How can I change the IDENTITY Col for each server |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jmh9403
Starting Member
6 Posts |
Posted - 2014-07-02 : 14:36:59
|
| I checked it out and understand how it increments the IDENTITY colWhere do I put the code for each tableWould I put the code in the stored procedures for SubscribersI'm kind of lost now |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-02 : 14:40:52
|
| You would just run it one-time for each replicated table on each server.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
jmh9403
Starting Member
6 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jmh9403
Starting Member
6 Posts |
Posted - 2014-07-03 : 17:01:16
|
| When I use DBCC CHECKIDENT my table of 500 rowsI give it 501 - 2000 ID using DBCC CHECKIDENT If the table goes up to lest say 800 rowsnow I have used 300 assigned ids If it goes back to 500 rows do I get back the 300 ids that I assigned are does it work that way |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-05 : 16:50:43
|
| No, it doesn't work that way. It is always increasing (or decreasing if you set it up that way, very rare). You need to account for this by setting the appropriate ranges for each server and possibly using the bigint data type.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
jmh9403
Starting Member
6 Posts |
Posted - 2014-07-13 : 13:22:50
|
| I under stand using DBCC CHECKIDENTI would like to get away from using it because of the limitation of running out of ID'sCould I Export the database from the primary server (which it's ID's are 1,1) to another database and it's ID's would be 1,3Wants I get everyone on the new database. I would do the same thing for server 2 and the ID's for this server would be 2,3and server 3 and it's ID's would be 3,3Server 2 and 3 are not being used yet and have no dataIf what I am trying to do is not practical I will use DBCC CHCKIDENT |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-13 : 18:59:15
|
| You will never run out of ids if you use bigint.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
jmh9403
Starting Member
6 Posts |
Posted - 2014-07-14 : 09:06:44
|
| I want to tell I really appreciate your help |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
P2P replication
Tara Kizer
Tara Kizer