What is the best source control for SQL Server?

Question

We have TFS source control for Visual Studio and it works very well. Now we want to have source control for SQL Server. SQL Server in our project is not local and it is under network and we must connect to it with IP. RedGate source control and other source controls work with database folder but we do not have any file or folder from a database. Which source control is suitable for our work?

Answer 1

All source control systems manage source code as files, including database source code. So the first step in getting a database under source control is to create files (e.g. SQL scripts) for your database from the remote SQL Server. This can be done by reverse engineering your existing physical database as SQL scripts or using integrated tools like Red-Gate SQL Source Control, Red-Gate SQL Compare, ApexSQL Source Control, Red-Gate ReadyRoll for Visual Studio, Visual Studio SQL Server Data Tools (SSDT) Schema Compare, etc. Then check the files into your source control system.

Recommendations of a specific tool are beyond the scope of this forum but source control aware database tools generally connect to a local or remote SQL Server by server name or IP address, and compare the database schema of the live database with objects under source control. Schema differences can be either applied to the source controlled database objects, promoted to the database, or a deployment/migration script generated. Exactly how you might use database tooling with source control depends on your development process. There are multiple approaches, each having merit along with pros and cons.

I suggest you experiment with the tool(s) and development process of your choice to gain familiarity with both the process and associated tools for source control integration.