question

Is your production database indexed well for all the reports which are going to be run on the copy? If so then yes LogShipping will be an excellent choice, you just need to set expectations that every X minutes the database is unavailable while logs are restored.

If the database is not indexes correct for reporting, and you need to create custom indexes then you are into the realms of transactional replication instead.

But for logshipping, essentially your hourly log backup needs to stop (if you are using OLA's scripts it will skip it automatically, but if your using anything else you need to stop it)

On the configuration, you set the "with standby" option to allow the database to be read from.
On the configuration you set the delay on the restore job to be whatever you have negotiated with the business, but they have to be made aware that if they choose to restore every 12 hours, then the data is going to be up to 12 hours out of date etc. If they want it hourly then its an hour out of date, so its all about communication with who is going to be using it for reporting as if they want real time data then LogShipping is not the tool.

Essentially you restore the full and every log then disable the log backup, then setup logshipping and it will carry on where it left off.

Thanks for those pointers. I will be testing today and will get back to you on any hurdles. One question though. I understand what you mean by "with standby" but is this under the secondary server/database or is this also a setting I need to look for when creating the Log Shipping job on the primary server?

When you configure logshipping there are two modes.

NoRecovery or WithStandby
If you want to query the second machine then you need to use the standby option.

Thanks Anthony. I did see this once I started working through the set-up. Just a few bullet points for anyone else looking to try this in a test environment first

• start with a clean install of both Windows and SQL
• start with a clean install of both Windows and SQL
• start with a clean install of both Windows and SQL

I knew going in that cloning my actual test server might cause issues. And.. it did, after trying the obvious hurdles I'm building two new test VMs to run my scenarios in for the programmers. If you don't know cloning SQL for any other reason than recovery can cause headaches. So I cut my losses and started fresh.

I will follow-up after I get this rolling with an update.

Oh yeah don't clone a SQL instance once it is use.

You can template an installation where you essentially "sysprep" SQL like you would the operating system, then once the OS has been built from the template SQL can be finalised as an installation, cuts deployment times down somewhat.

But we should all be striving for standardisation so having installation ini files and building something into a CI/CD pipeline build with things like DBATools or Jenkins etc to kick off installations is the more newer DevOps style approach to sysprep'ing for SQL now

So I have two new servers using the same SQL/Windows install so there should not be any issues with versions. I have installed SQL Studio and run through the steps above. I'm still not getting any backups on the primary and when I run the job manually it fails as well. All attempts fail with the 14420 error.

All testing is successful including Full and TRN backups when performed outside of the Log-Shipping job(s). What could I be missing? I can even map to the secondary server ship to location with issues.

14420 is out of sync.

How often are you backing up and restoring?

What have you configured as the alert thresholds.

If you backup every 60 but alert at 45 minutes then yeah expect a lot of 14420 errors to be raised.

So this morning I now only get the other 14421 error. This was after I changed the folders where the data was to be backed up to (primary) and then the folder it was to be shipped to (secondary). I know this is wrong but I'm testing looking for issues I may have caused.

Well I had this big list of changes I made to reset everything back to zero... and in the end I'm getting a security error..

What I did this time around was take all default log ship settings and this time asked SQL to backup and restore the database to the second location. Previously I had selected database was already restored.

Both backup and restore folders have the same test account added to the security tab of the folders with Full control. I even shared them out as well thinking with the same rights. The test account is a Windows account. It's obvious what the issue is I just can't figure out where I've missed permissions on the Secondary server.

OK... so after my previous rant knowing this is security but not knowing where to look I combed through what I could and found my issue. On the Primary even though the SQL Service account was tied to the SQL services it was not granted rights to the folder. In my case this didn't fall under Everyone. When manually added I got the access denied error. So I added the account under the Advanced button in Security and that did the trick.

The database has been recreated on the Secondary and it's in Stand by read only. I'll keep an eye on the TRN file backup/restore.

I have to be missing something. After the initial backup it failed for the transaction log shipping. I've looked at the security but it still fails. Everything I read online is pretty straight forward. Any ideas Anthony?