Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
 Posted - 2005-04-04 : 04:56:09
|
| Hi all,We currently have a manually coded version of log-shipping (inherited from SQL 7 days) that is relatively unstable, and yet fiarly critical in our environment :(So naturally I want to move to a MS solution, with all the positives it would give us, but given my brief review of the BOL and articles I've found, I have some concerns.Speciifcally, I have a requirement for:>Varying restore intervals - basically, over night I would need less frequent refreshes, and specific point-in-time that need to be available for quite a while, while during the day I would like a fairly frequent refresh, say every 15 minutes>External triggering and pausing of the schedule - One of the night point-in-time is to be used for a critical extract that must be done a that specific point-in-time, and we need to stop/pause LS until this extract is successful. Also, there are certain extracts etc. that would run based on triggering from an external scheduling toolReading the articles, I am also uncertain whether my scenario of 3 source server's, and 1 destination server, would pose an issue (with a seperate server/instance running as monitoring). Our intention is not to use this log shipping to create a failover environment, but to create a Reporting/Extract/Ad Hoc "mining-type" query environment.So - any ideas?*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-04-04 : 05:02:11
|
| Wouldn't advise the ms version.Here's one that is pretty simple.http://www.mindsdoor.net/SQLAdmin/LogShipping.htmlNot sure what you would do with the 3 source servers though. Log ship to 3 different databases then use views to combine maybe?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2005-04-04 : 06:31:15
|
| Thanks Nigel,Doesn't look too dis-similar to the log shipping that people developed in SQL 7 days (but it was a quick glance).In our case, I have about 8 different database that I want to log ship to this reporting environment - hence the 3 different sources servers - views to combine wouldn't necessarily be necessary (and would impact upon restores, wouldn't it , since they are objects? That can't be created in a non-restored ((non recovery)) ).*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2005-04-04 : 07:09:31
|
An example of what it is I need to achievetime line Source Server1 Source Server2 Source Server3 Target Server1 Mon Server100:00external scheduledjob finished at00:32 ship log (db1) restore log (db1) Control & Mon Turn off LS on DB1 until Job X on TS1 is completed04:00 if enabled, if enabled if enabled if enabled, ship log of ship log (DB2, ship log (DB5, restore log (db1, DB1 DB3,DB4) DB6,DB7,DB8) db2,db3,db4,db5, db6,db7,db8)06:00 if enabled, if enabled if enabled if enabled, ship log of ship log (DB2, ship log (DB5, restore log (db1, DB1 DB3,DB4) DB6,DB7,DB8) db2,db3,db4,db5, db6,db7,db8)07:00 if enabled, if enabled if enabled if enabled, ship log of ship log (DB2, ship log (DB5, restore log (db1, DB1 DB3,DB4) DB6,DB7,DB8) db2,db3,db4,db5, db6,db7,db8)08:00 if enabled, if enabled if enabled if enabled, ship log of ship log (DB2, ship log (DB5, restore log (db1, DB1 DB3,DB4) DB6,DB7,DB8) db2,db3,db4,db5, db6,db7,db8)08:15 if enabled, if enabled if enabled if enabled, ship log of ship log (DB2, ship log (DB5, restore log (db1, DB1 DB3,DB4) DB6,DB7,DB8) db2,db3,db4,db5, db6,db7,db8) every 15 minutesuntil 13:0013:00 if enabled, if enabled if enabled if enabled, ship log of ship log (DB2, ship log (DB5, restore log (db1, DB1 DB3,DB4) DB6,DB7,DB8) db2,db3,db4,db5, db6,db7,db8)14:00 if enabled, if enabled if enabled if enabled, ship log of ship log (DB2, ship log (DB5, restore log (db1, DB1 DB3,DB4) DB6,DB7,DB8) db2,db3,db4,db5, db6,db7,db8)every 5 minutesuntil 18:0019:00 if enabled, if enabled if enabled if enabled, ship log of ship log (DB2, ship log (DB5, restore log (db1, DB1 DB3,DB4) DB6,DB7,DB8) db2,db3,db4,db5, db6,db7,db8)22:00 if enabled, if enabled if enabled if enabled, ship log of ship log (DB2, ship log (DB5, restore log (db1, DB1 DB3,DB4) DB6,DB7,DB8) db2,db3,db4,db5, db6,db7,db8)00:00external scheduledjob finished at00:27 ship log (db1) restore log (db1) Control & Mon Turn off LS on DB1 until Job X on TS1 is completed04:00 if enabled, if enabled if enabled if enabled, ship log of ship log (DB2, ship log (DB5, restore log (db1, DB1 DB3,DB4) DB6,DB7,DB8) db2,db3,db4,db5, db6,db7,db8)... and so on... Some of these DB's are external vendor DB's where structure changes cannot be made (i.e. replication GUID's), and they are related (like an external workflow DB, internal CRM, external product engine, etc.)*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-04-04 : 07:13:26
|
| Based on that last post, I definitely wouldn't use MS Log Shipping. Have you looked at SQL Sentry at all? We have some of the same issues you have. We ended up writing our own, as do a lot of people.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-04-04 : 07:57:19
|
| I was suggestiong you put the views in another database not in one that was resored.The link I gave you applies any log files that are there but every filename is timestamped so it's easy to change for a schedule.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2005-04-04 : 08:47:49
|
| Saw that Nigel, thanks!Derrick, - haven't seen SQL Sentry. I'll look around to see what it is, and what it can do for us, and also want to check out the Sql Server Resource Kit, see if that might illuminate me...Anyone know where I can see the Resource Kit?CiaO*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
tky_drkw
Starting Member
1 Post |
Posted - 2006-05-16 : 22:16:23
|
Hello. Out of curiosity... why log shipping (versus replication)? I've recently stepped into a new environment and have taken over the entire infrastructure. Majority of our servers use replication. One use (plus warm standby) uses log shipping. I'm puzzled why we are using log shipping instead of replication. I'm thinking of replacing log shipping with replication, but want to educate myself on the "pros and cons" first.What are your thoughts? Why did your team choose log shipping instead of a replication solution?Thank you,Frank  |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-05-17 : 04:09:36
|
quote:
Originally posted by tky_drkw
What are your thoughts? Why did your team choose log shipping instead of a replication solution?
The two are for different purposes, replication is not really a DR solution.-------Moo. :) |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-05-17 : 10:46:09
|
for replication to be dr, you need to replicate all tables, imagine a database with more than 30 tables to replicate? plus another database... plus another database....and plus, you need to identify PKs for all those tables to replicate them  --------------------keeping it simple... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-17 : 14:40:52
|
| I'm not saying I'd recommend log shipping for this, but it can be done. You can control it by disabling the restore job on the secondary servers. Then you'd need an extra job on the secondary servers that either enables the log shipping restore job or just kicks it off depending on the time of day.Replication can also be handled similarly since it all happens through jobs as well.SQL Server 2000 Resource Kit:http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/default.mspx?mfr=trueTara Kizeraka tduggan |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-17 : 14:48:53
|
| Just re-read your chart...you can't use log shipping. You can only have one source server (primary server) in log shipping. You can have multiple target servers though. I'm not even sure if replication will work for you. I doubt that custom log shipping would work either.The reason why LS (or custom log shipping) won't work has to do with the transaction log chain. Tara Kizeraka tduggan |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-05-18 : 07:17:26
|
| @Tara: Are you refering to me not being able to use Log Shipping to achieve what I want, or Tky_Drkw?@Tky_Drkw: We were not using Log shipping to achieve any measure of disaster recovery. We were using it to create a 'largely' consistent, point-in-time picture of several databases from live systems, from both bespoke and external product databases. This was done for reporting.We did get a customized version of log-shipping working. On a broad level, the target, based on a schedule defined via SQL Agent, read the different source server's msdb's for the databases that it wanted to log ship, determined the log backup names, locations and sequences, copied the files across and applied them in sequence to restore a db top a specific point-in-time - for example 10:20 am. Then it processed the next databases, until it had completed the list of databases required. At the end of this, the list of databases (kept on the 'target server' in a log shipping database) would then have been restored to to standard most recent 15 minute log backup points of all of the source server's - i.e all databases should be @ 10:15. Yes, Tara, we did make use, for ad hoc requirements, of a script executing the SQL Agent Job specifically, and we managed status to ensure that other scheduled jobs didn't interfere.hth*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-18 : 12:24:43
|
| I am referring to the chart that you posted that shows what you want to achieve. It does not appear to be a fit for log shipping due to the transaction log chain. The chain must be consecutive. But I could be wrong. I'd have to see the chain to be sure.Tara Kizeraka tduggan |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2006-05-19 : 09:08:45
|
| >but to create a Reporting/Extract/Ad Hoc "mining-type" query >environment.How big are these databases? If not huge I'd start with the basics and get full backups shipped and restored first (nightly).That sets the pattern of jobs schedules and permissions on the control server.This works well when you don't want to spend consecutive late nights resyncing logs, making extra full backups, stopping and starting jobs, all the while working around your "going to tape" schedule. It depends on what the business requirement is, of course, but it gets the "reporting deliverable" out there faster. Maybe not as concurrent as the end goal of 15 minutes but at least your customers can work on a morning snapshot a couple hours old and you can then strive and promise "real time". That would be the log implimentation and you could eventually stop or minimize the full ship/restore.Just an idea and definitely bound by database size and network capability.Don't forget your dba/util database on the control server for the control tables.That keeps your system "atomic" when a server rebuild/upgrade has to happen."it's definitely useless and maybe harmful". |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2006-05-19 : 09:19:08
|
| >Some of these DB's are external vendor DB's where structure changes cannot be made (i.e. replication GUID's), and they are related (like an >external workflow DB, internal CRM, external product engine, etc.)Even if you could get them working then comes the next version / service pacs / support agreement."it's definitely useless and maybe harmful". |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-05-19 : 10:01:16
|
quote:
Originally posted by Sitka
>but to create a Reporting/Extract/Ad Hoc "mining-type" query >environment.How big are these databases? If not huge I'd start with the basics and get full backups shipped and restored first (nightly).between 20 mb and 35 gbThat sets the pattern of jobs schedules and permissions on the control server.This works well when you don't want to spend consecutive late nights resyncing logs, making extra full backups, stopping and starting jobs, all the while working around your "going to tape" schedule. It depends on what the business requirement is, of course, but it gets the "reporting deliverable" out there faster. Maybe not as concurrent as the end goal of 15 minutes but at least your customers can work on a morning snapshot a couple hours old and you can then strive and promise "real time". That would be the log implimentation and you could eventually stop or minimize the full ship/restore.Just an idea and definitely bound by database size and network capability.Don't forget your dba/util database on the control server for the control tables.That keeps your system "atomic" when a server rebuild/upgrade has to happen."it's definitely useless and maybe harmful".
The old log-shipping systems was had been in place for a couple of years. My modified version went live during June of 2005, and was able to deliver multiple db's, refreshed using a defined SQL Server agent schedule (per the outline initially given, more-or-less), and also using an external scheduling agent that runs the overnight "batch" to trigger the ad-hoc point-in-time requirement.As I tried to describe in my response yesterday, the target server controls and runs the log-shipping, and the sql server agent there 'pulls' backups across to meet the most recent triggered run - i.e if it triggers at 10:20, it fetches the log backups since last execution (per database) until most recent log backup prior to execution (i.e. 10:15 - it will not fetch the 10:30 log backup for a database, even if the job runs long enough that a source database might have such a log backup).On completion of the target server SQL agent job, the databases that are defined and have log-shipping enabled ( a bit switch on the table) will be up to the most recent log-backup point across the source servers (i.e the 10:15 I mentioned).Since go-live until shut-down((about9 months) (I no longer work at that company, and they've shut-down their online presence, and no longer use or need the system),we had 3 issues, all caused by cluster failovers on the 'target system' leaving the SQL Server agent jobs disconnected. No late nights, or re-synching needed, thankfully.*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2006-05-19 : 13:52:52
|
| Nice, aside from the fact that the it's boarded up, but at least the solution was correct.(didn't notice the dates on the post.)"it's definitely useless and maybe harmful". |
|
|
|
|
|
|
|
|
MS Log Shipping - can it help me?