| Author |
Topic |
|
SQLCode
Posting Yak Master
143 Posts |
 Posted - 2006-03-27 : 16:11:57
|
| Till recently, I was of the impression that log shipping is bunch of stored procedures written by users that Backup databases on source Move them from source server to destination, Restore it on Destination I totally missed the SQL 200 in built Log Shipping capabilities thru' maintenance plan. I am going to try this. I thought I did not find much stuff on this in forums. Also, looks like after log shipping the db should be in restricted or stand by mode. Now, this log shipped db is going to be my source for reports and transformation processing. How does it work if it is always on restricted/standby mode ?any more info or links or help is greatly appreciated |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-03-27 : 16:25:02
|
quote:
Now, this log shipped db is going to be my source for reports and transformation processing.
Log shipping should not be used to get your data in sync for your reporting environment as every time a tlog is applied, users will get disconnected from the database regardless if they are running a query or not. Transactional replication should be used instead. Log shipping is for disaster recovery purposes.Tara Kizeraka tduggan |
 |
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2006-03-27 : 16:34:56
|
| Thanks Tara for replying. You bailed me out several times in the past too.Well, we tried snapshot replication and we have several databases on the same production environment. To do this replication it had to be down for a long time. This didn't work out. This replicated server feeds to the ETL server for warehouse/reports.Can transaction replication be done without the source being down?TIA. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-03-27 : 16:37:37
|
| Yes. That's what we use for our reporting environment. I do not believe that the source has to be down for snapshot replication. I'm not sure as I've never used it. But I'm pretty sure!Tara Kizeraka tduggan |
|
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2006-03-28 : 12:18:11
|
| Thanks Tara,I have to check with our production folks, but does snapshot not mean or freeze production, when it is taken? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-03-28 : 12:58:38
|
quote:
Originally posted by SQLCode
Thanks Tara,I have to check with our production folks, but does snapshot not mean or freeze production, when it is taken?
No it does not freeze the source database.Tara Kizeraka tduggan |
|
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2006-03-28 : 13:36:56
|
| Tara,When a snapshot is taken, it is probably locking up those tables and hence all other queries are qued up making it extremely slow. There are several dbs on the same server cluster about 50-80G in size.At least that is story I got. Hence they were able to do this snapshot only when we could bring the system down which is very rare and not practical.Are we doing something wrong that it is locking up? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-03-28 : 13:41:09
|
| A snapshot should not do this. An initial snapshot is needed to start transactional replication. I can't imagine that it is locking the tables as it's just bulk outputting the data. When they say lock, what do they mean? Did they run SQL Profiler, sp_who, sp_who2, sp_lock to see this or are they just guessing?Tara Kizeraka tduggan |
|
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2006-03-28 : 15:11:24
|
| They apparently were shared locks but the sizes of the tables made everything extremely slow. We are now leaning towards log shipping. I have code that takes back ups(full and tlog), move files between servers and restore.Now, this replicated data server is the source for our ETL. We have to implement this immediately and do not have any additional servers to be standby just for replication.I am planning on bringing data only for let say 4 days. day 5-7, take it back on normal db mode can be used for ETL. We cannot be most current with log shipped data but thats ok.Do you think this will work?How reliable is MS replication? Should I not use my code and use MS Log shipping set up?We do have Enterprise SQL 2000.I saw your earlier posts and you mentioned about MS not being very handy?Please I need help.TIA> |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-03-28 : 15:19:03
|
quote:
I saw your earlier posts and you mentioned about MS not being very handy?
Not sure what you mean by that.Replication is very reliable. With transactional replication, you only need to do the snapshot the first time to setup the tables and initial data. I'd highly not recommend using log shipping for your reporting/warehouse environment. This is mostly what replication is for. You don't need a standby server for replication. You can replicate to another database on the same SQL Server instance.Tara Kizeraka tduggan |
|
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2006-03-28 : 15:42:53
|
| Sorry I mixed up Log shipping and replication. I am going to strongly suggest for Trans Replication. But What if I am allowed only Log shipping for immediate implementation.I saw your earlier posts on Log Shipping and you also suggested to write code to do that and not use MS Lg shipping? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-03-28 : 15:44:04
|
| We use MS' log shipping, but some people here prefer to roll their own. The problem with log shipping is that you must disconnect your users when applying a transaction log. This is usually not acceptable in a reporting or data warehouse environment.Tara Kizeraka tduggan |
|
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2006-03-28 : 16:01:43
|
| I have to write a recommendation irrespective of what we implemented as a short term solution.So you are saying the transactional production database takes no major hit when a snapshot is generated and does not lock anything and will not cause major slowdowns?Thanks for your valuable advice |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-03-28 : 16:06:25
|
| I can't say that for sure. We've not experienced any performance problems when the snapshot is run. You'll have to test to determine what the impact is.The snapshot is the same regardless if you use transactional or snapshot replication. It's just that with transactional, you only do it once.Tara Kizeraka tduggan |
|
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2006-03-28 : 16:10:29
|
| You mean you do not have to do it again even when you take your server down (for compiles, maintenances etc and these downtimes happen as often as once in 1-2 months) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-03-28 : 16:14:13
|
| The only time I re-run a snapshot is when I move to another server. When moving to another server, I setup replication again from scratch, which means doing a snashot again.Tara Kizeraka tduggan |
|
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2006-06-02 : 15:45:20
|
| What happens when I a lot of new tables added? How does trans repl figure that? I know it can do column changes, but what about brand new table additions on source that need to be replicated?Do you still not have to generate a new snapshot? |
|
|
|
|
Log Shipping -SQL 2000 in built