Marko Zivkovic

How to create a linked server to an Azure SQL database

September 27, 2017 by

Linked servers allow to access data from another SQL Server or another data source (e.g. Excel) by using SQL Server Management Studio (SSMS) or Transact-SQL.

This article will explain how to create and configure a linked server to retrieve data from an Azure SQL database. Also, we will explain how to solve some common problems/issues during the process of creating a linked server to an Azure SQL database.

Read more »
Gerald Britton

How to compare tables in SQL Server

September 26, 2017 by

Introduction

If you’ve been developing in SQL Server for any length of time, you’ve no doubt hit this scenario: You have an existing, working query that produces results your customers or business owners say are correct. Now, you’re asked to change something, or perhaps you find out your existing code will have to work with new source data or maybe there’s a performance problem and you need to tune the query. Whatever the case, you want to be sure that whatever changes have been made (whether in your code or somewhere else), the changes in the output are as expected. In other words, you need to be sure that anything that was supposed to change, did, and that anything else remains the same. So, how can you easily do that in SQL Server?

Read more »
Daniel Calbimonte

How to connect to the Azure Storage Account with SQL Server Management Studio (SSMS)

September 25, 2017 by

Introduction

In SQL Server Management Studio (SSMS), it is possible to connect to the Azure Storage. The Azure Storage Account is useful because it creates replicas automatically in the cloud. You only need to upload your file to the Azure Storage Account and the replication is automatic. You can have multiple replications in different regions and Datacenters with few clicks.

Read more »
Prashanth Jayaram

SQL Server DB Migration – Cloning a database to another collation

September 19, 2017 by

Database migration is a vital task in any environment, complex or otherwise. Seamless migrations are the goal but the efforts required to ensure it are tremendous.

Backing up and restoring the database is surely a preferred and a robust approach, but does it work well in all situations? How do we plan this when the source and the destination databases need different configurations? How do we make such a migration seamless?

Read more »
Ahmad Yaseen

SQL Server 2016 Maintenance Plan Enhancements

September 18, 2017 by

SQL Server Maintenance Plans is a SQL Server Management Studio built-in feature that helps in creating a workflow of variant database administration tasks, which can be run automatically using a predefined schedule or manually triggered by the user.

SQL Server Maintenance Plans allow you to use typical database maintenance tasks or customize your own task using a T-SQL script that runs on the local server or group of SQL Servers, providing more flexibility to the database administration tasks.

Read more »
Sifiso Ndlovu

How to replace hardcoded lookups using SQL Server Master Data Services

September 14, 2017 by

Introduction

A big part of the technical debt in my organization’s data warehouse (DW) and business intelligence (BI) environments relates to hardcoded lookup data. This is data required by the business to make sense of transactional data but was never planned for in the underlying source system and consequently get injected into DW and BI solutions. Inevitably, it is only a matter of time before DW and BI team lose track of the places wherein the hardcoded data reside thus making it difficult to maintain. Furthermore, due to lack of documentation or staff retention, anyone who subsequently takes over these DW/BI solutions can unknowingly create duplicate lookup data. In this article, I explain how we reduced such technical debt in my organization by moving most of the hardcoded lookups into SQL Server Master Data Services (MDS).

Read more »
Craig Porteous

How to connect to (and query) Power BI and Azure using PowerShell

September 13, 2017 by
Power BI “as a whole” is a bit of a black box. If you’re like me and used to using SQL Server & its components; SSRS, SSAS etc. you have access to installation directories, Event logs, trace logs, error logs, chocolate logs? You can see full instances & their contents in one go, whether that be databases, reports or cubes. It gives you the control over & responsibility for performance & maintenance. Read more »
Prashanth Jayaram

How to Capture Database(s) usage stats using sp_spaceused and PowerShell

September 11, 2017 by

Today’s businesses work with huge volumes of data. Sometimes, understanding the granularity of the data is helpful in data administration. Understanding the granularity poses unique challenges though and the database administrator needs to balance the key business and technical metrics of the environment. One of the key technical metrics is disk space estimation, which is vital to capacity planning and forecasting. The simplest way to get this information is by using the system stored procedure, called, sp_spaceused. The growth metrics can be captured periodically and stored in a central repository, a repository dedicated to helping with capacity planning and forecasting the disk requirements. This is a critical part of the day-to-day activities of a database administrator.

Read more »
Derik Hammer

Synchronizing SQL Server Instance Objects in an Availability Group

September 8, 2017 by

Availability groups can no longer be considered new. The feature was released in SQL Server 2012 but I often notice that SQL Server Failover Cluster Instances (FCIs) are much better understood. For a very long time, when people thought about SQL Server they thought about the entire instance as a unit. Microsoft has been implementing new features and services which begin to segment the concept of an instance from the concept of a database.

Read more »
Samir Behara

Querying Microsoft SQL Server 2012/2014 – Preparing for Exam 70-461

September 8, 2017 by
In today’s competitive world, technical certifications play an important role in advancing your career. It is a great way to validate and sharpen your technical skills, thus helping you in being more productive in your assignments. It also helps you to stand out from the crowd and get more visibility. A few months back I decided to appear the Microsoft Exam 70-461: Querying SQL Server 2012/2014 and was able to successfully pass the exam. Read more »
Thomas LeBlanc

Using Many-to-Many Relationships in SQL Server Analysis Services (SSAS) 2016

September 7, 2017 by

The Multidimensional Cube option of Analysis Services has handled many-to-many relationships with ease for many versions before 2016. The Tabular had a work around using DAX formulas until the release of SQL Server 2016. There are still some limitations to many-to many in Tabular but of course, there are some “tricks” to overcome the limitations. But, the many-to-many relationship will be in businesses data for many years to come. A solution has to be provided when it comes to Analysis Service databases.

Read more »
Jefferson Elias

How to drop a role in a SQL Server Database

September 6, 2017 by

Introduction

Let’s say we have a database role that we don’t need anymore because we defined another security policy using another database role. What are the steps to follow in order to properly drop this database role? That’s the question this article will try to answer, covering as many cases as possible.

In the following article, we will consider the simple steps we can follow in order to do this task using both SSMS and T-SQL. Then, we will focus on some facts that will lead us to the conclusion that, if we do it this way, it won’t work every time. We will list some situations where it could fail and define a test case situation in order to create a stored procedure that will do the job correctly, in all cases bymanaging these situations.

Read more »
Minette Steynberg

8 things to know about Azure Cosmos DB (formerly DocumentDB)

September 4, 2017 by

Introduction

Azure Cosmos DB is a low-latency, high throughput, globally distributed, a multi-model database which can scale within minutes and offers 5 consistency options to let you decide how to deal with the CAP theorem.

Azure Cosmos DB used to be known as Document DB, but since additional features were added it has now morphed into Azure Cosmos DB. The name was chosen to spark the innovation and imagination of developers around the globe.

Read more »
Neeraj Prasad Sharma

Nested Loop Joins in SQL Server – Batch Sort and Implicit Sort

September 1, 2017 by

In SQL Server, it`s not always required to fully understand the internal structure, especially for performance and optimization, if the database design is good, because SQL Server is a very powerful Relational Database and, as such, it has many inbuilt optimization processes which assure a response to the users as fast as possible. But it is always beneficial for the SQL Server developers and administrators to understand the internal structure of the SQL Server so that they can understand and fix the problems that slowed the response of the Database.

Read more »
Ahmad Yaseen

What’s new in SQL Server Management Studio 17.2; Authentication methods, scripting options and more

August 31, 2017 by

SQL Server Management Studio is an integrated graphical interface that is used to configure, manage, monitor and administrate the SQL Server instances hosted on the local machine, on a remote server or in the cloud. It provides us with editing, debugging and deploying environment for the T-SQL, XML, MDX and DMX languages.

Read more »
Daniel Calbimonte

Configure Multi-Factor Authentication in Azure SQL Database

August 30, 2017 by

Introduction

The new SSMS 17.2 allows users to authenticate using Active Directory with Multi-Factor Authentication (MFA). This is a secure method for authentication where you have more than one method to validate your authentication. That way, you have a more secure authentication and prevent your Azure Portal or applications to be easily attacked by hackers or other malicious users.

Read more »
Minette Steynberg

Introduction to Azure SQL Data Warehouse

August 29, 2017 by

Introduction

Azure SQL Data Warehouse is a new addition to the Azure Data Platform. When I first heard about it I wasn’t quite sure about what exactly it would be. As it turns out it is relational database for large amounts of database and really big queries as a service. This is essentially the equivalent of the APS (Analytics Platform System) in the cloud.

Read more »
Jefferson Elias

Hands on Full-Text Search in SQL Server

August 25, 2017 by

Introduction

In most cases, we will use clustered and non-clustered indexes to help a query go faster, but these kinds of indexes have their own limitations and cannot be used for fast text lookup. For instance, a LIKE operator will lead SQL Server to scan the whole table in order to pick up values that meet the expression next to this operator. This means it won’t be fast in every case, even if an index is created for considered column.

Read more »
Ed Pollack

SQL Server reporting – SQL Server Index Utilization

August 24, 2017 by

Understanding indexing needs allows us to ensure that important processes run efficiently and that our server hardware is not being over-taxed by poorly performing queries.

Collecting metrics on SQL Server index usage and missing index needs is critical to making smart decisions. To be truly proactive, though, we need to create a framework that allows us to quickly, efficiently, and regularly report on changes in indexing needs. With a system in place that can let us know when changes are needed, we can stay ahead of the optimization game and keep our customers happy!

Read more »
Nikola Dimitrijevic

All about SQL Server spinlocks

August 23, 2017 by

As discussed in the article All about latches in SQL Server, spinlocks are also a special form of locks that SQL Server uses to protect data. Spinlocks are lightweight objects used by the SQL Server Operating System (SQLOS) to protect data structure access. To understand, properly, the difference between latches and spinlocks and why the spinlock is an important SQL Server’ object type, it is vital to understand the cycle of query execution in SQL Server

Read more »