July 28, 2015 at 11:54 am
Hi
I'm curious, has anyone ever had to script fully dynamic partition switching?
To elaborate the process all you would need is to pass in one parameter..a table name (being the source or transactional table that then moves data to an archive table):
1 have one parameter that passes in a source table name
2 creates staging tables (as source and archive are in different filegroups) on the fly dynamically that are an EXACT copy of the source and archive tables right down to foreign keys and indexes (to allow switching to be successful)
3 switch between staging and archive table
4 Audit the useful information
5 Zero data loss and 24\7 transactions
6 error handling
So has anyone else had to overcome this and if so how?
Would be interested to see others solutions 🙂
Looking forward to responses 😀
Cheers
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
July 28, 2015 at 12:05 pm
Andy Hyslop (7/28/2015)
HiI'm curious, has anyone ever had to script fully dynamic partition switching?
To elaborate the process all you would need is to pass in one parameter..a table name (being the source or transactional table that then moves data to an archive table):
1 have one parameter that passes in a source table name
2 creates staging tables (as source and archive are in different filegroups) on the fly dynamically that are an EXACT copy of the source and archive tables right down to foreign keys and indexes (to allow switching to be successful)
3 switch between staging and archive table
4 Audit the useful information
5 Zero data loss and 24\7 transactions
6 error handling
So has anyone else had to overcome this and if so how?
Would be interested to see others solutions 🙂
Looking forward to responses 😀
Cheers
Andy
I'm not sure you could ever avoid some amount of switching delay, as creating the exact copies of the tables isn't going to take zero seconds, and neither is validating the foreign key constraints. In the meantime, potential new data appears in the original tables. Then what ? Even if your tables are small enough to allow the re-creation to be sub-second in terms of elapsed time, you still have the opportunity for a record to sneak in while the foreign key validation is taking place. I really don't see how this could work. If this were plausible, why would Microsoft have gone down the roads of log shipping and replication ?
This is not to say that if you can find a way to make it work that I won't immediately update my knowledge and look pretty closely at what you did and give credit where credit is due. I'm just a tad skeptical...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 28, 2015 at 3:38 pm
Hi Steve
Thanks for the reply
There is a cavieat I forgot to mention..my apologies
Rows are only ever inserted sequentially and never updated and archiving takes place once a month and only for data > 90 days old so inserted rows during the process is not an issue as I'm just archiving the months data prior to 90 days
Have you ever done anything like this in the past?
Cheers
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
July 28, 2015 at 7:18 pm
Andy Hyslop (7/28/2015)
Hi SteveThanks for the reply
There is a cavieat I forgot to mention..my apologies
Rows are only ever inserted sequentially and never updated and archiving takes place once a month and only for data > 90 days old so inserted rows during the process is not an issue as I'm just archiving the months data prior to 90 days
Have you ever done anything like this in the past?
Cheers
Andy
That certainly makes a difference. I have yet to run into data that static. Everything I've ever seen is moving all the time. Even the data warehouses are often active replication targets. As I'm usually on contract, I change environments regularly. I'm not a partition expert, but I know that partition switching can do some neat stuff. Just test the crap out of it and ensure it's fairly seamless with no burps before relying on it.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 28, 2015 at 9:01 pm
Hi,
I've done a fully dynamic scripts which take the database, the table and sometimes the partitioning column. They were a mix of TSQL and Powershell steps.
The process includes creating a new partition for the new data, switching the old one into the stage table, bcp the stage table into a text file, dropping the staging table and finally dropping the empty files/file groups.
July 29, 2015 at 1:58 am
Thanks for the replies both - I'm surprised more people haven't had to code something like this
When I was asked to do this my first thought was I want 1 dynamic process that can be used for all tables, I don't want the headache of maintaining / ensuring that all our staging tables are amended along with any changes the Devs may make to the source / archive table and changing any partitioning scripts to deal with those changes :w00t:
Makes me wonder why more people haven't done it :ermm:
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply