DevOps

Prashanth Jayaram

All about MSSQL-Scripter, the SQL Server cross-platform scripting Tool

August 14, 2018 by

One of the major challenges we face today, in the software development lifecycle, is with respect to development and deployment. As applications are deployed by moving various pieces of the SQL code between several versions, configuration, different editions, and sometimes even different platforms, deployment becomes daunting. In my opinion, scripting helps a lot and can really “grease the wheels” with addressing many scenarios that involve such complexity.

The importance of database level scripting is high, but it is getting easier by the day. Scripting is a process of generating a text file that contains the data structures and data from the database objects.

So, do we have a SQL native tool which serves the various purposes and usage? In some cases, the answer’s a yes, and in others, it’s a no.

Read more »
Prashanth Jayaram

Continuous Database Delivery (CD) using SQL Server Tools SqlPackage.exe

August 14, 2018 by

A little curiosity on my part led me to research a little further on the available pieces of SQL Server Tools. A cross-platform tool for continuous integration and deployment, called SqlPackage, is part of SQL Server Data Tools. I picked it up because continuous integration, delivery and deployment are now must-have in the modern-day application development paradigm.

We’d talk about the following in this article:

  1. The Database Management Life Cycle
  2. Complexities of the database release management process
  3. Introduction to SQL Server Data Tools and SqlPackage
  4. Using SqlPackage.exe, with examples
Read more »
Prashanth Jayaram

The BCP (Bulk Copy Program) command in action

August 13, 2018 by

There are various methods available for bulk data operations.

  1. BCP utility
  2. BULK INSERT
  3. Using OPENROWSET
  4. Import/Export wizard

The BCP (Bulk Copy Program) utility is a command line that program that bulk-copies data between a SQL instance and a data file using a special format file. The BCP utility can be used to import large numbers of rows into SQL Server or export SQL Server data into files. The BCP data files don’t include any schema details or format information. Hence, it is recommended to create a format file to record the data format so in case of any failures, you can refer to the format file and better understand the data format to determine what may have gone wrong..

We’ve been using the BCP tool for a long time, the reason being that it has a very low overhead, and works great for bulk exporting and importing of data. It is one of the most efficient ways to handle bulk import and export of data.

Read more »
Prashanth Jayaram

Overview of the SQLCMD utility in SQL Server

August 13, 2018 by

This article is aimed at helping you understand the sqlcmd utility. Of course, this is a tool most of us have used it at several occasions given that the tool has been around for a decade now. However, to be a solid starting point, this article contains some examples that help you understand the tool from different viewpoints.

This article discusses the following:

  1. How to enable SQLCMD
  2. A few important points to remember
  3. SQLCMD with scripting variables in SSMS
  4. Passing variables (or argument) as T-SQL and or a SQL Script file
  5. SQLCMD with scripting variables and Windows scripting in SSMS
Read more »
Prashanth Jayaram

Getting started building applications using SQL Server DevOps Tools

March 28, 2018 by

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.

Read more »
Nemanja Popovic

SQL Server database continuous integration (CI) Best practices and how to implement them – Testing, processing and automation

January 31, 2017 by

Testing

Test databases should be processed with unit tests

In many shops code is unit tested at the point of commit. For databases, I prefer running all unit tests at once and in sequence against a QA database, vs development, as part of a Test step, in my continuous integration workflow pipeline. Yes, issues would be caught later than at check-in, but continuous integration largely solves this with frequent iterations, including at a commit itself. So the difference between on-check in, unit testing and unit testing a build created on-commit, is simply that the unit tests will be run against a fully re-constituted QA database, vs Development. The previous article in this series is SQL Server database continuous integration (CI) Best practices and how to implement them – Source control. Read more »
Nemanja Popovic

SQL Server database continuous integration (CI) Best practices and how to implement them – Source control

January 31, 2017 by

This article provides for a roadmap to continuous integration and delivery best practices, and along the way demonstrates how to apply these with ApexSQL tools and technologies. In some sections this article is aspirational, as no solution yet exists, but demonstrates our plan, direction and roadmap. As the tools that apply these best practices are released this article will be updated accordingly.

Read more »
Marko Radakovic

Understanding SQL Server database static data and how it fits into Database lifecycle management

January 13, 2017 by

What is static data

Static data (aka Code, Lookup, List or Reference data), in the context of a relational database management system, is generally data that represents fixed data, that generally doesn’t change, or if it does, changes infrequently, such as abbreviations for US states for example e.g. ME, MA, NC. This data is typically referenced, extensively, by transactional type data. For example, an customer table would have references to static table for City name, State or province, Country, Payment terms e.g. NET 30 etc.

Read more »
Paul Stanton

Automate Delivery of SQL Server Production Data Environments Using Containers

January 12, 2017 by

There has been a lot of buzz about Docker and containers this year, and earlier this Fall Microsoft released container support in Windows Server 2016. WinDocks is a team of former Microsoft engineers, and we released an independent port of Docker’s open source in March of 2016. Full disclosure, I am a co-founder at WinDocks.

In this article we’ll take a look at the basics of containers, and the most popular use by SQL Server DBAs.

Read more »
Marko Radakovic

Introduction to SQL Server database continuous integration

January 5, 2016 by

What is SQL database CI?

Continuous integration (CI), in the context of databases, refers to the practice of isolated database changes to be integrated, as soon as they are made and pushed to a source control repository. In the early days of CI, daily integration was a rule of thumb to follow. However, today, it is more common, than not, that database changes are integrated several times a day. Each change needs to pass an established testing plan that executes automatically on detected changes committed to the repository and if everything is ok, changes will be automatically merged using the build script. with the remaining code, into a new build that will be pushed to other environments (i.e. production, QA).

Read more »
Régis Baccaro

Continuous Deployment using SQL Server Data Tools and Visual Studio Online

February 2, 2015 by

In the previous posts

Deployment to several databases using SQL Server Data Tools and TFS using a Custom Workflow file
Deployment to several databases using SQL Server Data Tools and Team foundation Server
Continuous integration with SQL Server Data Tools and Team Foundation Server

I have been mostly writing about the interaction between SQL Server Data Tools and Team Foundation Server. Microsoft provides a hosted version of Team Foundation Build Service called Visual Studio Online. The configuration and functionality is mostly the same than what I have previously been writing about but there are some specifics things that we need to be aware when using Visual Studio Online Build Service.

Read more »
Régis Baccaro

Deployment to several databases using SQL Server Data Tools and TFS using a Custom Workflow file

January 16, 2015 by

In the previous blog post : Deployment to several databases using SQL Server Data Tools and Team foundation Server I illustrated how it is possible to use TFS and a batch file to deploy a database to several SQL Server instances or to deploy several SQL Server databases to several instances. The main way to achieve that in the previous post was using a batch file. For more information about this technique please have a look at that blog post.

In this post on the other hand I will demonstrate how the same functionality can be achieved using a Windows Workflow Foundation file (xaml) deployment file and Team Foundation Server.

Read more »