?
Solved

How to do that with SQL ?

Posted on 2009-12-21
13
Medium Priority
?
278 Views
Last Modified: 2012-05-08
Hello Gurus,
We are in the process of installing a new ERP that is under SQL 2005.

I need to have high availability concerning that ERP and I was considering of having a mirror/failover/cluster/replica of my SQL Box on a remote off-site location.

But I need to be online with both Boxes , meaning that all Transactions are commited to SQL1 are then replicated to SQL2.
SQL2 will not be active unless SQL1 dies.

Which version of SQL does that ? and how it is done ?
How much bandwidth do I need ? a 512KB connection is enough ? or I need more ? How do I calculate how much I need ?
thanks gurus.
0
Comment
Question by:ammounpierre
  • 4
  • 4
  • 3
  • +1
13 Comments
 
LVL 15

Expert Comment

by:jorge_toriz
ID: 26102383
Mirroring is only supported by the Enterprise Edition, the bandwidth depend on the transactions amount that you want to replicate to your mirror server.

Well, for the implementation you can find a lot of books of implementing mirroring in SQL Server, I attach a simple PDF file as an example.
Tutorial-mirroring.pdf
0
 
LVL 10

Expert Comment

by:Murali
ID: 26102400
Obviously you SQL 2005 will do you requirement. As you have mentioned SQL2 will not be active till SQL1 dies. So you dont need database replication which happens in live and which occupys lot of bandwidth. Instead that you can go for data export/ import for a praticular scheduled periods in a day. Like twise in a day or thrice in a day.

The bandwidth depends on your database size and number of transaction on sql.

You can also use the database replication still in SQL 2005, However you bandwith depends on the sql utilization.

I believe 512Kpbs would be enough speed if it is completely allotted for sql replication.
0
 
LVL 10

Expert Comment

by:Murali
ID: 26102407
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:jorge_toriz
ID: 26102422
I differ from the opinion of Murali due to author has commented that he needs a "HIGH AVAILABILITY DATABASE", with an import/export solution you cannot achieve this goal.
0
 
LVL 10

Expert Comment

by:Murali
ID: 26102639
Hi jorge_toriz, I am hoping high availability with one of two servers only. Not both sql servers are writable at the unique time. I considered sql2 is the backup one and readable. Writable in exceptional cases that unless sql1 dies. I thought it would work with scheduling import/export.
0
 

Author Comment

by:ammounpierre
ID: 26102813
jorge_toriz is right. Scheduling the replication is a no go.
I need the 2 SQL to be in full sync always. It is true that SQL2 will only be "updated" via the replication thing from SQL1.
But if SQL1 dies , then I would need to put SQL2 into play till SQL1 is up again .

My problem comes in 2 folds:
1-How can this be done ? what if the connection between SQL1 and SQL2 goes down for some time ? how is it snyced again ?

2-How much is needed as a bandwidth ? The whole database for 1 year would be around 10Gb.
0
 

Author Comment

by:ammounpierre
ID: 26102820
I am a bit confused with terminology here...
what is miroring ?
what is clustering ?
what is replication ?
0
 
LVL 5

Expert Comment

by:spikelly
ID: 26102943
To achieve this purpose,use the MIRRORING feature of SQL Server.
Which version of SQL does that ? and how it is done ?
--> SQL Server 2005 Enterprise Version
How much bandwidth do I need ? a 512KB connection is enough ? or I need more ? How do I calculate how much I need ?
--> The bandwidth is not that much important for mirroring implementation since the changes you make on SRV1 will be commited on SRV2 depending on your mirroring setting; i would recommend the "High Safety with Automatic Failover (Synchronous)"Mode.

Check this for setting up all your mirroring, great article on:
http://www.mssqltips.com/tip.asp?tip=1705
0
 
LVL 5

Accepted Solution

by:
spikelly earned 2000 total points
ID: 26102979
what is miroring ?
what is clustering ?
what is replication ?
Check out microsoft documentaion on all those topics; there well explained on MS website or technet.
Mirroring:
http://technet.microsoft.com/en-us/library/cc917680.aspx
http://msdn.microsoft.com/en-us/library/ms188712.aspx
Clustering:
http://msdn.microsoft.com/en-us/library/ms179530.aspx
Replication:
http://technet.microsoft.com/en-us/library/ms151198.aspx

Mirroring is the easiest and less cost solution to implement;on top of that, it is the suitable solution for you according to what you described in your first post.
0
 

Author Comment

by:ammounpierre
ID: 26103041
BUT IS mirroring an automatic process ?
meaning whenever a transaction is committed to SQL1 it will be automatically "moved" to SQL2 ?
or there is a schedule on it that I can set up  ?

what if the link goes down for 1h or so... when the link is back what happens then ?
thanks a lot for your unvaluable help ! U are great ! all of you !
0
 
LVL 5

Assisted Solution

by:spikelly
spikelly earned 2000 total points
ID: 26103084
BUT IS mirroring an automatic process ?
-->YES IT IS !
meaning whenever a transaction is committed to SQL1 it will be automatically "moved" to SQL2 ?
--> TRANSACTION ARE AUTOMATICALLY COMITTED, you will this  "High Safety with Automatic Failover (Synchronous)"Mode.
or there is a schedule on it that I can set up  ?
--> NO SCHEDULE NEEDED

what if the link goes down for 1h or so... when the link is back what happens then ?
_-> IF THE LINK GOES DOWN, NO WORRY,ONCE THE LINK WILL BE UP AGAIN ON SERVER1 , CHANGES WILL BE AUTOMATICALLY SYNCHRONIZED ON SERVER2;SQL Server do auto-manage all this process.
thanks a lot for your unvaluable help ! U are great ! all of you !
-->That's why we are on Experts-Exchange ! Cheers!
0
 

Author Comment

by:ammounpierre
ID: 26103240
One last question !!! What version of SQL 2005 does the mirroring ?
is it hard to implement ?
thanks
(promise last question !)
0
 
LVL 5

Assisted Solution

by:spikelly
spikelly earned 2000 total points
ID: 26103267
One last question !!! What version of SQL 2005 does the mirroring ?
--> SQL Server 2005 ENTERPRISE EDITION ONLY!
is it hard to implement ?
The easiest high availability solution to implement. I gave you already the link! follow instructions step by step and you get to set that up!; here again:
http://www.mssqltips.com/tip.asp?tip=1705

More here:
http://technet.microsoft.com/en-us/library/cc917680.aspx
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question