Timothy Smith

Useful DBCC log commands

June 1, 2016 by

Background

When we’re architecting or troubleshooting issues in an environment, understanding how the log is set up and its history helps us identify if improving it will carry significant impacts overall. We have some built in tools that we can use, such as DBCC LOGINFO and DBCC SQLPERF and in this tip we look at how we can use them when we’re debugging issues, designing our logs for scale, or monitoring our current setup. We also look at some ways we can retain the information these provide so that we can use this for historic measurements and benchmarking, as well as using them for potentially alerting.

Read more »
Ahmad Yaseen

SQL Server 2014 contained databases

June 1, 2016 by

SQL Server provides two ways to authenticate users; SQL Server Authentication, which requires a predefined username and password to connect to the SQL Server, and Windows Authentication, in which SQL Server trusts the windows integrated user. The server level user that is authenticated to connect to the SQL Server is called a Server Login. This login should be mapped to a database user and granted permissions at the database level in order to access the database and be able to perform the authorized tasks. The relationship between the Server login and the database user in addition to the database metadata stored in the master system database draw up the dependencies between the SQL Server databases the server-level resources.

Read more »
Marko Radakovic

Revision history of an object change in a SQL database using Team Foundation Server

May 31, 2016 by

Similarly, as described previously in this article, where the revision history is covered for the Git source control system, we’ll present the workflow of reviewing the history of committed SQL database objects using Team Foundation Server (TFS) source control system. In order to use TFS and have SQL database objects being version controlled, Visual Studio is required, as well as TFS server, either installed on a machine or TFS through Team Services, which is actually TFS “in the cloud”.

Read more »
Ed Pollack

Creating the perfect schema documentation script

May 27, 2016 by

Description

System views allow us to gain access to information about any objects within SQL Server, from tables to extended properties to check constraints. This information can be collected and used for many purposes, one being the need to document our objects without the need to click endlessly in the GUI or to incur an immense amount of manual work.

Read more »
Timothy Smith

Monitoring changes in SQL Server using change data capture

May 27, 2016 by

Background

In multi-user environments, changes may occur frequently to the architecture, data, or overall structure that creates work for other users. In this series, we look at some ways that we can track changes on the data and architecture layer for pin-pointing times, changes, and using the information for alerting, if changes should be kept to a minimum. SQL Server comes with some built-in tools that allow us to monitor changes, and depending on the architecture, we can create tools that allow us also to monitor and identify changes near the time that they occur.

Read more »
Timothy Smith

Some replication architecture errors and their resolutions

May 16, 2016 by

Background

From time to time, I’ve run into replication issues in inherited environments that I did not architect and some of these environments experienced errors in replication because of how it was constructed from the beginning. In this tip, we look at some of the basics in replication architecture and then at solving some of these problems. Some of the replication issues I’ve seen are caused by misunderstanding what is impossible and possible with replication.

Read more »
Daniel Calbimonte

Azure Cosmos DB from zero to 10 minutes

May 10, 2016 by

Introduction

In our last chapter, we talked about Cosmos DB, which is available in Azure and explained that it is an Azure Database used to store NoSQL documents.

In this new article, we will show how to create and query the database from zero. The only requirement is to have an Azure Account. If you have an Azure account, this tutorial will take you just 10 minutes (if not, you can complain in the comments section below 😉 ).

Read more »
Timothy Smith

Troubleshooting some waits issues

May 10, 2016 by

Background

On occasion, I’ll see waits that exceed what I expect well above normal and a few of them have some architecture and standards to consider following when troubleshooting, though like most waits’ issues, there can be other underlying factors that are happening as well. In this article, I investigate the three waits ASYNC_NETWORK_IO and WRITELOG. In general, waits vary by environment and server, so before reading this article an immediate question to ask is, “Do you know what’s normal for yours?” When a wait suddenly spikes, or if the architecture is designed in a manner that should prevent a specific wait from consuming time, and yet you see that the wait does, I would be concerned. In addition, because applications and environments differ by architecture, you may want to consider other troubleshooting steps, as these may not apply to your situations.

Read more »
Sifiso W. Ndlovu

New Excel slicer feature for report users in Office 2016

May 9, 2016 by

Whilst researching for the article Report filtering: Excel slicers vs SQL Server Reporting Services (SSRS) parameters, I discovered a new Excel Slicer feature in Microsoft Office 2016 that allows users to select/deselect multiple items without having to hold down the control (Ctrl) keyboard key. For some people, this may not be a sufficient reason to upgrade to Office 2016 but for developers of business intelligence (BI) solutions, this new feature further enhances the experience of consumers of BI solutions.

Read more »
Timothy Smith

How to build better alerting

May 4, 2016 by

Background

One of the most popular complaints from developers to DBAs involves alerting, whether from third party tools or alerting built by other developers or DBAs in the environment. Building or using alerts for important applications, data layers, or processes within a SQL Server environment offer everyone benefits, but can become noisy if they’re architected poorly, or the purpose isn’t considered. In this article, we look at considerations for building effective alerts that tell us when something is wrong without creating situations where we learn to disregard them. We want to make sure that we respond when we need to, and not always be on high alert when there is no issue.

Read more »
Ahmad Yaseen

SQL Server 2014 Columnstore index

April 29, 2016 by

By default, SQL Server stores data logically in the tables as rows and columns, which appear in the result grid while retrieving data from any table and physically in the disk in the row-store format inside the data pages. A new data store mechanism introduced in SQL Server 2012, based on xVelocity in-memory technology, in which the data is stored in the column-store data format. This data store mechanism called the Columnstore index.

Read more »
Ahmad Yaseen

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

April 29, 2016 by

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
Read more »
Sifiso W. Ndlovu

Meaning of the 5127 database snapshot error code

April 15, 2016 by

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.

Read more »
Daniel Calbimonte

The new elastic databases in Azure

April 12, 2016 by

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.

Read more »
Shawn Melton

SSIS and PowerShell – Execute process task

April 11, 2016 by

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.

Read more »
Jefferson Elias

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

April 11, 2016 by

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.

Read more »