August 15, 2017 at 7:46 am
Guys,
TLS is a bit of black magic to me; I understand at a basic level how it works but I am not sure how to resolve my current problem without corrupting what I already have.
We are in the middle of a large database migration. In our production environmen we have a LIVE database and a REPORT database and the REPORT database is just a backup-restore of LIVE from the previous night so it is always a day behind - not a problem, the business is happy with this.
In our UAT environment we are A) limited to the amount of disk space we have and B) need to be able to update the REPORT database faster than a full backup-restore (takes about 2 hours). I set up TLS so that the small number of transactions occuring in UAT could be repllicated to the UAT REPORTING database but never turned on the process to load the TLS into the REPORTING database. Since I have done that, we have refreshed the UAT database by taking an updated copy of LIVE and running migration scripts on it. Some of the pending files in the TLS folder are for the 'old' copy of UAT and some are for the 'new' copy including 8GB of migration script transactions.
What is the best way to ensure that my REPORTING now matches UAT?
Should I delete and restore the REPORTING database with what is currently in UAT and if I do that, do I need to delete any of the TLS files or will the process automatically work out where to start from.
Do I need to delete all the TLS files relating to the 'old' UAT database.
Do I need to delete TLS files from before the restore of REPORTING and if so how do I identify them?
TIA
Aaron
August 15, 2017 at 8:54 am
UAT Reporting and UAT source are no longer in sync. Since you never really started log shipping, there is really nothing to be in sync and it's probably cleaner to just start all over at this point. It looks like you have the jobs enabled on the primary but not on the secondary if you have files in the share. You can delete them as the LSNs would be off at this point and you wouldn't be able to restore those log files. Just delete the log shipping and start over.
Remove Log Shipping (SQL Server)
A couple of things to keep in mind - your UAT environment is not really a reflection of what you have in production. You are not unit testing anything for the report database as you have two different processes in each environment. You did mention that you have disk space and time constraint issues but you should also be aware of this discrepancy from production and the business should know as well. They need to understand that the Report database in production and how it's refreshed is not tested in UAT and should be seen as an untested risk for migration.
Usually its referred to as log shipping (also have seen it referenced as LS before) and the two databases being the primary(source) and the secondary. TLS is generally known as transport layer security. Could just be me but I had a harder time reading through this with all the references to TLS and changing that to log shipping as I read through.
Sue
August 16, 2017 at 2:20 am
Thanks Sue,
TLS/LS - My bad 🙂
You are right on all counts. I ended up turning off the existing LS and starting again. I completely agree that UAT does not match production in terms of the process, however the business has said that it is happy to accept the difference in that the REPORTING copy of the database is for staging only to take the ETL workload off the LIVE database and how that stage is created is outside the scope of the UAT testing (on this occasion). Having said that, the volume of transactions in PRODUCTION each day is about .005% of the entire database so LS would probably be a better solution in PRODUCTION as well. This is really the tip-of-the-iceberg in terms of poor processes and performance as a result of some seriously bad design decisions made in the past by ex-colleagues who didn't really understand how to write, deploy and maintain efficient code and systems - not rubbishing them, as we have all been at the start of that learning journey at some point and it is the stuff you don't know that you don't know that has resulted in a massive technical debt that I am trying to pay down so that we can get through UAT.
I now have a separate issue which is that there is not enough space to perform a FULL backup of the UAT database and restore REPORTING so that I can turn on LS but that is a topic for another thread 🙁
Aaron
August 16, 2017 at 7:29 am
Just to clarify in terms of bringing it up with the business, you really want to do that more as a CYA. When we are having to work around issues due to not having the resources to do things in a better/correct/ more efficient way, that essentially is a business decision. So if it doesn't work out the way they wanted it, it should be their problem to own. They may agree to being behind for reporting but when you have migrations added to the mix, you really have no way to test some pieces due to the difference between the environments. If something doesn't work right after a migration and there was no testing due to resource constraints, it really shouldn't fall on you.
Sue
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply