August 27, 2018 at 12:52 pm
Is there a tool out there that will help prep for a RECOVERY scenario (outside of the system and application DB backups) ... Looking for a tool which will script out LOGINS, JOBS, Replication, Proxies, Operators, etc.
We have over 200 SQL Server instances and a tool to automate the generation of the scripts would be very helpful. And we could run it daily.
August 27, 2018 at 6:56 pm
Express12 - Monday, August 27, 2018 12:52 PMIs there a tool out there that will help prep for a RECOVERY scenario (outside of the system and application DB backups) ... Looking for a tool which will script out LOGINS, JOBS, Replication, Proxies, Operators, etc.
We have over 200 SQL Server instances and a tool to automate the generation of the scripts would be very helpful. And we could run it daily.
I guess my question would be, what's wrong with backups of the system databases?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 28, 2018 at 5:49 am
Jeff - thanks for your reply. Question - in a D/R recovery scenario, once I restore all my system DB's & application DB's (including SSISDB and Distribution DB) onto my new target D/R Server --- will all of my Objects be available on the new SQL Server?
Including: Logins, custom 'Server Roles', Credentials, Proxies, Endpoints, Linked Servers, Triggers, Replication definitions for PUBs and SUBs, AlwaysOn objects, Jobs, Alerts, Operators, etc??
(Note: I realize I may have to rename the new target D/R SQL Server back to the original name)
August 28, 2018 at 7:03 am
If you restore master, you get logins, linked servers, config settings (These need to be checked if different hardware).
If you restore msdb , you get jobs, operators, schedules, all Agent and backup stuff.
That being said, if you are looking to move things to a warm server, check out dbatools.io for PoSh cmdlets that will move items over. You can schedule this regularly to ensure things are up to date.
If you are worried about DR for lots of servers, but don't have duplicates for each of them, be sure you understand how to restore:
- https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-and-restore-of-system-databases-sql-server?view=sql-server-2017
- https://docs.microsoft.com/en-us/sql/relational-databases/replication/administration/back-up-and-restore-replicated-databases?view=sql-server-2017
August 28, 2018 at 7:49 am
So, based on your feedback -- w/ master and msdb restored to the same-named, remote server name (assuming the original server was destroyed). we'd get the following objects restored:
from master: Logins, Linked Servers
from model: Jobs, Alerts, Operators
still outstanding: custom 'Server Roles', Credentials, Proxies, Endpoints, Triggers, Replication definitions for PUBs and SUBs
for D/R re-creation -- Do I need to manually script these outstanding objects from each of my 200+ SQL Servers? (yes, we actually have over 270 SQL Servers)
(note: AlwaysOn objects are exclusionary as this setup would imply a remote server w/ all the definitions already in place)
August 28, 2018 at 8:18 am
Sorry, I didn't list everything. You should verify all of this in BOL or checking as well, rather than just assuming myself or someone else covers this.
Server roles exist in masterCREATE SERVER ROLE myrole
SELECT
*
FROM sys.server_principals AS sp
Credentials and endpoints, as well as DDL triggers are in master as well. Proxies will be in master or msdb, depending on if this is for agents or the server, like xp_cmdshell.
Replication stuff tends to be stored in the replicated database, so assuming you restore these, you should be ok. Renaming the publisher and distributor back to original names will solve your network resolution issues. Replication jobs are in msdb
For D/R, you can script these, but as mentioned, these are in master or msdb. Restoring those is slighty tricky, so you need to practice this and ensure you can do it. If you need a real DR test, then you need an isolated network, as you'll be renaming systems to existing system names.
Whether you do this for one server or 270 is irrelevant. Your process ought to be how to script this for one server, and how to restore this server. Get that working, and use a path on a share (with a folder for the system) that is the target of scripting. Once that's working, you deploy this to every instance and each instance scripts itself. For DR, you need to have this regularly run, as things will change in scripts. That's not a bad solution to put in place, especially as backups can fail or get corrupted, and likely the situation will occur just before you need to recover.
I would ensure I have both backups of master/msdb (ssisdb, dist db,. etc) as well as scripts for the setup.
August 28, 2018 at 9:01 am
excellent feedback Steve. fyi, I've deployed Ola's backup, IndexOptimize and Integrity Check solution on every server so we're good w/ the system & application DB backups (FULLs, DIFFs and T-logs) and also the SSISDB and Distribution backups.
I was reading up on the Replication D/R stuff.. and see that yes, it can be a little tricky. This link is comprehensive and details a lot of HOW TO's: https://docs.microsoft.com/en-us/sql/relational-databases/replication/administration/strategies-for-backing-up-and-restoring-snapshot-and-transactional-replication?view=sql-server-2017
I'm going to request a VM here ... then Restore everything to that for a given DEV server. (understanding it's server name & IP address will be different for this test)... And take a careful look at WHAT objects are restored.
thx again for your feedback. Very helpful.
August 28, 2018 at 11:36 am
You are welcome. Again, even in a VM, you might try isolating it from the network once you've gotten the backups restored, before you restart. Then you can practice renaming.
If you need to check repl, get 2 vms and have the network people isolate them on the same, but separate network.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply