# Database Discussions > MS SQL Server 7/MS SQL Server 2000 >  Merge replication

## whitebelt

Hi,

I have just set up Merge replication, I have two servers, server A and server B, the merge replication worked successfully but I don't quite sure which databases should or should not replicated?  If not, what other methods should I use?

I would really appreciated any comments or advice out there!

-whitebelt

----------


## rmiao

If you need sync data in the db on both servers, then you should replicate it.

----------


## whitebelt

thanks for your quick reply!  what I meant was should I replicate every database on the server A to the other server B?  

Thanks for your help!

-Whitebelt

----------


## rmiao

Depends on if you need those dbs on both servers and keep them in sync or not.

----------


## whitebelt

Thanks 'rmiao' for your quick response!  I am a bit confused on this...some people said the master database should not be replicated, it should be backup and restored only in Merge replication, is that a correct statement?

-whitebelt

----------


## rmiao

You can't replicate system dbs at all.

----------


## whitebelt

Thank you for your responses!  I really appreciate it.

-Whitebelt

----------


## whitebelt

I am a bit confused....please help!  

If I don't replicate system dbs, then server B does not have system dbs at all, so how will the server B up and run when the server A goes down?   

My second question:  If I need to have some of login accounts from server A to be on the server B, as you mentioned above, we don't replicate the master database, so how would I get some of the login accounts from server A to server B?  Should I replicate some of the tables in the master db that contain those login accounts?

Thanks in advance for your response!

-Whitebelt

----------


## rmiao

Every sql server has set of system dbs, they are created by sql installation. To copy sql logins, you can use dts copy logins task. Again, you CAN'T replicate system dbs nor anything in them.

----------


## whitebelt

Thank you for your response!  I really appreciate it!  I understand now.

-Whitebelt

----------


## whitebelt

Hi,

I was just wondering what if I just want to migrate some of the login accounts (Not all) from server A to server B, do I still use dts copy logins task to do this?  And when I modify/update the login accounts for particular users, how do I synchronize the changes to the server B?  

Thanks for your help!

-whitebelt

----------


## rmiao

You can copy sql login per db. Even copy all logins, dts will ignore those existing ones.

----------


## whitebelt

Thanks very much for your quick response 'rmiao' !

----------


## yeahiknow987

From your comments and reading between the lines, im not sure sql replication is what your looking for, at least not merge replication.  

you say : 

"so how will the server B up and run when the server A goes down? "

If your trying to create a mirror that will "kick in" when server A goes down then I suggest you look into Mirroring on SQL Server 2005, link below..

http://www.microsoft.com/technet/pro.../dbmirror.mspx

This technology can have clients that (once configured) automatically redirect to the "backup" server should the primary server fail.

Hope this helps, good luck

----------


## whitebelt

Thank you for the input!  I really appreciate it!

I was actually trying to replicate the server using sql server 2000 and we replicate the server using Merge replication.  I wish we would use sql 2005 but we aren't.  

Thanks again for your input!

-whitebelt

----------


## yeahiknow987

ok fair enough,

Are you trying to use the replicated db (on server b) as a mirror/backup or will the server B database be actively used by users?

----------


## whitebelt

We want to have the server B actively used by users.

----------


## yeahiknow987

ah right, that makes more sense ;-)

----------


## whitebelt

Hi,

I have just run the 'dts copy logins task' to copy the logins from server A to server B and the database that I copied was the master database.  I am honestly not sure how to test it to make sure the logins on server B is working.  Any ideas how?  

Thanks in advance for input!

-whitebelt

----------


## yeahiknow987

Try setting up a sql datasource on a client machine to Server b, specifying username and password to connect with, then try a query using that data source (alternatively I think theres a "test datasource" button), if it fails on uid/pwd it will report a login failure

----------


## whitebelt

Hi,

Thanks for your response!  I guess I am not familiar with this test, is there anyway you can walk me through step-by-step how to test it?    

I really appreciate your help!

-whitebelt

----------


## rmiao

Just open query analyzer, choose sql server b to connect to, put in that user id and password.

----------


## yeahiknow987

yeah thats probably the easiest thing to do

----------


## whitebelt

Thank you both for your quick responses!  

I tried to log onto Query Analyzer but I got an error message 'sql server does not exist or access denied', I used the same userid and password to log onto Enterprize Manager to the same server (serverB), and it worked fine.    Does it have anything to do with access right?     I mean if I can log onto the Enterprize Manager, I should be able to log onto the Query Analyzer correct?    I had no problem logging onto this server before though.

Looking forward to your responses!

-whitebelt

----------


## rmiao

If it works in em, should work in query analyzer. Is it windows account or standard sql account? Right click the server b in em and go to edit sql server registration to see what kind of authentication used.

----------


## whitebelt

Hi 'rmiao',

Thank you so much for your quick reply!

I have checked the sql server registration, it's used 'window authentication', so I logged onto serverB again using window authentication connection, and it worked fine, but if I used 'sql server authentication', it does not work.   How do I change/edit my userid so that I can also log onto the serverB using 'sql server authentication' ?

-whitebelt

----------


## rmiao

No you can't since they are different type of sql logins.

----------


## whitebelt

I see!   so I guess it's the only way to test is to log onto the serverB with whatever the authentication is set in the sql server registration for that particular userid and password ?  Am I correct?

-Whitebelt

----------


## rmiao

That's right.

----------


## whitebelt

Thanks again for all your responses!

-whitebelt

----------


## peace2007

Dears, 

I need to merge data between two sql2005 servers. Could you let me know how I can do that step by step. 

Thanks in advance, Sheila

----------


## rmiao

Check your own thread, don't post same issue in every thread.

----------


## whitebelt

Hi Sheila,

I have searched the internet for you this morning from google, here is what I have found:

http://www.sswug.org/discuss/RPL  or http://www.sswug.org, right now they have 7 days trial going on.  If I were you I just gonna sign up to get that 7 days trial to get the information you need.  Then cancel later if you decide you don't want it.

I will keep an eye out for you, if I find something related to merge replication for 2005, I will let you know.  I have learned doing merge replication from doing research on "google", it's funny but you can get a lot information from there!

Regards,

-whitebelt

----------


## whitebelt

I was just wondering if a table from a destination can be pushed over to the source table using Merge replication (sql 2000) ?  I used to just export it over  but not sure if there's different way to do it?

Your response is really appreciate !

-whitebelt

----------


## rmiao

Can't if it's not in publishing article.

----------


## eng_125_nour

i'm realy want help i need any books in replication

----------


## rmiao

Check books online.

----------

