| Author |
Topic |
|
DBADave
Constraint Violating Yak Guru
366 Posts |
 Posted - 2009-02-09 : 23:34:58
|
| What's the best non-SAN option for a high availability? Ideally we would like to have a second server function as a "readonly" reporting server, but I can't think of a good way to do this outside of SQL Server replication. And since our corresponding application is a 3rd party financial system I don't think we will be permitted to add Primary Keys, a prerequisite to transactional replication. Database mirroring is intriguing and of course log shipping is also an option. Which option would you pursue first and is a reporting "readonly" server possible?Thanks, Dave |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-09 : 23:52:14
|
| You can use Database Mirroring. Take advantage of Database Snapshot for reporting purposes on Mirror Server. |
 |
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2009-02-10 : 00:04:14
|
| Is the database snapshot in addition to the transaction log backups we are running and if so, how do you time them with the t-log backups?What type of overhead does it place on the source server?For some background we have 19 databases with the largest being 233GB. Over time (1.5 - 2 years) it will be 1 terrabyte or larger. All other databases are less then 50GB, with most being less then 4GB.Thanks, Dave |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-10 : 13:10:58
|
| You can asynchronous database mirroring to reduce performance issues. You will be creating Database snapshot in Mirror Server for Reporting Purposes. You can use regular transaction log backup to minimize log growth.Also you can consider using Merge Replication. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-02-10 : 13:37:46
|
| We use exactly what sodeep has described. We have a rapidly growing database that we asynchronously mirror to our DR site located 300 miles away. We can't use synchronous database mirroring due to the latency involved in the two-phase commit. We have a SQL job in place on the mirror server that creates a database snapshot on a nightly basis off the mirror. This provides a read-only reporting environment that is at most 24 hours old. It works great. The database snapshot file is so tiny too.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2009-02-10 : 23:55:03
|
| How large are your databases involved in the Snapshot? We have 19 databases with one being 233GB. It is expected to hit 1 TB in about 1.5 years. Would a Snapshot take a long time for a large database?With asynchronous how do you deal with the possibility of data loss?Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-02-11 : 14:31:38
|
| It's currently about 125GB, but that is going to swell very fast in the next couple of weeks. The snapshots are very fast to create and very small in size. I believe the snapshot job takes less than a minute off the mirror.We'd rather lose a few seconds/minutes of data than experience the performance issue with a two-phased commit. Latency with asynchronous database mirroring is very minor even over 300 miles. The data loss risk is low as we'd have to lose the data center first. We use clustering and backup our transaction logs every 15 minutes at the principal site.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2009-02-11 : 15:32:26
|
| Are you saying that if there is data loss you can revert back to t-log backups?We couldn't afford data loss since the system is financial/accounting so losing that type of information could cause problems, especially since it could be client data being lost. I'm not sure how to eliminate that risk.Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Looking for Failover Advice