How to copy tables from one database to another in SQL Server

April 29, 2016 by Ahmad Yaseen

In some cases, as a DBA, you are requested to copy the schema and the content of specific tables from a database to another one in the same instance or in a different SQL instance, such as copying specific tables from a production database to a DEV one for testing or troubleshooting purposes.

SQL Server offers a lot of methods that can be used to perform table’s data and schema copy process. In order to go through each one of these methods, we will consider the below scenario:

The hosting SQL Server: localhost.
Both databases hosted in the same SQL Server 2017 instance
The source database: AdventureWorks2018
The destination database: SQLShackDemo

SQL snippets in SQL Server Management Studio

April 22, 2016 by Marko Zivkovic

Snippets are a great productivity feature to speed up typing of repetitive T-SQL. Snippets were introduced in SQL Server 2012 as part of SQL Server Management Studio (SSMS).

Snippets are XML templates with predefined fields and values that render into T-SQL, when processed by the query window.

Meaning of the 5127 database snapshot error code

April 15, 2016 by Sifiso Ndlovu

Long before I turned to Data Warehousing and OLTP replica environments such as Operational Data Stores as a form of data redundancy strategies, I had been using database snapshots. During that time, the analytics and reporting teams were directed towards a database snapshot as a data source for their development. Another benefit of having a database snapshot, is the ease of knowing that should unintended changes be committed in source, you could easily revert the operation by restoring source database off the snapshot.

The new elastic databases in Azure

April 12, 2016 by Daniel Calbimonte

Introduction

The elastic database is a new feature offered for SQL Azure Databases. Elastic databases are useful when you have several databases and you want to monitor and manage all the databases at the same time. To do this, a pool is required. The pool allows handling several databases at the same time.

In this chapter, we will learn how to create the pool and add SQL Azure Databases to it.

SQL database continuous integration with Team City

April 12, 2016 by Marko Radakovic

Team City is a Java based continuous integration and deployment server shipped as commercial software but free to use for up to 20 build configurations. It polls the source control repository for any changes providing build reports and statistics to the team.

SSIS and PowerShell – Execute process task

April 11, 2016 by Shawn Melton

Introduction

SQL Server Integration Services (SSIS) and PowerShell (PS) together offer a plethora of opportunities, and some shortcuts when having to import, export, or at times moving data. I have come across packages that contain a Script Task with lines and lines of C# code that, done with PowerShell, could make maintaining that package much easier. Overall, the most common thing I see Script Task doing is accessing the file system or doing some manipulation on a file. One thing I hope that picks up speed in the BI world of SSIS is utilizing PowerShell for these type of actions. This is not to say one is better than the other as you should pick what is best in your eyes, but when I can do operations against the file system with a one-liner in PowerShell it is just easier to maintain that in the package. In this article I will go over how you can use the most common task utilized for executing PowerShell code in an SSIS package: Execute Process Task.

Create Azure Data Warehouse reports using Azure Power BI

April 11, 2016 by Daniel Calbimonte

Introduction

Power BI is an extremely powerful tool to create dashboards and reports. In the last article, we learned how to create a Data Warehouse in Azure. In this new chapter, we will learn how to create reports from the Data Warehouse in Azure using Power BI in Azure.

How to take advantage of the SQL Server’s transaction log?

April 11, 2016 by Jefferson Elias

Introduction

SQL Server keeps track of all database modifications and every database transaction. This is done in a file called the transaction log or TLOG. This transaction log is particular to a SQL Server database and there is, at least, one transaction log per SQL Server database.

As explained on MSDN, the transaction log is a critical component of the database and, if there is a system failure, the transaction log might be required to bring your database back to a consistent state. The transaction log should never be deleted or moved unless you fully understand the ramifications of doing this.

The benefits, costs, and documentation of database constraints

April 7, 2016 by Ed Pollack

Constraints exist as a way to enforce or document rules within the database. How do they affect performance, and what are the benefits of using them?

Perform volume maintenance tasks security policy

April 4, 2016 by Shawn Melton

Introduction

You may see it more commonly referenced as Database Instance File Initialization (DIFI). If you are not familiar with the file initialization, this is the process SQL Server has to go through when it is creating the data files for a given database, and also during an expansion event (either manually or from auto growth) for a database. It only pertains to the data file(s) of the database, as log files are not affected by this security policy. SQL Server will “zero out” the file, basically fill it up with a bunch of zeros to allocate the amount of space requested. If you are a new DBA, this configuration actually goes all the way back to SQL Server 2005.

TFS tools for managing SQL Server development

March 29, 2016 by Sifiso Ndlovu

In today’s world of DevOps and ALM practices, there is often a strong emphasis on the visibility and traceability of the work we perform towards software development. This means that as developers, we often have to account (to clients, business owners, project teams etc.) for a list of development tasks that will be undertaken to deliver a given project requirement or user story. Amidst several tools that can be used to facilitate such a practice, Team Foundation Server (TFS) is one such tool that can be used by developers to keep track of their work items.

How to install and use Microsoft Baseline Security Analyzer (MBSA)

March 25, 2016 by Jefferson Elias

Introduction

Microsoft Baseline Security Analyzer is one of the tools provided by Microsoft to help administrators to scan systems (local and remote) for missing security updates and common security misconfigurations. It can scan the server operating system and SQL Server but also other products as well, such as Microsoft web server IIS.

Using SQL Server cursors – Advantages and disadvantages

March 23, 2016 by Sergiu Onet

Intro

In relational databases, operations are made on a set of rows. For example, a SELECT statement returns a set of rows which is called a result set. Sometimes the application logic needs to work with a row at a time rather than the entire result set at once. In T-SQL, one way of doing this is using a CURSOR.

SQL Server indexed views

March 17, 2016 by Ahmad Yaseen

SQL Server Views are virtual tables that are used to retrieve a set of data from one or more tables. The view’s data is not stored in the database, but the real retrieval of data is from the source tables. When you call the view, the source table’s definition is substituted in the main query and the execution will be like reading from these tables directly.

Managing untrusted foreign keys

March 17, 2016 by Jefferson Elias

Introduction

Intended audience

This article is intended for application developers and database administrators who plan to develop, deploy, and/or assess solutions for Microsoft SQL Server on a Microsoft Windows platform.

Working with ragged right formatted files in SSIS

March 16, 2016 by Sifiso Ndlovu

In the world of SSIS development architecture, preference should be given to extracting data from flat files instead of non-Microsoft relational databases. This is because you often don’t have to worry about driver support and compatibility issues in your SSIS development/server machine that is often attributed to non-Microsoft database vendors. In fact, I’ve been in several situations whereby we cannot upgrade to another version of SSIS (i.e. BIDS to SSDT) due to the lack of external vendor driver compatibility issues in the newer versions of SSIS.

Compare and synchronize images between two SQL Server databases

March 11, 2016 by Daniel Calbimonte

Introduction

Sometimes we lose data by mistake and we want to compare the data with older backups and synchronize the data.

There are several ways to do this. For example, you can create T-SQL scripts with some outer joins. Another option is to use the tablediff command line.

Searching SQL Server made easy – Building the perfect search script

March 9, 2016 by Ed Pollack

The need to search through database schema for specific words or phrases is commonplace for any DBA. The ability to do so quickly, accurately, and completely is critical when developing new features, modifying existing code, or cleaning up the vestiges from an application’s ancient history.

Searching SQL Server made easy – Searching catalog views

March 9, 2016 by Ed Pollack

The new SQL Data warehouse in Azure

March 8, 2016 by Daniel Calbimonte

Introduction

In previous chapters, we taught how to create SQL Databases in Azure. In this new chapter, we will show you the new SQL Data Warehouse Service in Azure.

Analyzing SQL Server backups

March 8, 2016 by Shawn Melton

Introduction

Database backups are important and always something you should have in any environment. Outside of needing them to restore a given database they have some information that can be useful in certain situations. One situation I found them convenient is with consolidation projects. If the databases are online you can obviously go to the source SQL Server instance to gather that information, but as a consultant I don’t necessarily have access to every environment. You may have the same issue if you are being brought into a project and your customer or department manager just wants you to advise on how you would setup the server. One easy request is to have them point you where the backups are stored and ensure you have access to the files.

How to track changes in SQL Server

March 8, 2016 by Ahmad Yaseen

As a part of a Big Data project, we are often asked to find the best way to track the changes applied to the database tables, so that, no requirement is created to load all the huge tables to the data warehouse database at the end of the day, if not all of the data was changed.

Using and troubleshooting SQL Server IntelliSense – For SQL Server 2012 or higher

February 29, 2016 by Minette Steynberg

When I first heard that Microsoft was going to introduce IntelliSense for SQL Server I could hardly contain my excitement. Much to my disappointment the initial version of IntelliSense left a lot to be desired. From issues like just not popping up at all, to not working on remote servers and sometimes picking incorrect entries could make it a bit tedious to use and for those of us who started out on SQL Server before the advent of IntelliSense it was easier just to revert back to good old fashioned typing from memory. I am happy to report however, that there has been great strides in IntelliSense to such an extent that if I suddenly had to live without it, I would be really really sad and my productivity would probably be affected somewhat as well. Read more »

Azure SQL Database vs SQL Server on Azure Virtual Machines

February 22, 2016 by Daniel Calbimonte

Introduction

There are two main options in Azure to handle SQL databases:

Azure SQL Database.
SQL Server on Azure VM.

In this chapter, we will talk about the advantages and disadvantages of each option.

SQL Server pivoting on non-numeric data types

February 19, 2016 by Sifiso Ndlovu

Introduction

In the article, Multiple Options to Transposing Rows into Columns, I covered various options available in SQL Server to rotating a given row into columns. One of the options included the use of a PIVOT relational operator. The mandatory requirement of the operator is that you must supply the aggregate function with only a numeric data type. Such a mandatory requirement is usually not an issue as most aggregations and subsequent pivoting is performed against fields of numeric data type. However, sometimes the nature of business reporting requests may be such that you are required to cater for pivoting against non-numeric data types. In this article we take a look at how you can deal with such requirements by introducing a workaround to pivoting on non-numeric fields.