Identity insert without reseed

Question

Identity insert without reseed

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

ryan13in SSC Enthusiast Points: 145 More actions · Answer 1

Hi Sergiy ,

Thanks for your reply . The case of splitting the identity ranges , such that one server will have positive identity ranges and the other will have negative identity ranges will work as long as we only have 2 PEERS ( servers ) in the replication topology . This will break if we want to have 3 or more peers ( servers ) in the replication topology.

Is there any way we can do what SQL Server replication is doing ?

Thanks for your help .

Smendle Hall of Fame Points: 3969 More actions · Answer 2

salliven (5/14/2013)
How do you know that?
I'm managing more Microsoft replication.
But Microsoft replication is slow and inflexible. Our replication program is fast and flexible (example: maximum 0,5 sec replication latency, more smaller disk overhead, replication between mssql and mysql, etc), but insert without identity reseed is a big problem.

Why not just use a 2 column PK where column 1 is your ident and column 2 is a server id?

kumarcug SSC Enthusiast Points: 184 More actions · Answer 3

Hi ryan13in,

I am also in same case as yours, I found something like replication program establishing sql connection with an additional flag as follows,

provider=SQLNCLI10;initial catalog=DBName1;data source=SQLInstName;replication server name connect option=source;persist security info=false;integrated security=sspi

here is the MSDN documentaion for explanation...

https://msdn.microsoft.com/en-us/library/ee224228(v=sql.105).aspx

Unfortunately we could not esablish user connection with this flag. I have tried in SSMS conection option as well as connect via .net code, I get following error..

"ConnectionString": "Keyword not supported: 'replication server name connect option'."

Please share if you were able to accomplish this task.

Thanks,

Kumar

kumarcug SSC Enthusiast Points: 184 More actions · Answer 4

Hi,

I found the answer after spend a whole day for POC,

We need to connect to SSMS using "Replication=True" flag in the "Additaional Connection Parameters" tab of connection window. Now I am able to insert higher identity value without reseed to current identity. We can also achive this by establishing .net connection using following connection string..

Provider=SQLNCLI10;Initial Catalog=DBName1;Data Source=SQLInstName;Replication=True;Persist Security Info=false;Integrated Security=SSPI

Cheers,

Kumar

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 5

kumarcug (6/15/2016)
Hi,
I found the answer after spend a whole day for POC,
We need to connect to SSMS using "Replication=True" flag in the "Additaional Connection Parameters" tab of connection window. Now I am able to insert higher identity value without reseed to current identity. We can also achive this by establishing .net connection using following connection string..
Provider=SQLNCLI10;Initial Catalog=DBName1;Data Source=SQLInstName;Replication=True;Persist Security Info=false;Integrated Security=SSPI
Cheers,
Kumar

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)