question

shusta avatar image
shusta asked

Secondary SQL server for reporting

Hello, I've been asked to to create a secondary copy of several databases for daily reporting. I'm thinking I can use Log Shipping as my solution but I'm not 100% on that. Is there a better solution for this? This would be between two SQL 2016 Stnd versions.

Here's what I have right now:

  • The current databases are set to Full and have TRN log backups every hour
    • would creating a schedule in log shipping mess with my current backups?
  • The secondary location needs to be interrogated for reporting purposes
    • what can be done so the secondary databases are left in a state the allows reporting to be run but backups to also be restored?
  • Can I perform one full and then TRN logs for there on?

Thanks,
Scott

reportinglog-shippingsql-server-2016logshippingsql server 2016
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image
anthony.green answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

shusta avatar image
shusta answered

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?

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image
anthony.green answered

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.


1630415018551.png


1630415018551.png (39.6 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

shusta avatar image
shusta answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image
anthony.green answered

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

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

shusta avatar image
shusta answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image
anthony.green answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

shusta avatar image
shusta answered

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..


screenshot035.jpg


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.


screenshot035.jpg (26.7 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

shusta avatar image
shusta answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

shusta avatar image
shusta answered

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?

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.