Building a streamlined and automated database development cycle is no mean feat. Come to think of it—you need to find a slew of mutually compatible tools that address database design, development, testing, deployment, and administration. Not only do these tools have to work seamlessly together, but they also must facilitate easy teamwork under your corporate standards and deliver means of automation for routine manual operations that would otherwise unnecessarily take too much time.
It all may look quite easy and obvious on paper, but you won’t know how hard it can be until you actually try. This is especially true if you have already adopted certain tools and now you need to expand and switch to something more advanced.
Let’s take SQL Server, for instance. The vendor of this database system, Microsoft, provides its users with a must-have free IDE—SQL Server Management Studio—which is simply indispensable for non-demanding individuals but quite insufficient if we raise the stakes and talk about database development on an enterprise level. It just lacks enough firepower. However, the standard incarnation of SSMS can be greatly expanded with third-party add-ins sophisticated enough to level up your entire database development process with new features and automation capabilities.
That said, let’s see how we can build an automated and standardized SQL Server development cycle around SSMS with nothing but a handy bundle of auxiliary tools and add-ins by our side.
Contents
Automated database development cycle
The bundle in question is called dbForge SQL Tools, and it completely reinvents the basic SSMS with 15 versatile tools. Taken together, they form a clockwork mechanism that sets an automated and standardized cycle of database development in motion. Let’s take a closer look.
Development
Every developer that works on a project commits their SQL code to a shared repository that is further synchronized with the central code base.
Continuous Integration
Continuous Integration is a development practice that implies that every change introduced to the code gets tested. This allows detecting and eliminating bugs early on.
Continuous Delivery
Continuous Delivery facilitates well-polished regular releases, which not only contributes to smooth experience but also enables faster response to feedback and urgent updates in case of need.
Operations
Finally, ongoing monitoring, maintenance, and reporting ensure timely detection, analysis, and elimination of any performance-related issues.
The tools that make it happen
Now, let’s break down this process tool by tool and see how each one contributes to it in detail.
Write clean and standardized SQL code
SQL Complete is an SSMS add-in designed to at least double the user’s regular coding speed without compromising the quality of the output. The features that make it possible include context-aware code completion, relevant object suggestions, code snippets, syntax validation, formatting (that brings code written by multiple developers to unified standards), refactoring, and debugging.
Apply version control for effective teamwork
Source Control allows linking databases to the most popular version control systems, including Git, GitHub, GitLab, Azure DevOps, Mercurial, TFVC, SVN, Perforce, Plastic SCM, and SourceGear Vault. With its help, multiple developers can seamlessly collaborate on the same database.
Compare and synchronize database schemas
Schema Compare delivers the easiest way of detecting and viewing differences between source and target database structures. Afterward, users can synchronize database structures via a generated script. Regular synchronization can be easily scheduled and automated.
Compare and synchronize table data
Data Compare does just about the same in regard to table data. All the differences are conveniently shown and can just as well be synced in a matter of moments. Automation works just as well here.
Import and export data using 14 most popular formats
Data Pump makes data migration as easy as ever, offering import and export to HTML, TXT, XLS, XLSX, MDB, RTF, PDF, JSON, XML, CSV, SQL, ODBC, DBF, and Google Sheets. Moreover, the process is carried out via a wizard with a rich selection of flexible settings tailored to each specific format.
Build queries of any complexity on diagrams
Query Builder is a perfect solution for those who prefer visual query building over SQL coding. Actually, users don’t have to write a single line of code to get their queries ready. They only need to draw a query on a diagram by visualizing relationships between the required objects.
Automate unit testing
Unit Test is another handy add-in that helps write unit tests in T-SQL directly in SSMS and run them against the required databases at any moment. Support for the command line allows automating the execution of these tests.
Generate column-intelligent test data
Data Generator is an invaluable tool that allows avoiding the use of real-world data for testing. Instead, it emulates meaningful data and generates any amount of it that you might need. Finally, it easily populates the required databases with the newly generated data.
Retrieve full database documentation
Documenter provides users with comprehensive documentation that contains extensive information on database structures, object types and properties, as well as inter-object dependencies.
Find database objects and table data in no time
Search is a free add-in that helps quickly locate and view the required database objects, data, and text in SQL Server databases.
Track server performance in real time
Monitor is a tool that allows users to keep an eye on server performance, instantly know the status of any server and/or database, locate and optimize slow queries, as well as diagnose and address performance-related issues in the fastest way.
Collect and analyze trace data
Event Profiler is another free add-in that allows capturing and analyzing all possible types of SQL Server events and detecting queries that impact server productivity the most.
Analyze and fix index fragmentation issues
Index Manager is a tool that allows users to quickly collect index fragmentation statistics and detect databases that require maintenance. Users can instantly rebuild and reorganize indexes in visual mode and generate SQL scripts for future use.
Decrypt procedures, functions, triggers, and views
SQL Decryptor is a free add-in that performs decryption on encrypted SQL Server objects. This includes stored procedures, views, functions, and triggers that were created with the WITH ENCRYPTION option.
Build a robust CLI-powered DevOps cycle
DevOps Automation is a solution that allows automating a number of database-related operations from the command line. It is compatible with CI/CD solutions such as Jenkins, TeamCity, Bamboo, and Azure DevOps. With the integration of all the abovementioned dbForge tools, it forms a powerful toolchain that expertly addresses every stage of database development.
Conclusion
Following our try-before-you-buy principle, we gladly invite you to download SQL Tools for a free 30-day trial, which is a nice way to see all that we have described in action and see how your particular development processes can benefit from the adoption of these tools.
