This article is part of the SQL Server Tools series, aimed at giving you an idea of the available tools and techniques to build applications using SQL Server tools. The first article of the series covers the rudimentary DevOps concepts and discusses on a high level, the overview of SQL Server tools that can be piped for DevOps operations.
In this article, I will explore various SQL tools. I will be discussing in detail about each of the following tools in the upcoming articles:
- Sqlcmd
- Bcp
- SqlPackage
- MSSQL-Scripter
- Azure Data Studio
For the first installment of this new series I will give you an overview of DevOps and highlight how SQL Server tools helps.
The recent trends under the umbrella of DevOps (Development and Operations) led to the birth of various new techniques to fulfill the complexities of today’s needs through Continuous Integration (CI) and Continuous Deployment (CD) over a wide range of diverse enterprise applications. DevOps is a combination of two teams — development as well as operations — and it’s a whole new value stream for delivery. There is a direct correlation between automation of software delivery processes and success with customers.
In today’s world, various technologies and complex applications, a myriad of database systems, and several underlying data virtualization platforms require continuous effort in adopting a well-defined process of release management. Having the right tools and practices in place enables you to make the most of your DevOps automation process.
DevOps is the probably the most popular solution which has now become a cultural shift on how the Development and Operations teams can work together in order to meet the paradigm shift. DevOps is one of the most viable options to improve the Time to Market and cope with a large number of release cycles. Cross-coupling development and operations with agile processes or methods simplify the process of Continuous Integration and Continuous Deployment. It increases the efficiency and reduces (and sometimes eliminates) the risk factors involved in the process.
In a nutshell, DevOps is the process by which you deliver value to your client or your customers by ensuring that you continuously collaborate with all the connected teams, extending the potential of being agile. In other words, it is a process by which you meet, decide what you want, develop the application, build it, test it, receive feedback, decide on what expectations you want to meet, develop the capability, build it, test it, deploy it, then receive feedback
This process improves the value delivered by decreasing the cycle time, finding out potential issues and fixing them early. Databases are an integral part of most enterprise-grade applications. And ensuring that our databases are DevOps capable is an important aspect of delivering a good product. The one thing that we have learnt is that going cross-platform is very important in order to make DevOps a success — Microsoft realizes this as well.
In the market, already there are many cross-platform tools available to manage SQL Server databases, such as:
- SQL Server Management Studio (SSMS)
- SQL Server Data Tools (SSDT)
- PowerShell
- Sqlcmd
- Bcp
- Sqlpackage
- Mssql-conf
- Mssql-scripter
- DMV Tool
- Azure Data Studio (ADS)
- Visual Studio
Among above SQL tools, the following tools also fall into the DevOps Category — these tools have DevOps capabilities:
- Sqlcmd
- Bcp
- Sqlpackage
- Mssql-scripter
- Azure Data Studio
We’ll introduce ourselves to these tools in this article, and dive deeper into each of them in upcoming articles.
Sqlcmd
sqlcmd.exe is a powerful SQL Server command line utility for ad-hoc query execution. It’s an interactive tool which allows the user to enter T-SQL, call Stored Procedures, and execute script files in one of the following modes:
- Execute sqlcmd at the command prompt
- Use SQLCMD query editor in SSMS
- Integrate sqlcmd in dynamic SQL and execute it using SSMS
- As a Windows Batch script file
- In the SQL Server agent job step as an operating system command, cmd.exe
Sqlcmd is a now-cross-platform tool available for Linux, Windows and MacOS. It comes in handy for SQL administrators, developers and DevOps specialists in handling several SQL-related tasks. It provides us with an interface to run ad-hoc queries on multiple instances of the SQL Server, creating output for T-SQL statements or creating automation scripts for repetitive tasks. This utility can be leveraged to schedule database jobs like backup and restore operations through the use of T-SQL scripts. This will simplify database management in a DevOps pipeline.
BCP.EXE
This tool is used to copy data between SQL instances in a specified format or using a SQL query. It is also available on Linux, Windows and MacOS, since it is installed with SQLCMD. The BCP utility can be used to import large of rows into SQL Server tables or to export data out of tables into data files, which makes DevOps data movement operations seamless. The process can be automated through the command line and can handle schema changes using a format file.
SqlPackage.exe
The SQL Server Data Tools (SSDT) in-houses the SqlPackage, a cross-platform SQL tool for continuous integration and deployment. Continuous integration and deployment have become a platform for the modern day application development.
This tool is powerful enough to handle a development environment comprising of SQL Server, Azure SQL database and Azure SQL Data warehouse.
MSSQL-Scripter
The mssql-scripter is an open source cross-platform command line tool. This command-line tool is used for scripting SQL Server database objects and data. It’s very similar to the Generate Scripts Wizard in SSMS with a wide range of options.
The one of the major challenges faced nowadays in SDLC lifecycle is development and deployment. As the applications are deployed by moving various pieces of the SQL code between several versions, configurations, editions, and sometimes even different platforms, the process becomes a daunting task. I feel that scripting is the best way to address many of such scenarios.
Azure Data Studio
Microsoft Azure Data Studio is a new GUI-based lightweight tool for developing and managing your modern SQL databases. The tool resembles Microsoft’s lightweight code editor — the open source Visual Studio Code — in terms of functionalities and the UI in general. If you’ve used Visual Studio Code (or VS Code), you’d feel at home with Azure Data Studio. Azure Data Studio also integrates really well other products like Azure SQL Database and Data Warehouse. The Azure Data Studio is a cross-platform tool available for Linux, Windows, and Mac to manage the SQL Server databases. You can go ahead and download the Microsoft Azure Data Studio to try it out!
Summary
Microsoft has provided a platform and tools to manage modern data applications. The focus of this series are to help the DBAs to give the glimpse of currently available SQL Server DevOps tools.
- Sqlcmd – Overview of SQLCMD utility in SQL Server
- Bcp – BCP (Bulk Copy Program) in Action
- SqlPackage – Continuous Deployment using SQL Server Tools SqlPackage.exe
- MSSQL-Scripter – SQL Server cross-platform MSSQL-Scripter scripting Tool
- Azure Data Studio – Getting started with Azure Data Studio (ADS)
Table of contents
My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.
View all posts by Prashanth Jayaram
- Stairway to SQL essentials - April 7, 2021
- A quick overview of database audit in SQL - January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021