| Author |
Topic |
|
rspinell
Starting Member
36 Posts |
 Posted - 2008-07-22 : 16:44:27
|
| Im not sure if im reading the docs right. Can someone answer this question for me.If I have a DB called DB1 on a server called Server1 and wish to use database mirroring to mirror DB1 to a server called Server2 can I use snapshots of DB1 on Server2 ? I wish to use Server2 as a reporting DB that will get updated every 12 hrs. I currently do this process with log shipping in SQL2000 and thought I could perform the same function using database mirroring and snapshots. I believe what I've read in the docs states that I could only do snaphots of DB1 on Server1? Have I got this correct ?My goal is to have DB1 on Server2 so people can hit that DB and do reports from the read only database during the day, and not hit my production box.Thank you. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-07-22 : 16:52:48
|
| Yes , It is snapshot of source database and remember database snapshots are static and consumes space as they are used. For reporting purposes, I suggest you use transactional replication. |
 |
|
|
rspinell
Starting Member
36 Posts |
Posted - 2008-07-22 : 17:08:39
|
| Sodeep thanks for the info, so you would stay with log shipping on SQL2005 if I wanted to use Server2 as a reporting box and have my DB available as read only on Server2? I guess im confused between transactional replication vs log shipping. Why would you use transactional replication over log shipping ? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
rspinell
Starting Member
36 Posts |
Posted - 2008-07-22 : 17:19:51
|
| I believe we looked into transactional replication a few years back, but I believe it locks tables when doing the replication process, am I correct about this? I think that is why we went with log shipping. I was going to use database mirroring in SQL2005, but if I cant do snapshots on server2 of DB1 then database mirroring goes out the window for us. Do I have that right?Thanks for the help on this. I read alot today but it just made me more confused. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-22 : 17:25:57
|
| We've got a system where we create a snapshot of the mirrored database nightly. Books Online says this can't be done unless it's in sync mode, but we are doing it in async mode so BOL must be wrong. It works great for us. But we transactional replication for all of our reporting needs. The database snapshot thing that we are doing is for adhoc queries so that non-DBAs don't break production.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
rspinell
Starting Member
36 Posts |
Posted - 2008-07-22 : 17:40:33
|
| Doesn't transactional replication lock tables ? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-22 : 18:02:59
|
| I've never had an issue with that. We use it on very active mission critical systems.Could you provide the link to the article that describes this locking issue?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-07-22 : 18:16:44
|
| The problem with log shipping is when log tries to get restored in secondary server assuming you have good transaction rate, all users gets kicked out or log doesn't get restored at all. Or you can schedule to restore all logs in offline hours which will pile up logs and data won't be in-synch.So transactional replication is a way to go. Well if your database is static like you said, Then you can create database snapshots but will consume space as i said. |
|
|
|
rspinell
Starting Member
36 Posts |
Posted - 2008-07-22 : 18:20:03
|
| Search for locks in the doc.http://msdn.microsoft.com/en-us/library/aa902656(SQL.80).aspxTara, in your goddess opinion for the scenario I laid out in my first post, what replication do you think would be best for us ? I'm leaning toward sticking with log shipping as this seems the easiest to configure. Database mirroring wouldn't seem to work for us since we need a read only DB to be available for people to do reporting from on Server2. I would need to do snapshots (giving me the ability to have a read only DB with mirroring) but that would only be available on Server1, not on Server2 where I want all reporting done from.Thanks ! |
|
|
|
rspinell
Starting Member
36 Posts |
Posted - 2008-07-22 : 18:23:25
|
| Sodeep if what Im reading is correct I wouldn't be able to use database snapshots since this option is only available for the DB on server1 not on server2, where I want my users to do reporting from. I was originally under the impression I could perform database mirroring to server2 and then perform a DB snapshot of DB1 on server2 and have it available for my users. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-22 : 18:27:14
|
| I would definitely not recommend log shipping for the reasons that sodeep just mentioned. Like I said earlier, we are able to do a database snapshot on the mirrored database, which would be Server2 in your example.The replication locking issue has to do with the initial snapshot only. You do not need to continue to snapshots once it has been setup.Just to be clear, Goddess title <> guru.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-07-22 : 18:34:17
|
| Ok now I got what you mean by lock in transactional replication. Yes while generating snapshot for big tables it places locks so it can take snapshot but you can modify it to @sync method( concurrent(I guess) while doing snapshot (see sp_addpublication). And you can do trasactional replication with backup/restore for whole database as well.see this:http://msdn.microsoft.com/en-us/library/ms147834.aspx |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-07-22 : 22:54:53
|
| >> I wish to use Server2 as a reporting DB that will get updated every 12 hrs.In this case, you can just backup db on server1 and restore it on server2 every 12 hours. Easier to handle and less traffic. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-07-22 : 22:59:44
|
| rmaio, I think your suggestion is really good for small database but not for Huge database. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-07-22 : 23:19:43
|
| Since we don't know that, just give OP all possible options. |
|
|
|
rspinell
Starting Member
36 Posts |
Posted - 2008-07-23 : 10:21:35
|
| All, this is fantastic information, thanks so much for all the help.Rmiao, our DB is around 300GB, so its hard to do the backup restore method.Tara, you stated that you have gotten a database snapshot on the mirrored database to work, which would be Server2 in your example. I think mirroring the DB1 database from Server1 to Server2 and then doing snaphots on DB2 so my clients can access DB1 on Server2 in read only for reporting is the route I would like to go. Do you have more info on how you got this to work? I was confused in some of the docs in regards to after the snapshot is taken of DB1 on Server2. If I do the snapshot at 8am for DB1 on Server 2 and a client queries DB1 on Server2 at 9am, they will only see data as of 8am, they wont hit my production DB to get the "lost" hrs worth of data, correct ? I thought I read somewhere that the client would hit the sparse fill (snapshot) but then try to hit the production DB to get the missing data from 8:01 - 9:00am.Thank you. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-07-23 : 10:25:02
|
| "I thought I read somewhere that the client would hit the sparse fill (snapshot) but then try to hit the production DB to get the missing data from 8:01 - 9:00am."This is incorrect. Database snapshots are static. |
|
|
|
rspinell
Starting Member
36 Posts |
Posted - 2008-07-23 : 10:38:48
|
| Sodeep thats what I figured, I just wanted get a 2nd opinion on this. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-23 : 13:05:12
|
| We didn't do anything special to get the database snapshot on a mirrored database to work. BOL is incorrect when it says it can't be done on an async mirror.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
rspinell
Starting Member
36 Posts |
Posted - 2008-07-25 : 11:52:57
|
| All, I've been pulled into another project, so will need to get back to this next week. I just wanted to say thanks for all the great info. |
|
|
|
Next Page
|
Database mirroing / snapshots on another box ?