SQL Database development

Vitor Montalvão

How to use the SQL Server Database Experimentation Assistant (DEA) tool

February 24, 2017 by

Introduction

This is my second article about Database Experimentation Assistant (DEA). Please refer to my previous article to see how to download and install DEA.

The Database Experimentation Assistant is the new A/B testing solution for SQL Server upgrades. It enables customers to gather performance insights for upgrades by customers to conduct experiments on production database workloads across two versions of SQL Server.

Read more »
Paul Stanton

How to use Windows hosted file shares to support SQL Server containers

February 23, 2017 by

In a previous article, Automate Delivery of SQL Server Production Data Environments Using Containers, we introduced SQL Server containers for delivery of production data environments to development and QA teams. In this article we look at the methods used for working with SQL Server data, and use of file shares to support delivery of production databases with containers.

Read more »
Aamir Syed

When to Use Temporary Tables vs. Table Variables

February 21, 2017 by

It is very beneficial to store data in temporary tables rather than manipulate or work with permanent tables. Let’s say you want full DDL or DML access to a table, but don’t have it. You can use your existing read access to pull the data into a temporary table and make adjustments from there. Or you don’t have permissions to create a table in the existing database, you can create a temporary table that you can manipulate. Finally, you might be in a situation where you need the data to be visible only in the current session.

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 »
Daniel Calbimonte

How to generate random SQL Server test data using T-SQL

January 26, 2017 by

Introduction

In this article, we will talk about generating random values for testing purposes.

I once had a customer with software that worked fine in the demo with 30 rows, but after some months, the software had more than a million rows and it became very slow. The problem was not SQL Server, the problem was the application, which was not designed for tables with millions of rows. The customer sued to the software provider and lawyers were needed to create a resolution. If the provider had tested the software with millions of rows, this problem would have never happened.

Read more »
Daniel Calbimonte

Are SQL Server database triggers evil?

January 25, 2017 by

Introduction

There is a lot of talk about how bad triggers are, how you should never use them, etc. I wanted to spend some time reviewing fact vs fiction and do an objective analysis of SQL Server database triggers (both DDL and DML), warts and all. We will review alternatives and compare them with triggers to determine advantages vs disadvantages of each approach.

Read more »

Best Practices for Configuring Newly Installed SQL Server Instances

January 20, 2017 by

Often we install SQL Server on clients or we get to clients where they have pre-installed SQL Server Instances. As a DBA we have the Primary responsibility of keeping the Database Up and Running. This responsibility leads to optimizing, performance tuning and many other stuff related to database’s internal objects. While keeping the other aspects in mind many of us have to go through the configuration of the newly installed or pre-installed but un-configured instances. Configuring SQL Server newly instances are not an easy task and a DBA might want to configure an Instance in many ways.

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 »
Vitor Montalvão

How to download and install the SQL Server Database Experimentation Assistant (DEA)

January 12, 2017 by

Introduction

The Database Experimentation Assistant (DEA) is a new Experimental solution provided by Microsoft used to help upgrading databases from older SQL Server versions (MSSQL 2005 or higher versions) to the more recent ones (MSSQL 2012 or higher versions). It accomplished this based in a given workload that is previously captured from the source database and later replayed on the target SQL Server instances for comparison.

Read more »

Microsoft SQL Server Non-Contained Object Migration Deployment Procedure using Powershell

January 12, 2017 by

As DBAs, we all face a lot of challenges while performing the migration of SQL Server databases from one server to another or even the whole servers at some times. The Database Migration process is not always a simple Backup and Restore process so we might need a huge amount of effort if we have to migrate objects which are not included in the native backups for a specified database, these objects are called Non-Contained Objects.

Read more »
Shawn Melton
C:\Users\wshaw\AppData\Local\Temp\SNAGHTML519cfb8.PNG

Introduction of Visual Studio Code for DBAs

December 29, 2016 by

Introduction

Visual Studio Code (Code), have you heard of this product yet? This is an open-source, cross-platform and extremely light weight code editor from Microsoft. You may see some folks explain this editor as the little brother to Visual Studio Community Edition (VS Community), but it is more compared to editors like Atom, Sublime Text or even Notepad++. It is not something you can use to compile program code, so it is for the less complex coding projects. I utilize Code as my editor of choice now with PowerShell, and even T-SQL at times. In this article, I wanted to walk you through using Code and note some specific extensions I use for PowerShell and SQL Server.

Read more »
Kimberly Killian

How to Split a Comma Separated Value (CSV) file into SQL Server Columns

December 29, 2016 by

Receiving a comma delimited file is not new technology nor is it difficult to deal with in SQL Server. As a matter of fact, it is extremely easy. There are many cases as to why you would want to do this. For example, you have an external data source that needs to be imported into your database/table. There a couple ways to do this, however the quickest and easiest way is to use the native “import” feature within SQL Server Management Studio and you can even save it to an SSIS Package at the end of the process. The end result of using this method is that the external CSV file is loaded into a SQL Server table where columns are created and rows are populated.

Read more »
Page 1 of 41234