Sql Server Source Control
This small project intends to help with databases which isn't using source control. Sometimes its hard to implement a database project or to change the way we develop to enable the usage of SCM. This project allows us to develop the way we want to and still leave the possibility to find changes in SCM. The tool is intended to be as little intrusive as possible and require zero attention from the developer after initialization.
How it works
Theres a couple core concepts used.
- The changes on database are picked up by a database trigger which saves the modification metadata to table
- Small C# service polls the database table at set interval and pulls the changes as .sql files and deletes polled changes from the table
- Git picks up the changes and commits them to local repository and pushes them to remote
How to setup
- Admin rights on the machine you're installing the service for
- VSTS account and a project with empty repository
- Probably works with other services also as long as your credentials can push to remote repository
Setup files are located in Setup folder.
Start by setting up the database.
Run InitializeDb.sql and InitTrigger.sql.
These scripts setup the log table, an user and password for your database which the service uses to poll the log table and the trigger to get changes. This user gets rights to view and delete data on the log table and to view definitions on all objects to allow scripting.
Now you can build the service by running these commands in project root:
- dotnet restore
- dotnet publish --configuration Release
After setupping these run Init.ps1 to install the service. The powershell script asks for a couple of parameters which include:
- Name for the service
- Interval to poll log-table (ms, using something between 1000-10000 is advised)
- Sql Server instance name
- Database name where the log table was created
- Local repository path (will be automatically created)
- Url for remote repository
- Credentials for remote repository (will be saved in credential manager)
- Credentials for the Sql Server instance
- Password for current user to run the service as current user (for credential manager access)
- Setup might be hard
- Schemas needs to be added to object names
- If there's been multiple changes while the script is not running, only the latest one will be picked up
- Table modify events are weird when modified through designer