There's a lot that goes into a Disaster Recovery plan. One of them is offsite backups. There are some businesses that don't have backups going offsite, let alone a Disaster Recovery plan.
This conversation has taken place more than I thought it would.
Look, we need these things
The setup for sp_AllNightLog creates jobs for four separate activities
1 job to poll for new databases to back up (primary)
1 job to poll for new databases to restore (secondary)
10 jobs to poll for backups to take (primary)
10 jobs to poll for restores to... whatever (secondary)
Carry on
It turns out that the only thing harder than checking for new databases restored to a SQL Server, is checking a folder for a backup of a database that doesn't exist on another SQL Server.
You’re a systems administrator or database administrator who wants to protect your production SQL Server. However, you don’t have a separate data center or another colo site.
You’re looking for instructions on:
"We'd like to offload our reporting queries to a separate SQL Server."
The first costs are fairly obvious.
Hardware and storage - even if you're running it in a virtual machine, you need to account for the costs of say, 4 cores and 32GB RAM. Not only will you need storage for the databases, but you'll also need to decide whether this server gets backed up, and copied to a disaster recovery data center.
At first glance, if you set up log shipping the way most folks do, it looks like you can't run CHECKDB on a subscriber:
On SQL Server Enterprise Edition, you can try working around it by creating a database snapshot - but that won't work either:
Both Oracle and SQL Server offer several ways to support disaster recovery scenarios. One of the simplest ways to handle DR in the SQL Server world is to use SQL Server log shipping. Oracle doesn't offer log shipping as a part of the core product, but it's possible to set up log shipping in Oracle.
I'm a fan of SQL Server's transaction log shipping. It works in Standard Edition, it's relatively simple to set up, and you can even make your log shipping secondary readable using STANDBY mode.
I've worked with some pretty cool, complex log shipping environments over the years. In one case, we had multiple log shipping secondaries and a load balancer involved to support a full fledged reporting application. It worked pretty well-- with a lot of careful scripting and support.
Let's say you're a DBA managing a 2TB database. You use SQL Server transaction log shipping to keep a standby copy of the database nice and warm in case of emergency. Lots of data can change in your log shipping primary database: sometimes it's index maintenance, sometimes it's a code release, sometimes it's just natural data processing.
I've been working with and talking about SQL Server Log Shipping a lot lately!
I've gotten a lot of great questions about log shipping, so I've put together an FAQ.
Want to know how much it'll cost to back up your database transaction logs to Amazon S3? I'll show you with a snippet of T-SQL. Note the parameters at the top for retention periods. The defaults assume one full backup weekly. DBAs would normally want to do full backups more often, but when you're paying…
