alternative for backup/restore for seperate Reporting DB

Question

Post reply

alternative for backup/restore for seperate Reporting DB

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

repent_kog_is_near SSCertifiable Points: 7278 More actions · Answer 1

Thanks to both Jeff for excellent points on SAN.

1)

On the database mirror + database snapshot option, are you referring to these 2 technologies:

http://technet.microsoft.com/en-us/library/cc917680.aspx

http://msdn.microsoft.com/en-us/library/ms175158%28SQL.90%29.aspx

If so, won't the Original Database be hit twice- for both reads and writes, as the database snapshot only points to the original database for the reads. Please correct me if I am wrong on this.

2)If we do not need multiple instances of SQL Server to access the reporting databases, could we just settle for 'alter database set read_only' instead of Scalable Shared Database?

Dan

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

Dan,

For item 1 - yes, those are the technologies I was talking about. And, no - you would not be hitting the original (production) system because the database snapshot is done from the mirror. The setup is:

1) Setup database mirroring in asynchronous mode (high performance).

2) Create database snapshot from mirrored version of production database

Since both the mirror and the snapshot will be on a different system using storage that is separate from your live system, the impact on your production system is minimal.

For item 2 - yes, you can just set the database to read_only. Again, the idea is that if needed you can move the scalable shared databases with this easily enough.

To summarize:

Option 1 requires Enterprise Edition for the Database Snapshot feature.

Option 2 requires Enterprise Edition for the Scalable Shared Database feature.

If you are already licensed with Enterprise Edition - this is not a concern and you can choose whichever option you prefer. If you do not have licensing for Enterprise Edition - that would be an additional cost to use Option1. There may not be additional cost for Option 2 - if your organization has already purchased the necessary components on the SAN, but it may involve additional costs. You definitely need to research and figure out which one is going to work for you.

Also, there are other options that have been shown so you really need to follow up and determine which one is going to be the best one for your organization.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

repent_kog_is_near SSCertifiable Points: 7278 More actions · Answer 3

Jeff

Thanks for the points. I have never used mirror before. Do I need one extra server to host the mirror?

Would there be benefits to doing DB snapshop instead of hitting the mirror directly.?

Dan

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

repent_kog_is_near (10/20/2009)
Jeff
Thanks for the points. I have never used mirror before. Do I need one extra server to host the mirror?
Would there be benefits to doing DB snapshop instead of hitting the mirror directly.?
Dan

Yes, you would need another server to host the mirrored database. In either option, you would want this on another server because your goal is to reduce or eliminate contention on the production system due to reporting. That is why you want to do this - correct?

You can create database snapshots on the production system, but a database snapshot uses a sparse file to store the delta only. When reading from the snapshot, you are actually reading from the production database which is not going to help. Besides, the goal here is to offload reporting onto another server that won't interfere with the production system.

If you use SAN snapshot - depending on how you do that (e.g. Snapclone, snapshot, other...) the results will be different. If you are using a snapshot on the SAN - it is the same as a DB snapshot. If you are using a Snapclone - that is a separate copy of the database (cloned). There are also ways to create mirrors on a SAN, so that would be another difference.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

repent_kog_is_near SSCertifiable Points: 7278 More actions · Answer 5

Jeff

Just thought about it.. we may have to throw in or remove some indexes into the reporting DB.. that essentially rules out the DB Snapshot/Mirror and Shared Database, doesn't it? (because they are read-only)

Dan

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

Yes, if you are going to be altering the database in any way - that is going to eliminate a lot of possibilities and make the process much harder.

You can still use a SAN snapclone process - which basically creates a read/write copy of the database. However, you then have to also build in a process to recreate your additional indexes or drop existing indexes (I wouldn't drop them, I would just disable them).

In that scenario - you would have to create the scripts to do this. You could create them on a different database, in a folder, or something else - then run a process to execute the scripts. Could get rather ugly as things are added/removed, but it is doable.

I think at this point, you would be looking more at replication and/or ETL processes and setting up a data warehouse.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

repent_kog_is_near (10/20/2009)
Jeff
Just thought about it.. we may have to throw in or remove some indexes into the reporting DB.. that essentially rules out the DB Snapshot/Mirror and Shared Database, doesn't it? (because they are read-only)
Dan

Any method that depends on read-only copies of the database will have several problems:

You cannot modify the security of database objects, add or remove database users, or add or remove users from database roles.

You cannot use stored procedures that change data in tables in the database.

You cannot make changes to database objects.

repent_kog_is_near SSCertifiable Points: 7278 More actions · Answer 8

Jeff & Mike

Thanks for your input on the RO DBs. yes, RO has it's downfall, but some of the good features that would work for us are RO featured (Mirror, Shared DB, Snapshot DB).

Jeff you had said "if you are going to be altering the database in any way - that is going to eliminate a lot of possibilities and make the process much harder."

Did you mean to say "make the process much easier"?, as we are eliminating choices now.

If SAN Snapshot (or whichever is best fit) works, we do not mind adding a process to create or disable indexes.

Were you thinking of transactional replication? and then add/disable/remove as needed through job or a Import/Export process for the DW reporting?

Thanks

Dan

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 9

repent_kog_is_near (10/20/2009)
Jeff & Mike
Thanks for your input on the RO DBs. yes, RO has it's downfall, but some of the good features that would work for us are RO featured (Mirror, Shared DB, Snapshot DB).
Jeff you had said "if you are going to be altering the database in any way - that is going to eliminate a lot of possibilities and make the process much harder."
Did you mean to say "make the process much easier"?, as we are eliminating choices now.
If SAN Snapshot (or whichever is best fit) works, we do not mind adding a process to create or disable indexes.
Were you thinking of transactional replication? and then add/disable/remove as needed through job or a Import/Export process for the DW reporting?
Thanks
Dan

No, I meant harder because you have to create a process that is going to run after the process that creates your snapclone. Then, you have to put in a process to manage changes in that process so new indexes that are created are added into this script. Management of the solution becomes much harder...

Yes, some form of replication to get the data you want to report on in a separate database used only for reporting. Or, use an ETL product (SSIS, Informatica, etc...) to extract data from production and load into a reporting/DW system.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

repent_kog_is_near SSCertifiable Points: 7278 More actions · Answer 10

Jeff

Thanks for the analysis. It was very helpful.

We talked about the database system/model best suited to replace the backup/restore.

Now, would going with a new server be the only best option? How about moving the new reporting database (based on snapclone or replication or SSIS) on a separate drive with its own spindles? The reason is this will be a major consideration as the company prioritizes their new server needs. A new server will make a lot of difference as it will have all the memory & CPU for the replicated database, but if that is not possible, would a new drive be a decent secondary option?

thanks

Dan

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 11

The best option is another server - that will offload the processing completely and remove any contention between the production system and reporting.

Be aware that creating a new database, even on separate spindles - will still have an impact on your production system. Worse, if you don't have enough I/O channels even offloading to separate spindles won't help much since that I/O still has to go through the same network interface.

It's a balancing act - you do the best you can with the resources you have.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 12

I don't know much about SAN snapshots, but I think you need a special license for that!

My blog

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

repent_kog_is_near (10/20/2009)
Jeff
Just thought about it.. we may have to throw in or remove some indexes into the reporting DB.. that essentially rules out the DB Snapshot/Mirror and Shared Database, doesn't it? (because they are read-only)
Dan

Nope... because if the SAN does it, they are NOT read only. They are left as fully functioning databases with all methods and features left intact.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)