May 10, 2018 at 2:45 am
Hi all
We've got a few databases that we want to start log-shipping from.
Normally, we'd use replication but the 3rd-party software that updates these databases falls over if replication is in place.
The reason for log-shipping to to get the data from server A to a central point (Server B) and then replicate to servers C&D. I'm aware that the databases on server B will be read-only but I'm hoping that the replicated databases on servers C&D will be read-write as in a normally replicated database (so we can add our own indexes, etc).
The steps I'm planning on are as follows:-
Set up share on server B to receive shipped logs (as we're wanting to log-ship from multiple databases, I'll be setting up sub-folders in the share)
Back up database on Server A
Restore database on server B
Start log-shipping from Server A and point to share on server B for where the shipped logs need to go
On server B, set up restored database to receive/replay logs
Set up replication from server B to servers C&D
Now for a couple of (possibly stupid) questions:-
1) Have I missed anything in the steps above?
2) We take regular full backups of the databases I want to log-ship from, how will that affect log-shipping (I'm assuming it will cause some issues if not set up correctly)?
3) With the backups, would we be better taking backups of the databases from server B instead of server A?
As usual, I'll be grateful for any pointers (I've read the documentation)/gotchas I need to be careful of.
::edit::
Extra information......
On server A we have a basic maintenance plan that updates statistics and rebuilds/reorders indexes.
I'm assuming that job can stay in place but would like confirmation one way or another.
May 10, 2018 at 4:27 pm
richardmgreen1 - Thursday, May 10, 2018 2:45 AMHi allWe've got a few databases that we want to start log-shipping from.
Normally, we'd use replication but the 3rd-party software that updates these databases falls over if replication is in place.The reason for log-shipping to to get the data from server A to a central point (Server B) and then replicate to servers C&D. I'm aware that the databases on server B will be read-only but I'm hoping that the replicated databases on servers C&D will be read-write as in a normally replicated database (so we can add our own indexes, etc).
The steps I'm planning on are as follows:-
Set up share on server B to receive shipped logs (as we're wanting to log-ship from multiple databases, I'll be setting up sub-folders in the share)
Back up database on Server A
Restore database on server B
Start log-shipping from Server A and point to share on server B for where the shipped logs need to go
On server B, set up restored database to receive/replay logs
Set up replication from server B to servers C&DNow for a couple of (possibly stupid) questions:-
1) Have I missed anything in the steps above?
2) We take regular full backups of the databases I want to log-ship from, how will that affect log-shipping (I'm assuming it will cause some issues if not set up correctly)?
3) With the backups, would we be better taking backups of the databases from server B instead of server A?As usual, I'll be grateful for any pointers (I've read the documentation)/gotchas I need to be careful of.
::edit::
Extra information......
On server A we have a basic maintenance plan that updates statistics and rebuilds/reorders indexes.
I'm assuming that job can stay in place but would like confirmation one way or another.
Have you run through this in a test environment at all? The first thing is you can't setup the replication once the log shipping is implemented - you can't replicate a read only standby database. If you were to the recover the log shipped database (so it's not in standby) then you could setup replication and it can create the objects needed for replication. But then you would need to restore the log shipped database again to get it in standby and lose the replication objects.
You could backup the database once the objects are there and then restore that backup to standby. I haven't test that for awhile but I thought there were still some other issues once you get it back into standby again. Even if this did get working, it would likely be a nightmare to manage.
In terms of the backups and log shipping, it's the log backups that you would need to be concerned about. You want log shipping to manage the backups of the published database as any log backups outside of that will break the log chain. Full or differential backups don't have an impact.
You wouldn't be able to backup the secondary in the log shipping. I guess you could if you did a restore to get it out of standby but then your starting all over again. You should just backup the primary.
Sue
May 11, 2018 at 1:40 am
Thanks sue
Looks like a small rethink in the plan is needed.
We haven't done anything as yet as it's still in the planning stage.
I suppose we could just log-ship from server A directly to servers C&D providing we can read from the receiving databases as we need to do that for our reporting.
::edit:: It might be helpful if I explained what we're trying to do. The server references here bear no resemblance to my initial post.
We have a server (server A) that hold the data from a 3rd-party application.
This is currently mirrored to server B and the mirrors are snapshotted at midnight.
We then move a lot of data from server B to servers C&D through queries over linked servers (which isn't very elegant and takes a while). There are very few indexes on the databases on server A (and the 3rd-party vendor won't allow any to be added which doesn't help matters. We've asked them to add indexes but they've refused.)
What we want to do is get a copy of the data from server A to servers C&D on a regular basis (e.g. by log-shipping) in such a way as to allow us to add indexes on the databases on servers C&D (as the vendor won't care what we do with our copies of the databases as long as don't touch their copies).
We can't use replication as the mechanism the 3rd-party use to modify the data in their copy of the databases breaks when we try to set up replication.
If log-shipping (in this case) isn't suitable for what we want to do, I'm open to ideas.
May 11, 2018 at 4:01 am
richardmgreen1 - Thursday, May 10, 2018 2:45 AMSet up share on server B to receive shipped logs (as we're wanting to log-ship from multiple databases, I'll be setting up sub-folders in the share)
.
The share would normally be set up on the primary or an accessible network location. The secondary copy job will then copy any log backups locally
richardmgreen1 - Thursday, May 10, 2018 2:45 AM
Set up replication from server B to servers C&D
Since the database is read only you wont be able to setup the log shipped database as a publisher
richardmgreen1 - Thursday, May 10, 2018 2:45 AM
We take regular full backups of the databases I want to log-ship from, how will that affect log-shipping
It won't, full backups do not affect the log chain
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 11, 2018 at 4:24 am
Thanks Perry.
Looks like we're gong to have to have a rethink of the plan,
May 11, 2018 at 7:56 am
richardmgreen1 - Friday, May 11, 2018 1:40 AMThanks sueLooks like a small rethink in the plan is needed.
We haven't done anything as yet as it's still in the planning stage.
I suppose we could just log-ship from server A directly to servers C&D providing we can read from the receiving databases as we need to do that for our reporting.::edit:: It might be helpful if I explained what we're trying to do. The server references here bear no resemblance to my initial post.
We have a server (server A) that hold the data from a 3rd-party application.This is currently mirrored to server B and the mirrors are snapshotted at midnight.
We then move a lot of data from server B to servers C&D through queries over linked servers (which isn't very elegant and takes a while). There are very few indexes on the databases on server A (and the 3rd-party vendor won't allow any to be added which doesn't help matters. We've asked them to add indexes but they've refused.)
What we want to do is get a copy of the data from server A to servers C&D on a regular basis (e.g. by log-shipping) in such a way as to allow us to add indexes on the databases on servers C&D (as the vendor won't care what we do with our copies of the databases as long as don't touch their copies).
We can't use replication as the mechanism the 3rd-party use to modify the data in their copy of the databases breaks when we try to set up replication.
If log-shipping (in this case) isn't suitable for what we want to do, I'm open to ideas.
Can you snapshot over to C and D?
You can certainly log ship from A to C and D and read only access is allowed when in standby mode. The connections do get "kicked out" when the logs are restored on the secondary databases. But it is read only.
Sue
May 14, 2018 at 2:41 am
Hi Sue
We could but we wouldn't be able to add any new indexes (which is the main aim of this as well as getting local copies of the data).
From what's been posted before, I think we can do this (feel free to laugh/correct me).....
1) Back up databases on A
2) Restore database on B
3) Set up replication from B to C & D
4) Put the database on B into stand-by mode (unless that will be done automatically by log-shipping?)
4) Set up log-shipping from A to B (which hopefully won't need a new database backup/restore)
Also from what's been posted before, I'll also need to get any log backups stopped so it doesn't break the chain.
Anyone any thoughts?
May 14, 2018 at 7:06 am
richardmgreen1 - Monday, May 14, 2018 2:41 AMHi SueWe could but we wouldn't be able to add any new indexes (which is the main aim of this as well as getting local copies of the data).
From what's been posted before, I think we can do this (feel free to laugh/correct me).....
1) Back up databases on A
2) Restore database on B
3) Set up replication from B to C & D
4) Put the database on B into stand-by mode (unless that will be done automatically by log-shipping?)
4) Set up log-shipping from A to B (which hopefully won't need a new database backup/restore)Also from what's been posted before, I'll also need to get any log backups stopped so it doesn't break the chain.
Anyone any thoughts?
That's not much different from your first approach and it won't work. You can't put a database into stand-by - you do that with a restore process. And once you do all of that, you will have broken replication.
Sue
May 14, 2018 at 7:57 am
At the risk of clutching at straws, how about:-
1) Back up databases on A
2) Restore database on B
3) Set up replication from B to C & D
4) Backup database on B (which will (hopefully) include all the replication items) and restore into standby-mode
4) Set up log-shipping from A to B (which hopefully won't need a new database backup/restore)
If that won't work, I'm stumped.
I just need a method of of moving data from A to B (replication is "banned" for want of a better word) and then replicating to C and D so we can add our own indexes, etc.
May 15, 2018 at 3:25 pm
richardmgreen1 - Monday, May 14, 2018 7:57 AMAt the risk of clutching at straws, how about:-
1) Back up databases on A
2) Restore database on B
3) Set up replication from B to C & D
4) Backup database on B (which will (hopefully) include all the replication items) and restore into standby-mode
4) Set up log-shipping from A to B (which hopefully won't need a new database backup/restore)If that won't work, I'm stumped.
I just need a method of of moving data from A to B (replication is "banned" for want of a better word) and then replicating to C and D so we can add our own indexes, etc.
I believe you can set it up that far. And then you are dealing with the management of all those which you'll need to provide good documentation on how it's managed.
All of the jobs will need to be coordinated in terms of both log shipping and replication with consideration for things such as all processes need to be kicked out of the database for the log shipping logs to be applied. That would include the replication log reader. The times it takes to replicate new data or apply logs will vary so you need to take that into account. I don't know what the vendor does that won't allow replication but most likely truncates tables. It would be good having an idea of what is really being done and what volume of data is affected since that will impact the log shipping.
And don't forget to plan the processes for if/when log shipping or replication has issues - things like applying logs getting too far behind to catch up, replication out of sync, etc. Any of those in and of themselves really aren't too difficult but with the different processes in place, you'd want to understand all of those and the jobs pretty well so you can handle those. You really, really need a test environment to work on this.
Sue
May 16, 2018 at 2:17 am
Thanks Sue
We'll be testing this on a couple of small(ish) test databases to start with (39GB and 23GB).
Not a lot is used on these (and if it goes completely pear-shaped it won't be an issue).
They are currently mirrored to another server for a different process and I'm hoping I won't need to switch that off.
We'll be using transactional replication to go B to C&D and no-one will be querying the databases on B at all (there are very few users on B anyway).
We can then stress-test the two test databases on A to get an idea of the volume of data that will be being passed around, with the added bonus of being to configure the timings for everything.
The vendor uses something weird called DR Manager (it's a home-grown transfer mechanism) to get the data into our main databases and it basically fails it if detects replication is enabled (even if it's just a normal feed and not reloading any of the tables).
I'd be looking (to start with) at doing the log shipping every 15 minutes (should keep the logs nice and small and easy to process).
May 16, 2018 at 6:42 am
richardmgreen1 - Wednesday, May 16, 2018 2:17 AMThanks SueWe'll be testing this on a couple of small(ish) test databases to start with (39GB and 23GB).
Not a lot is used on these (and if it goes completely pear-shaped it won't be an issue).They are currently mirrored to another server for a different process and I'm hoping I won't need to switch that off.
We'll be using transactional replication to go B to C&D and no-one will be querying the databases on B at all (there are very few users on B anyway).
We can then stress-test the two test databases on A to get an idea of the volume of data that will be being passed around, with the added bonus of being to configure the timings for everything.
The vendor uses something weird called DR Manager (it's a home-grown transfer mechanism) to get the data into our main databases and it basically fails it if detects replication is enabled (even if it's just a normal feed and not reloading any of the tables).
I'd be looking (to start with) at doing the log shipping every 15 minutes (should keep the logs nice and small and easy to process).
If the logs are applied every 15 mins, you will need to manage the log reader around this and will need to modify the jobs. Hopefully you are familiar with the jobs for both log shipping and the replication agents.
Sue
May 16, 2018 at 6:54 am
I've never tried log shipping before so that will be a bit of learning curve (not too steep from the research I've done).
With replication, I tend to take the defaults but I can change those as needed.
May 16, 2018 at 7:14 am
richardmgreen1 - Wednesday, May 16, 2018 6:54 AMI've never tried log shipping before so that will be a bit of learning curve (not too steep from the research I've done).
With replication, I tend to take the defaults but I can change those as needed.
You can't take the defaults for replication. The log reader agent will run continuously. You can't apply any logs from log shipping when the log reader agent is running as it will not be able to get exclusive access to the database. It's not just changing the schedules - you need to make sure the log reader isn't running before applying the logs and then it needs to be running when the logs aren't being applied. None of it will work if you take defaults for everything. And you will need to customize the jobs to some extent.
Sue
May 16, 2018 at 7:19 am
Hi Sue
Apologies, badly written response to your last post.
What I meant to say was......
Under normal circumstances, I would take the defaults for replication. In this case, I'll have a play with the schedules (for both log-shipping and replication) and see what I can work out. I think a useful first step will be to check how long log-shipping takes to completely restore any given log and then extend that time a little to allow for discrepancies/larger than expected logs.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply