SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Replication (2012)
 P2P replication
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jmh9403
Starting Member

USA
6 Posts

Posted - 07/01/2014 :  19:51:53  Show Profile  Reply with Quote
I am setting up 3 servers for P2P replication
CMC-A server 1
CMC-B server 2
CMC-C server 3

All are running windows 2008R2
All are running SQL 2012 Inter prize

Server 1 has the primary database and tables
I 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 time

The problem I have is setting up P2P replication to servers 2 and 3
Replication 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

USA
37466 Posts

Posted - 07/01/2014 :  20:28:31  Show Profile  Visit tkizer's Homepage  Reply with Quote
Use DBCC CHECKIDENT to modify the value: http://msdn.microsoft.com/en-us/library/ms176057.aspx.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jmh9403
Starting Member

USA
6 Posts

Posted - 07/02/2014 :  14:36:59  Show Profile  Reply with Quote
I checked it out and understand how it increments the IDENTITY col
Where do I put the code for each table
Would I put the code in the stored procedures for Subscribers
I'm kind of lost now
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 07/02/2014 :  14:40:52  Show Profile  Visit tkizer's Homepage  Reply with Quote
You would just run it one-time for each replicated table on each server.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jmh9403
Starting Member

USA
6 Posts

Posted - 07/03/2014 :  16:40:36  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

You would just run it one-time for each replicated table on each server.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 07/03/2014 :  16:42:36  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by jmh9403

quote:
Originally posted by tkizer

You would just run it one-time for each replicated table on each server.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/







Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jmh9403
Starting Member

USA
6 Posts

Posted - 07/03/2014 :  17:01:16  Show Profile  Reply with Quote
When I use DBCC CHECKIDENT my table of 500 rows
I give it 501 - 2000 ID using DBCC CHECKIDENT
If the table goes up to lest say 800 rows
now 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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 07/05/2014 :  16:50:43  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jmh9403
Starting Member

USA
6 Posts

Posted - 07/13/2014 :  13:22:50  Show Profile  Reply with Quote
I under stand using DBCC CHECKIDENT
I would like to get away from using it because of the limitation of running out of ID's
Could 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,3
Wants 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,3
and server 3 and it's ID's would be 3,3
Server 2 and 3 are not being used yet and have no data
If what I am trying to do is not practical I will use DBCC CHCKIDENT
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 07/13/2014 :  18:59:15  Show Profile  Visit tkizer's Homepage  Reply with Quote
You will never run out of ids if you use bigint.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jmh9403
Starting Member

USA
6 Posts

Posted - 07/14/2014 :  09:06:44  Show Profile  Reply with Quote
I want to tell I really appreciate your help
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 07/14/2014 :  12:13:05  Show Profile  Visit tkizer's Homepage  Reply with Quote


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000