March 25, 2009 at 6:11 am
With reference to this very neat article,
http://www.sqlservercentral.com/articles/Replication/alteringacolumnonareplicatedtable/1666/
by Paul Ibison.
I dont just want to alter any existing field. nooo no no. I need to alter the primary key.
I have a replicated table with a key field, lets call it 'Id'
Id is an identity field not for replication, which is seeded with an increment of 1.
My product is required to operate on multiple replicated databases, so if the application cant reach one databse, it reaches another, and the data is shared accross all of them. I'm more of a developer than a sql server admin by the way so replication is someone elses cup of tea.
I need the Id to be incremented as normal on each server, so the 'not for replication' Id field thing is a problem. I need it not to be independant of each location, and the Id's need to be shared and incremented across each server.
My replication management knowledge is 0, and we have a guy who sorts that side of things, while i deal with data and table updates if needed. I perform updates using the sp_repl procedures, which has served me well so far.
To solve this, I've written code in my application to determine the next id needed, and then insert to the table including the new id, rather than sql server determining the id on insert. problem is, i cant insert into that field, so need to strip off the identity seed stuff.
I've tried the example in the article, but of course it has issues dropping a key field.
Can I make my TempId field the primary key, in order to drop Id, and then switch the key back?
or is there a sp_repl stored proc that will allow me to alter the table structure to remove the increment from Id?
This is a rarely used online tool, so the chances of two users selecting the same Id while connected to different databases are less than winning the national lottery. But maybe theres a better way?
The guy who maintains our databases isn't around so cant talk to him about it at the moment.
any advice?
Kindest regards,
kinnon 😀
March 25, 2009 at 12:59 pm
Identity columns and replication are a dangerous combination.
You should consider add location to the PK
OR
Use sequence tables to handle your Id needs.
* Noel
March 26, 2009 at 6:33 am
Noel,
Thanks for that. I think the location idea might have to be the one, as it would mean the least code changes in the application.
I'm thinking, based on what you've said, I convert the Id field to VARCHAR, looking something like LON1_X, or CDF1_X where X is the actual id, and LON1 or CDF1 could be a couple of the location names.
Saying that, I still want to modify the Id field based on my original question to remove the increment and do the Id work programatically as i think it would be easier to get rount the replication.
Any further info?
I guess it would be easier just to add a location field on the db side of things, but would incurr a heap of recoding to include that field in the application, so this would be a last resort as it will hold the project up big style. Am looking for a solution that involves the least amount of application coding.
Also, whats a sequence table out of curiosity (did i just curse/swear)?
Thanks and regards,
Allan.
March 27, 2009 at 8:45 pm
noeld (3/25/2009)
Identity columns and replication are a dangerous combination.
Identity columns and replication are a dangerous combination for the central subscriber model, specifically because you can have a PK conflict when two publishers generate the same value. There's nothing wrong with using an identity column when you have only one publisher.
March 27, 2009 at 8:50 pm
I'm thinking, based on what you've said, I convert the Id field to VARCHAR, looking something like LON1_X, or CDF1_X where X is the actual id, and LON1 or CDF1 could be a couple of the location names.
I guess it would be easier just to add a location field on the db side of things, but would incurr a heap of recoding to include that field in the application, so this would be a last resort as it will hold the project up big style. Am looking for a solution that involves the least amount of application coding.
It sounds like based on the situation you're in anything you do means that you're going to have to tear down replication, make changes to your PK, then rebuild your publication and subscriptions because you can't alter the PK on a replicated table.
That said, what I've done in a central subscriber model for tables that use an identity column is add a second column for the servername with the default value of @@servername. The PK is two part on the identity column and the servername. The nice thing about this approach is that you know which server generated the row and you avoid any PK violations that would otherwise occur when two publishers generate the same identity value.
March 30, 2009 at 12:59 pm
Kendal Van Dyke (3/27/2009)
noeld (3/25/2009)
Identity columns and replication are a dangerous combination.Identity columns and replication are a dangerous combination for the central subscriber model, specifically because you can have a PK conflict when two publishers generate the same value. There's nothing wrong with using an identity column when you have only one publisher.
It really all depends of what you call "nothing wrong". The fact that you may need to use replication for failover purposes makes the use of Identity columns a pain because of the reseeding needed at failover time.
The use of automatic identity range handling in merge is IMHO broken!
If the table has an identity column and you are planning to use substreams you could be in for a surprise unless you are running in manual mode.
I could enumerate more issues that are simply inherent to Identity columns and not replication but I don't want to beat a dead horse here 😉
* Noel
March 30, 2009 at 1:50 pm
It really all depends of what you call "nothing wrong". The fact that you may need to use replication for failover purposes makes the use of Identity columns a pain because of the reseeding needed at failover time.
OK, fair enough, though I wouldn't personally use replication for failover. There are better mechanisms, e.g. log shipping & mirroring, that are more appropriate.
The use of automatic identity range handling in merge is IMHO broken!
Merge replication is, for all practical purposes, a multiple publisher model. I said there's nothing wrong when you have a single publisher. And yes, I agree that you can get into sticky situations with identity columns in merge publications.
I could enumerate more issues that are simply inherent to Identity columns and not replication but I don't want to beat a dead horse here 😉
I bet you're a fan of Joe Celko! :hehe:
March 30, 2009 at 3:12 pm
I bet you're a fan of Joe Celko! [Hehe]
Believe me, I am not :w00t:
* Noel
March 31, 2009 at 2:34 am
The applications should be able to interact with any site and communicate with any other applications on other sites as if they were all being fed from the single location. We found that in some instances with the existing system, some clients had connection issues trying to reach our server, and websites hosted near our server; which is apparently due to bottle necks and points of failure in the net in their areas ... so we've been told. This is our solution, so we have servers in multiple countries to maximise the possibility of each client establishing a connection.
I'll add in site id, and have the publication redone with the keys stripped off and managed via the application. this will ensure there is no chance of a conflict.
Just need to get hold of my sql server admin guy ........
Thanks again and regards,
kinnon.
March 31, 2009 at 1:52 pm
Good Luck!
🙂
* Noel
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply