SQL Server auditing with Server and Database audit specifications

November 20, 2017 by Dinesh Asanka

Auditing is a key feature in any application or any system as it provides end users with better analysis for administrators. Apart from analysis, auditing can be used as a troubleshooting mechanism too. Apart from organizational reasons, there are compliance reasons for enabling auditing depending on the domain of operation.

Auditing is mainly about answering four questions, i.e. who, when, what and where. However, depending on the situation, it might be decided what questions of the mentioned four should be answered.

How to use BULK INSERT to import data locally and in Azure

November 16, 2017 by Daniel Calbimonte

Introduction

BULK INSERT is a popular method to import data from a local file to SQL Server. This feature is supported by the moment in SQL Server on-premises.

However, there is a new feature that is supported only in SQL Server 2017 on-premises. This feature allows importing data from a file stored in an Azure storage account to SQL Server on-premises using BULK INSERT. This feature will be supported in Azure SQL versions in the future.

In this article, we will show two examples. The first example will show how to use the traditional BULK INSERT statement from a local CSV file to Azure and the second example will show how to import data from a CSV file stored in Azure to SQL Server on-premises.

How to solve the SQL Identity Crisis in SQL Server

November 14, 2017 by Ed Pollack

Description

SQL Identity columns provide a convenient way to auto-number an ID column within a table without the need to manage the sequence. This convenience can save immense amounts of time, but also presents a single challenge: What happens when an identity column runs out of space within the data type chosen?

How to set up SQL Server Log Shipping on Linux

November 13, 2017 by Prashanth Jayaram

Log shipping is a high-availability configuration that perhaps most of us are familiar with. It’s one of the oldest techniques wherein we ship transaction logs from a Primary database to a Secondary database. Log Shipping is still a vital feature used in case of applications that use warm standby for Disaster Recovery. We can see many articles which discuss the process of configuring Log shipping using T-SQL or SSMS.

How to import data from an Excel file to a SQL Server database

November 10, 2017 by Marko Zivkovic

There are many ways to import data from an Excel file to a SQL Server database using:

SQL Server Integration Services (SSIS)
the OPENROWSET and OPENDATASOURCE functions
SQL Server linked servers
the SQL Server Import and Export Wizard

In this article, steps for importing data from an Excel file to a SQL Server database will be explained using the SQL Server Import and Export Wizard including some of problems that can occur during the processes.

An introduction to the bcp Utility (bulk copy program) in SQL Server

November 9, 2017 by Daniel Calbimonte

Introduction

The Bulk copy program aka bcp is the console application used to export and import data from text files to SQL Server or vice versa.

It is very popular because it is fast and easy to download.

How to be the top SQL Shack author and attend the PASS Summit – twice in a row

November 8, 2017 by Ahmad Yaseen

Being the top SQL Shack author again required double the effort and the time than I spent last year (2016) to achieve it, competing more professional authors with creative writing styles. Fortunately, I achieved it this year again, two years in a row. Let me tell you how I succeeded and what kept me motivated!

How to identify and resolve Hot latches in SQL Server

November 7, 2017 by Bhavesh Patel

Description

In SQL Server, internal latch architecture protects memory during SQL operations. It ensures the consistency of memory structures with read and write operation on pages. Rudimentarily, it has two classes, buffer latches, and non-buffer latches which perform lightweight synchronization in the SQL Engine.

An overview of Python vs PowerShell for SQL Server Database Administration

November 2, 2017 by Prashanth Jayaram

Today, Microsoft claims that Linux runs like a First-Class citizen on Azure, .NET Core has been open-sourced, and has been ported over to Linux, taking PowerShell along. PowerShell runs really well on Ubuntu, CentOS, RedHat Linux, and even Mac OS X. There are Alpha builds available for a few other platforms as well, all available for exploitation under the MIT License on GitHub. “Manage anything, anywhere” is what Microsoft is offering to its customers. Keeping with that, we now have:

What’s new in SQL Server Management Studio 17.3; Import Flat File wizard and XEvent Profiler

November 1, 2017 by Ahmad Yaseen

SQL Server Management Studio is an integrated graphical user interface tool that is used to perform wide range of administration and development tasks on the SQL Server instances, hosted locally on the user machine, remotely on a Windows or Linux server or on SQL Azure or Azure Data warehouse instances hosted in the cloud. You can use SQL Server Management Studio to easily administrate, develop, deploy, configure and monitor the SQL Server instances.

Commonly used SQL Server Constraints: FOREIGN KEY, CHECK and DEFAULT

October 31, 2017 by Ahmad Yaseen

In the previous article Commonly used SQL Server Constraints: NOT NULL, UNIQUE and PRIMARY KEY, we described, in detail, the first three types of the SQL Server constraints; NOT NULL, UNIQUE and PRIMARY KEY. In this article, we will discuss the other three constraints; FOREIGN KEY, CHECK and DEFAULT by describing each one briefly and providing practical examples.

How to build a SQL Server Virtual Lab with AutomatedLab in Hyper-V

October 30, 2017 by Craig Porteous

There are often situations I want to test a piece of software or the latest version of SQL Server for a proof of concept, maybe even the new Power BI Report Server. I could install these locally, where I’ve likely got previous SQL installs or an already burgeoning system. You could be lucky that you have access to Virtual Machines that you can build out for the task. Now add a few hours to setup Windows or even just the applications themselves. Those VMs may be in Azure, or AWS, now add a few £’s or $’s every time you want to work on something. Did you remember to turn them off?

How to import/export data to SQL Server using the SQL Server Import and Export Wizard

October 27, 2017 by Marko Zivkovic

This article will explain the steps for importing and exporting data using the SQL Server Import and Export Wizard.

Depending on the options that are selected in the SQL Server Import and Export Wizard, some steps may be different than those described in this article.

For the purpose of this example, exporting data from the SQL Server database to an Excel file will be shown.

SQL Server utilities you probably didn’t know about – SQLdiag, osql and SqlLocalDB

October 26, 2017 by Daniel Calbimonte

Introduction

There are several tools included in SQL Server on-premises that you may not know.

In this article we will explain the following tools:

SQLdiag
osql
SqlLocalDB

After reading this article, you will learn how to use them and understand what are they for.

Constraints in SQL Server: SQL NOT NULL, UNIQUE and SQL PRIMARY KEY

October 25, 2017 by Ahmad Yaseen

This article explains the SQL NOT NULL, Unique and SQL Primary Key constraints in SQL Server with examples.

Constraints in SQL Server are predefined rules and restrictions that are enforced in a single column or multiple columns, regarding the values allowed in the columns, to maintain the integrity, accuracy, and reliability of that column’s data. In other words, if the inserted data meets the constraint rule, it will be inserted successfully. If the inserted data violates the defined constraint, the insert operation will be aborted.

ETL optimization using SQL TRY Functions

October 24, 2017 by Sifiso Ndlovu

Introduction

An enterprise data warehouse ETL solution typically includes, amongst other steps, a data transformation step that converts source data from one data type into another. It is during this step that type conversion errors may occur and depending on the type of exception handling techniques implemented in the ETL solution (or lack thereof), frustration may occur for both ETL developers and DBAs when trying to identify and resolve type conversion errors. In this article we take a look at a trio of SQL TRY built-in functions that were introduced in SQL Server 2012, namely, TRY_PARSE, TRY_CAST, and TRY_CONVERT and how they could be utilized to reduce type conversion errors in ETL solutions and thereby saving developers needless troubleshooting exercise.

Why would a SQL Server DBA be interested in Python?

October 23, 2017 by Prashanth Jayaram

If we follow blogs and publications on the technological advancement with respect to SQL, we notice the increase in the number of references to Python, of late. Often, that makes us think:

Why so much emphasis on Python these days?
Isn’t knowing PowerShell scripting sufficient for the automation requirements of today?
Is it the time DBAs started learning a programming language such as Python in order to handle their day-to-day tasks more efficiently?
Why do so many job postings these days include “knowledge of scripting” as a requirement?
Is all of this happening because the paradigm is shifting? Can’t the current Microsoft-specific languages such as PowerShell handle the shift?

How SQL Server selects a deadlock victim

October 20, 2017 by Ben Richardson

In the article on Deadlock Analysis and Prevention, we saw how deadlock occurs. We saw that SQL Server selects one of the processes involved in the deadlock as deadlock victim. In this article, we will look at the criteria on which SQL server selects a deadlock victim. Why one process is selected as deadlock victim and not the other.

How to import a flat file into a SQL Server database using the Import Flat File wizard

October 19, 2017 by Marko Zivkovic

Import Flat File wizard is a new feature implemented in SQL Server Management Studio (SSMS) v17.3 for importing a .csv or a .txt file into a SQL Server database.

The latest SSMS version can be found on this link

Working with the SQL Server command line (sqlcmd)

October 18, 2017 by Daniel Calbimonte

Introduction

Sqlcmd allows executing queries, T-SQL sentences and SQL Server scripts using the command line.

In the previous article How to work with the command line and Azure to automate tasks, we worked with the sqlcmd in Azure.

In this new chapter, we will show the following examples in a local SQL Server using sqlcmd:

Monitoring SQL Server database status changes using T-SQL and PowerShell scripts

October 17, 2017 by Prashanth Jayaram

Monitoring a SQL Server database is a critical component of database administration. Ninety percent of the organizations expect the number of databases to increase over the next twelve months. An increase in data volumes can have negative effects on the availability of databases. Hence, SQL Server database monitoring is considered a critical responsibility of a database administrator. Organizations tend to spend a lot of their funds towards enterprise solutions. And due to the sensitive and growing nature of business and user needs, application availability is very important nowadays.

Introduction and FAQs about Microsoft Azure technologies

October 13, 2017 by Daniel Calbimonte

Most of the DBAs, Data Scientists, BI and Database developers are working on SQL Server on-premises. Is it time to migrate to the Cloud?

In this new article, we will talk about numbers, statistics, Azure technologies like Virtual Machines with SQL Server, Azure SQL, Azure Data Factory, Azure Machine learning and other technologies.

How to choose and check the right Database Recovery Model in accordance to your backup strategy

October 11, 2017 by Jefferson Elias

Introduction

Following best practices, we should apply principles like segregation of duties. This implies the segregation of application environments. In particular, we might see for a given application that it incorporates at least two environments: a production environment and a non-production environment that exists for testing, Q/A, training, etc

Managing A Slowly Changing Dimension in SQL Server Integration Services

October 10, 2017 by Thomas LeBlanc

A data warehouse has to be historically correct. This becomes an issue when data like the Product List Price for a previous year needs to be saved historically. Dimensional Modeling methodologies provide a solution for the situation. The Slowly Changing method integrated with components from SQL Server Integration Services solves the issue. This article will look at updating a product dimension table using the Slowly Changing Type 2 Dimension while maintaining the Type 1 columns.

The evolution of SQL Server towards Digital Transformation challenges

October 9, 2017 by Prashanth Jayaram

In a data-driven world, where every second see a transfer of billions of pieces of data, enterprises are focused on making the access to data fluid and capable of being accessed on demand on a myriad of devices. Leveraging these capabilities to deliver better business results is now the prime focus. Information Technology is no more what it has been. What the web saw during the dot-com boom is what enterprise data is seeing now. Social, mobile, analytics, cloud, Big Data, Internet of Things… They’ve been enabling organizations to scale.