Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
charnellebester
Starting Member
1 Post |
 Posted - 2010-01-03 : 04:40:49
|
| HI allI am hoping that you could give me some suggestions on the following: 1: I need a high availability sollution that will allow minimum downtime, for this i decided to configure Transaction log shipping, I am hoping that this will allow me to switch my application to the db when things go wrong. 2: This same database still needs to be backed up daily onto offsite backups a full backup needs to be made and I need to make sure that if anything goes wrong with the transaction log shipping I would still need to be able to restore the transaction logs.3: Our Crystal report writers need to be able to access another copy of the "live" database which can be a day late.Please could you suggest a few methods for me, (Hopefully all automated)..your help is much appreciated.charnelle bester |
|
|
JonRussell
Starting Member
4 Posts |
Posted - 2010-01-04 : 18:21:11
|
| charnelleI use log shipping myself to keep two standby databases, one local and one in another datacenter. For the log shipping, I would highly recommend setting up a “test” database so you can practice database failovers. Also, practice failing back to the primary server. If you document your restore procedures well, you can pull up a database under log shipping in a few minutes. While a couple of minutes of downtime technically may not be “high availability”, it is more than adequate for our needs. For the reporting server, I setup Transactional Replication for a read only copy available to analysts. This is so they can have near real time access to some information. If your analysts can work off one-day old data, and you take daily full backups, you may want to consider a daily restore. This is fairly simple solution and easy to setup. Also, having a daily restore from a backup of the production database is a good way to be testing your production backups on a daily basis. “Backups don’t really matter, restores do” – Steve Jones. If you setup either of these, be sure you keep an eye on your network traffic. If all this activity is going out of the same NIC, you could start having performance problems. To get around this, we have multiple NIC cards on the servers and run all backup and replication activity through another subnet on the other NIC. If you have access to a network engineers, definitely consult with them for this configuration.Jon |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-01-05 : 03:38:12
|
| The cheapest HA solution is probably mirroring. The mirrored database will not be usable for querying on the standby server but you will get a very fast failover should the principal server go down (given that you also use a witness server). Backups will not interfere at all, just do your backups like you normally do.About the reporting database there are numerous options, but I'd guess that replication is the most common solution. Depending on the size of your database you might even be able to do a full restore of the production database on a daily basis to a reporting server...but we need to know a little more about business requirements before making more qualified suggestions.- Lumbagohttp://xkcd.com/327/ |
|
|
|
|
|
|
|
|
Some suggestions on the following