Solved

BI Archive

Posted on 2014-12-02
13
75 Views
Last Modified: 2014-12-19
1.5TB MSSQL2k8r2 with 2years of sales data.  

We want to do a one time back up and restore of the entire database to a (read only) BI database. Then maintain only 6 months of data in our production environment, with data older than 6 months being archived in a (read only) BI database.

How would you recommend to setup the daily differential updates for new entries from Production db to BI db, only backing up the Inserts and updates and not the delete statements. Then delete the copied data from production db.
0
Comment
Question by:Sleepydog
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40476573
Have you considered partition switching?

Maybe see this previous answer: http://www.experts-exchange.com/Database/MS-SQL-Server/SQL-Server-2005/Q_28297008.html
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40476575
and this one: http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28412426.html

Basically, you use the ALTER TABLE SWITCH statement.

Or am I missing the point of the question?
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40476898
The BI database should use "SET READ_COMMITTED_SNAPSHOT ON".  That will prevent readers from being locked out while the data is being updated and prevent inconsistent reads.

Is your existing sales table clustered on sales date?  [It almost certainly isn't, and almost certainly should be.]

If not, you'll need to create a nonclustered index on sales date.  When it's time to move the data for the next day:
1) BEGIN TRANSACTION
2) copy a full 24 hours of data [or perhaps more if previous data copy(ies) had error] -- midnight of one day to less than midnight of the next day -- from 6 months ago to the BI db
3) confirm that all rows have been successfully copied to the BI db
4) delete rows from the source db
5) COMMIT TRANSACTION

Because of the snapshot isolation, users will be able to read the BI database even as data is being loaded to it: of course they won't see that data until it is committed, but they can read other data with no interference from data being loaded.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 1

Author Comment

by:Sleepydog
ID: 40477256
ScottPletcher- The Software vendor setup the database 2years ago and no one has touched it since. About a year ago we hired a DBA and his manager told him not to touch the database (you break it, you bought it mentality).

Phillip Burton - Altering the production tables is not an option.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40477290
OK, that provides some background, but it doesn't answer these qs:

Is your existing sales table clustered on sales date?

If not, can you create a nonclustered index on sales date?

If you can't even add a nonclustered index, I don't know how you're reasonably quickly going to pull each day's data to archive it as it reaches the threshold date, 6 mos or whatever it is.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40478075
At least you can use the partition switching to import the new data into the (read only) BI database, which will speed to importing up.

As for the export, if you have to use kid gloves and go carefully, so be it - Scott's answer for that bit is a good one.
0
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 40478300
we hired a DBA and his manager told him not to touch the database (you break it, you bought it mentality).
The worst nightmare for a DBA, but I've been there and did that already. He can't change the schema but he can touch the data, right?
I personally like the partition idea but if you can't do that then I recommend to implement a Snapshot Replication. Mind that this solution is not good for very large databases since kills the performance. Do you want to backup all data or some tables only?
0
 
LVL 1

Author Comment

by:Sleepydog
ID: 40478716
Vitor Montalvão - The business can't decide what data is important at this point so they want the entire Database backed up.
0
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 40478735
Well, the business can't decide what data is important and a DBA can't touch the database. So, you are running out of options here.
How big is the database?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40478828
>> At least you can use the partition switching to import the new data into the (read only) BI database, which will speed to importing up. <<

I don't see how that would even be possible.  Partition switches must be within the same filegroup.  How, then, could you possibly do partition switching between different dbs??
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40480282
Please correct me if I'm wrong, but:

1. In Source DB, create another table (Table 2) in Source DB to switch into,
2. Partition Switch into Table 2 in Source DB.
3. Use SSIS to export that into the Target DB into Table 2.
4. Partition Switch into Table in Target DB.

Obviously, not in this case if he can't touch the Source DB.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 500 total points
ID: 40480975
Theoretically I guess.  To me that's far more difficult than simply copying the data directly to a standard staging table or directly to the BI table with snapshot on.  Why get involved with hundreds/thousands of partitions?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40480989
Because if it's a partition per day, then it may make queries quicker?
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question