If you need to keep online access to the data, you've got a couple of options (assuming you're wanting to use just SQL native functionality for the solution, if you are open to 3rd party software and/or hardware, you've got quite a few other options):
1) Replication - most likely transactional replication and a single read-only subscriber (msdn is a good start for an overview, I'd post a link but I can only use 1 at the moment, just google "sql server replication msdn" and it will be at the top)
2) Keep your log shipping configuration to get data to the secondary server and leverage database snapshots combined with a common database and rotating synonyms (see here for details on this type of architecture). This will only work if you are using the enterprise edition on the secondary server (only edition that supports snapshots).
Configuring Log Shipping on a Cluster is a pretty straightforward setup. I've used it many times.
Before we begin, be sure to remove the exiting Log Shipping setup since it's not working. Remove the Secondary, and then remove the Primary.
Prerequisites
First, let's get some pre-requisites down.
- Is the database in the FULL Recovery Model?
- Has a FULL backup been taken?
- Do you have a LOG Backup job in place? If so, be sure to remove this
database from that job. Log Shipping will handle the LOG Backups for
you.
Primary Setup
OK, now let's try to walk through your Log Shipping setup.
First, just try to get the Log Shipping Primary working.
Go back into your database properties and check 'Enable this as as primary database', and then click the Backup Settings button.
Here you need to specify two things: the network path to your LOG Backups, and the local path to the same.
On my server, it is something like this:
- C:\SQL Backups\LS
- \MyServer\LS
Log Shipping Folder Permissions
Next, locate this folder in Windows Explorer, right-click, choose propertities and select the Permissions tab. Here, you will need to add your Primary SQL Agent Service account and ensure that it has Read and Write permissions on this folder.
Next, select the Sharing tab. Here, you will need to share the folder and ensure that the Secondary SQL Server Service account has Read permissions on the share.
Test The Log Shipping Backups
Once you have this in place, let's test your Log Shipping Backups. Look in SQL Agent Jobs and you should see something named LSBackup_YourDatabase.
Right-click the job and run it. Any errors are most likely related to folder and share permissions. Post the details.
Do not configure the Secondary until you get this part working first.
Initialize the Secondary
Once you have the Log Shipping Backup Job running multiple times without and error, run your FULL Backup job once more.
Copy and restore the FULL Backup onto your Secondary server. Be sure to use NORECOVERY.
Secondary Setup
Go back into database properties and the Log Shipping section. Under the Secondary Databases area click on Add. Connect to your Secondary server.
On the Initialize tab choose 'No, the secondary is itiialized'.
On the copy tab, enter the local folder on the Secondary server where you want to Log Shipping backup files to be copy to. Be sure that the Secondary SQL Agent Service Account has read and write permissions to this folder.
Test the Copy and Resore Jobs
Run the Copy job on the Secondary server. Any errors are most likely related to folder and its permissions. Post the details. Log at the job history. Use the built-in Log Shipping reports to see how far it's getting.
Once the Copy job is working, move on to the Resore job. Same testing steps.
Good luck!
Best Answer
WITH NORECOVERYI've done this dozens of times and it's never failed, so if it doesn't work then you might want to update the question with the EXACT steps you've taken.