Replication and ID's 1

[megmogg]

Hi all

Have a replication question.

We currently have one server "Server 1" and want to introduce a remote server as backup "Server 2".

I have this working and id's are grouped so server 1 gets so many and server 2 gets so many grouped.

My question is, my Auto ID numbers for one table should be continious.

However, the solution above is not ideal, because server 1 will be the main server and server 2 will only become active if 1 is down. If this is the case, then when 1 becomes active again, the unused group of numbers for server 2 will remain unused and it will appear as there as gaps.

Any ideas for alternatives?

Thanks

 

[mrdenny]

That's the way it's supose to work. Any perticular reason that you want the numbers to be continious?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[megmogg]

Thanks for the reply.

Continious numbers are by client request and is part of their accrediation for the work they do.

I was wondering if there is an alternative because in, for example, accounting scenarios, numbers also have to be continious. Should have been done before?

 

[mrdenny]

You'd have to write your own auto numbering schema and ignore the builtin one to make all the numbers continious.

Using the builtin replication you'll get the numbers split off in blocks.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[megmogg]

Thanks for the reply.

For my sanity, just to confirm the way it would work.

Option 1

If I wrote a seperate routine and had a table to hold the ID's, then both sites could never be on at the same time, it would have to be one or the other.

Therefore, when Server 1 is down and all users revert to Server 2, Server 2 would hold all the info.

When Server 1 came back up, they would replicate and sync and then I would have to switch all users back to Server 1.

Is this correct?

Option 2

Or could I run both servers online all the time and some users connected to Server 1 and some to Server 2 and would the new table with the ID's be OK?
i.e. If Server 1 went down, some users would then be re-routed to Server 2.

Would I ever get duplicates in the table?

 

[mrdenny]

Option 1 - Correct
Option 2 - If you were going to have both servers online at the same time, you'd want to stick with the built-in auto numbering. If you don't you could end up with duplicates being created from each side. If replication got hung up and the changes to the auto number table that you created didn't get replicated to the other server you could end up using the same number on both sides and that would be bad.

What's the end result that you are looking for? It's sounding like Replicaiton isn't going to be your best bet.

Are you trying to get both servers online at the same time, or are you trying to DR your application?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[megmogg]

Great, thanks for all the info.

Answers my questions.

 

[mrdenny]

no problem

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com