August 23, 2018 at 10:10 am
Evening Guys,
After not fully reading the MS Docs on the CloneDatabase command, I over looked the bit where it warns about using the cloned DB in production. Annoyingly.
I've been hatching a plan to use a Cloned Database in production.
What is it about Cloned DB's that make them not usable / risky in production? And is there a way to test the risks in advance?
Cheers
Alex
August 23, 2018 at 11:16 am
alex.sqldba - Thursday, August 23, 2018 10:09 AMEvening Guys,After not fully reading the MS Docs on the CloneDatabase command, I over looked the bit where it warns about using the cloned DB in production. Annoyingly.
I've been hatching a plan to use a Cloned Database in production.
What is it about Cloned DB's that make them not usable / risky in production? And is there a way to test the risks in advance?
Cheers
Alex
One of the bigger issues is that it isn't supported on a production server. It was really designed for diagnostics outside of the production environment.
For some troubleshooting or testing scripts that change the schema, you can reduce the load and risk on the production server if you clone it and move it off.
When Microsoft has it in the documentation that it isn't meant for or supported in production and also has one or two KB articles about the same and in addition provides the warning and non-support in production when you execute dbcc clonedatabase - usually that means they really, really, really mean it.
I can't really think of any good use for a clone in a production environment - it's a duplication of the schema, stats, query store that you already have on the production server. So I'm curious - what were you planning on using it for?
Sue
August 24, 2018 at 2:22 am
We have a 10TB DB that has been historically built with the wrong collation. Well, not necessarily the wrong collation at the time. Just wrong for what the front end application now supports. It's a vendor app we have no access to the code. We need to upgrade this Vendor App to the most recent to get the features that were added but it simply wont work with SQL_* Collation. Changing the collation to Latin1_General_CI_AS works. I surmise its a hard coded check when the application starts up. But that's neither here nor there as we can't change it, and the vendor won't support it.
Annoyingly, this DB has about 600 tables, each of which has a varchar or nvarchar (it seems they randomly chose varchar and nvarchar depending on the weather) and some text fields too.
The process of changing these is an utter raging ball-ache. And has to be completed in a very small window: a weekend - Friday 7pm until Sunday 10pm.
CLONEDATABASE would allow us to easily create an empty replica in advance, copy it to a new instance, restart that instance with something like the following -q switch
sqlservr -m -T4022 -T3659 -s"SQLEXP2014" -q"SQL_Latin1_General_CP1_CI_AI"
Then park this new DB wit the correct collation next to production, and start loading up the static data ahead of time (release date) and then on Friday night of the release weekend bulk load the bigger tables. This also allows us to do some tidy up / archiving on the data before hand, and end up with a neater tidier production database.
The other method of changing collation a major hassle. Dropping all the indices and constraints and altering the column collation per table per column, etc, etc and just doesn't complete in a normal weekend.
August 24, 2018 at 10:24 am
alex.sqldba - Friday, August 24, 2018 2:22 AMWe have a 10TB DB that has been historically built with the wrong collation. Well, not necessarily the wrong collation at the time. Just wrong for what the front end application now supports. It's a vendor app we have no access to the code. We need to upgrade this Vendor App to the most recent to get the features that were added but it simply wont work with SQL_* Collation. Changing the collation to Latin1_General_CI_AS works. I surmise its a hard coded check when the application starts up. But that's neither here nor there as we can't change it, and the vendor won't support it.Annoyingly, this DB has about 600 tables, each of which has a varchar or nvarchar (it seems they randomly chose varchar and nvarchar depending on the weather) and some text fields too.
The process of changing these is an utter raging ball-ache. And has to be completed in a very small window: a weekend - Friday 7pm until Sunday 10pm.
CLONEDATABASE would allow us to easily create an empty replica in advance, copy it to a new instance, restart that instance with something like the following -q switch
sqlservr -m -T4022 -T3659 -s"SQLEXP2014" -q"SQL_Latin1_General_CP1_CI_AI"
Then park this new DB wit the correct collation next to production, and start loading up the static data ahead of time (release date) and then on Friday night of the release weekend bulk load the bigger tables. This also allows us to do some tidy up / archiving on the data before hand, and end up with a neater tidier production database.The other method of changing collation a major hassle. Dropping all the indices and constraints and altering the column collation per table per column, etc, etc and just doesn't complete in a normal weekend.
You can script the database and all objects with no data. That gives you a new database and schema.
The difference would be that it won't have the statistics or query store which you really don't need. But it would be supported.
Sue
August 29, 2018 at 2:15 am
I've always avoided using script generation for rebuilding a DB with purely because historically (SQL Server 2005) it never really worked on complex databases.
But I am on SQL Server 2016.
So today I will investigate and see what results I get. Will post back in due course. Cheers Sue for reminding me of a feature I avoid 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply