Has anyone ever though of pushing a SQL Server database backup out remotely? (SQL Native andor RedGate Backups) My idea came from having to schedule SQL Server Agent jobs on the target servers to run a RedGate restore command to refresh our Development, QA and CI environments with a cleansed RedGate backup from our Production environment. This became a chore as after each restore I had to apply loginsusers and permissions which were different on each environment and apply in-flight patches which were also different on each environment as each server is at a different development stage respectivly.
So I came up with the idea to push the database backups out remotely having a single SQL Server Agent job per environment configured on the source server. The steps that the job must do:-
- Restrict Access
- Restore the database
- Setup logins
- Setup users
- Setup user roles
- Grant access
- Run release scripts
- Archive events after 30days
Knowing the process I began to write the schema. I identified that I needed to store some static data for this to work correctly. I identified I needed to store:-
- Restore specific configuration table restore.Configuration
- Database Instance configuration table infrastructure.DatabaseInstance
- Database configuration table infrastructure.Database
- Database Logins table permissions.Login
- Database Users table permissions.User
- Database User Permissions table permissions.UserPermission
- Database User Role Membership table permissions.UserRoleMembership
- Some type lookups tables permissions.DatabaseRole and permissions.PermissionType
See this database diagram :-
I also I wanted to log every event and sql that the SQL CLR executed, as I know that it will be very hard to debug the system once the solution goes live. Also I know that some DBAs have issues with SQL CLR’s as they can’t see what is going on inside them, just like they can with regular SQL stored procedures. So if things went wrong, which they usually do! they couldn’t see what it was doing and knowing that, they probably wouldn’t accept it into live. So decided to:-
- Create logging.Event to store every event in the system.
- Create logging.EventLog - the log.
- Create logging.EventType – the log types (Error, Information, Success, Warning)
- Create logging.EventLog_Archive - the archived log
See this database diagram :-
Right, once the the schema was completed, I started to develop the SQL CLRs that will process the events. All development was carried out following strict Test-Driven Development guidelines using Visual Studio 2012 integrated with SSDT (For a download of the source code see the end of this article). The SQL CLRs written in C# are compatible with SQL Server Versions 2008+, however the MS.Net framework (used for the SQL CLRs) is only compatible with versions 3.5 (for now!).
To deploy database, either :-
- Follow this guide to publish the dacpac with SqlPackage.exe
- Run the deployment script attached in sqlcmd mode. (DBRestore.publish.sql).
- Opening the solution with Visual Studio 2012 integrated with SSDT and publish from there.
Once deployed we need to add the configuration. I have provided template scripts under folder source / Database / Scripts. Complete the SqlCmdVariables in all the scripts and execute in sqlcmd mode, in order against the source restore server.
The source code is available here on Git at bitbucket.org
