Database Mirroring

[samuraiRocker]

Hi.

I need to have two separate database servers to have the same exact data at all times, and I'm guessing "mirroring" the SQL Server would somehow achieve this, but I'm not sure if that's the best way, or if it's possible at all.

We are trying to have two different servers acting as one with Network Load Balancing, and they aren't clustered with MSCS. When a request is made to these servers, there need to be separate database servers on each of the machines, and they have to have the same exact data.

I'm not really a system administrator, and I'm clueless as to what my options are, and I'm pretty sure there is some other ways to achieve the same result.....

So, what I'm trying to find out is:

1, Is it possible to "mirror" a database server so that they have the same data at all times?

2, Is there a better way of having two database servers, acting as one, without having to have to cluster them?

Any help/comment/suggestion would be greatly appreciated.

Thank you very much

 

[AshleyRickards]

You'll need to setup Transactional Replication. Do you have access to an SQL Administrator? or SQL Books Online will help or Use the Replication Wizard through Enterprise Manager. Ashley L Rickards
SQL DBA

 

[samuraiRocker]

Ashley,

Thanks for your comment. I have tried all day yesterday trying to set up the Transactional Replication, but I'm not sure if it's working correctly.

I have configured Distributor/Publisher and I 'Push'-ed subscription to the Distributor DB, and that went fine, it seems.

However, I tried changing the data in the Publisher database, but it doesn't seem like it's being updated in the Distributor database.

I'm not even sure if that's supposed to be happening, but it would be greatly appreciated if you could tell me how I can test this?

Sorry for being so vague, but I don't get any error message or anything....

Thanks in advance.

 

[AshleyRickards]

Make sure and create a subscription in the target server. If both databases are going to receive updates you'll need to make both servers publisher/distributor.
Run a couple of updates in the publisher and see if the subscriber receives the changes. It should be almost instantaneous. If not look at the job history or replication conflicts error log. Ashley L Rickards
SQL DBA

 

[samuraiRocker]

Ashley,

Thanks again for your message.

When you say 'create a subscription in the target server', do you mean I should 'Pull' subscription from the publisher instead of 'Push'-ing the subscription to the distributor??

 

[AshleyRickards]

That's about right .. kinf of same difference. You need to make sure the target server has a subscription to the publication. I think that should be setup through the wizard. Sometimes it delay's the initial snapshot so you might need to start it manually. Then run a couple of updates and see if the changes replicate. Should work for you.
Ashley L Rickards
SQL DBA

 

[samuraiRocker]

Ashely,

I tried setting it up again and now it works perfectly. Thank you very much for your help.

It seems, though, that distributor also updates the record in the publisher, and this seemed faster than publisher updating the distributor's data.....

I'm sort of confused because I never really intended to have distributor make changes to the publisher database, but it does. Which is actually fine because that's what we want...but I didn't know that I set it up that way....oh well.

Thanks again for your help!

 

[Guest_imported]

We need to duplicate/mirror an SQL database that cannot be stopped and needs to continue having transactions modifying its data. It would appear that the snapshot needs to have the SQL database inactive in order to work

Does anyone know of a workaround?

Thanks

Steve