SQL Mirroring - Database synchronization

[netnut1234]

I have a question regarding a SQL synchronization / Mirroring

At present we have 2 SQL servers that are in an active passive setup. We have installed on the servers a third party software that manage the connection to the database and it the active one goes down then will fail over to the passive database. So to the user the database is always available.
We are having issues with this third party application and want to take it out of the loop.
What we are trying to achieve is to synchronize/mirror all the databases between the 2 database servers. Is there an easy way to achieve this within SQL without using any third party applications. There is log shipping, but as we aren't that confident with SQL we are after some advice as to how we can get our goal of having all databases synchronized between the 2 servers.

Any help will be appreciated
Thanks
Tim

 

[mrdenny]

Big question for you. What version and edition of SQL Server? SQL 2000, SQL 2005? Standard or Enterprise?

Do you have a SAN available?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[netnut1234]

Hi Denny,

Thank for your reply.
We are running SQL 2005 Standard edition.
No we dont have SAN available to use. Would it be beneficial if we had one?
Thanks
Tim

 

[djj55]

SQL 2005 has mirroring that with minor application changes and a monitor server can do what you want. I am not an expert but just finished the chapter in my SQL 2005 book.

Good Luck,
djj

 

[zarkon4]

The following will walk you through the process.

http://msdn2.microsoft.com/en-us/library/ms175059.aspx

 

[mrdenny]

Database mirroring can be used to mirror all USER databases and it supports automatic fail over. However you can not mirror system databases.

Pretty much the only way to get a true high availability enviroment is to setup clustering, however this requires a SAN (or other type of shared storage) between two or more nodes of the cluster. Because of the shared storage requirement this is not a cheap solution.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[netnut1234]

Thanks for all your help with this.
I can see that we will use database mirroring.

The reason for implementing mirroring on our database server is at the moment we have a website that reads from the database, the database servers in an active/passive mode and we are using a third party application to copy over the databases and logs, and to manage the fail over. How would this work if we use database mirroring? As we will have both database servers live, the way the website accesses the database is by IP address and hostname, if with mirroring the database server was to fail over then how would the webserver communicate with the database, as the IP address and hostname will be unavailable, I am unsure how SQL will manage this?

I am not DBA so you will have to bear with me
Thanks

 

[mrdenny]

The failover is handled via the connection string. First you have to upgrade your web servers to use the SQL 2005 driver to connect. Then you have to edit the connection string to include the failover_mirror parameter which has the second servers name in it (I think that's the parameter).

Your system will still be in an active/passive state. Once one database server will be used at a time. However don't forget that you need to license both servers with CPU licenses. Database mirroring isn't a true active/passive system and as both instances are running both instances need to be licensed.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog