Kimberly Killian

How to automatically refresh a SQL Server database

October 4, 2017 by

As a DBA, I am often asked to automate tasks. In addition to this, I generally work with a lot of developers that need to test with data refreshing on a regular basis. A lot of the time, I am asked to either stand up a new test environment or overwrite/refresh an existing one. Now, these tasks are not difficult to complete, but why not just automate them into a SQL Agent job and just run it whenever you need it or schedule it and let the job do all the work? If you have read some of my previous articles (What is causing database slowdowns or Simple SQL Server automated user auditing), you will know I am a big proponent of automating repeatable tasks. The best part of this is if you have multiple clients like I do is that you can script out the job, change the variables and use it over and over again. I cannot tell you how many times I say “I have a job for that” when a client asks me to automate a task.

Read more »
Ahmad Yaseen

SQL Server Data Type Conversion Methods and performance comparison

October 3, 2017 by

When you define SQL Server database tables, local variables, expressions or parameters, you should specify what kind of data will be stored in those objects, such as text data, numbers, money or dates. This attribute is called the Data Type. SQL Server provides us with a big library of system data types that define all types of data that can be used with SQL Server, from which we can choose the data type that is suitable for the data we will store in that object. You can also define your own customized user defined data type using T-SQL script. SQL Server data types can be categorized into seven main categories:

Read more »
Prashanth Jayaram

How to configure Always Encrypted in SQL Server 2016 using SSMS, PowerShell and T-SQL

October 2, 2017 by

In an era of remote storage and retrieval of data, including the cloud, data security plays a vital role, especially since it’s vulnerable during the transit. Situations like database backup or copy from or to the cloud, there is always a risk of data exposure to outside world lurking around one corner or the other. We have seen a noticeable surge in the technologies around protection and security of data from the world full of unsafe hands. Efforts are being made to protect data at a very granular level of the encryption hierarchy. Protection of business data cannot be stressed upon more.

Read more »
Samir Behara

Review of SQL Cop unit tests collection

September 29, 2017 by

The importance of Code Quality in Software Development has increased over the period of time. Everyone wants to follow coding standards and write clean code. However, to timely deliver a product to Business we end up in violating design principles, writing fewer unit tests, increasing code complexity and breaching best practices. This increases the overall Technical Debt of the application and has the potential to halt future development work by creating unplanned work.

Read more »
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 »
Ahmad Yaseen

T-SQL commands performance comparison – NOT IN vs NOT EXISTS vs LEFT JOIN vs EXCEPT

September 22, 2017 by

The T-SQL commands library, available in Microsoft SQL Server and updated in each version with new commands and enhancements to the existing commands, provides us with different ways to perform the same action. In addition to an ever evolving toolkit of commands, different developers will apply different techniques and approaches to the same problem sets and challenges

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 W. 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 »
Page 5 of 39« First...34567...102030...Last »