How to Avoid CXPACKETs?

November 25, 2015 by Dinesh Asanka

CXPACKET is one of the famous wait types that database administrators are experiencing. Before moving into the details of the CXPACKET wait type, first, let us discuss the waits in SQL Server in brief.

SQL Server is a mini operating system. When SQL Server is executing any task and if for any reason it has to wait for resources to execute the task, it will wait in a list until it gets the relevant resources. This list is called the Waiter list or suspended list. This is not a queue as whenever that task is ready with required resources it will move to the runnable queue which means that it is ready to execute whenever the processor is free to execute. Depending on the type of wait, there are more than 200 wait types. CXPACKET, WRITELOG, ASYNC_NETWORK_IO are the most common wait types. This is a very brief discussion about waits. For any case where this is not clear, it will be better to get more details from other sources as this article is not intended to discuss waits in detail.

Mapping schema and recursively managing data – Part 2

November 20, 2015 by Ed Pollack

Introduction

In Part 1 of this article, we built a case for the benefits of documenting our schema via the use of a stored procedure, as well as the ways this information can be used for DML operations on a relational database. Below, we will continue where we left off by speeding up our SQL queries and then putting everything together so that we can demo a usable script that will hopefully make your life easier!

Mapping schema and recursively managing data – Part 1

November 18, 2015 by Ed Pollack

Introduction

In a typical OLTP environment, we want to maintain an acceptable level of data integrity. The easiest way to do this is through the use of foreign keys, which ensure that the values for a given column will always match that of a primary key in another table.

Over time, as the number of tables, columns, and foreign keys increase, the structure of that database can become unwieldy. A single table could easily link to thirty others, a table could have a parent-child relationship with itself, or a circular relationship could occur between a set of many tables.

Query Store – the next generation tool for every DBA

November 13, 2015 by Brian Bønk Rueløkke

Along with the release of SQL server 2016 CTP 3 now comes the preview of a brand new feature for on premise databases – the Query Store. This feature enables performance monitoring and troubleshooting through the log of executed queries.

This blogpost will cover the following aspects of the Query Store feature:

Introduction
How to activate it
Configuration options
What information is found in the Query Store
How to use the feature
What’s in it for me

Using SQL Server 2016 CTP3 in Azure

November 6, 2015 by Kenneth M. Nielsen

As the time of the release of the next version of SQL Server is closing in, the cycle of Community Technical Preview (CTP) releases is shorter and shorter – we are now at CTP3 and the product seems very much mature for release. There is a handful of features not yet implemented, that was on the roadmap – but I bet you the SQL Team will go an extra length to get the features ready for the final release.

This post will show you how to get started with testing/using the newest version of the CTP.

7 Neat tricks to better safeguard a SQL database

November 3, 2015 by Priyanka Chouhan

Every organization, whether large or small, imposes some security measures to protect its confidential data. Such data usually includes contract details, project planning reports, employee information, financial account details and more. More often than not, firewalls, anti-viruses, and other data security techniques are applied to keep unauthorized users or programs from accessing such sensitive company data. What most organizations probably fail to recognize is the threat that exists to such information from people within the circle of trust.

How to connect our C# application in Azure

October 30, 2015 by Daniel Calbimonte

Azure is a simple and cheap way to have a Database. You can rent your Azure Database of 2 GB per 5 USD per month. That is a very cheap price. You can also administer your Database from your Android or your iPhone, which is very interesting.

It is also possible to create your Windows Application using Visual Studio and connect to SQL Azure. In this new chapter, we are going to create Windows application in C# and connect to a SQL Azure database. We are going to retrieve the information of a table stored in Azure in our Windows application.

Where’s my Team Explorer 2015?

October 28, 2015 by Sifiso Ndlovu

Introduction

If you have been using Team Foundation Server (TFS) for as long as I have, you will recall that just about every release of TFS has always been accompanied by standalone Team Explorer and Team Explorer Everywhere software installers. So you will understand my bewilderment when I couldn’t locate these installers following a successful setup of on-premise (on-prem) TFS 2015 server instance for my employer. After going through every possible link on search engine results relating to “Team Explorer 2015,” I concluded that only Team Explorer Everywhere 2015 was released this time around.

SQL Server Database Source Control using Visual Studio Online: Git

October 23, 2015 by Sifiso Ndlovu

Introduction

Source-code versioning control has always been at the core of Continuous Integration (CI) development practice. In fact tools by JetBrains and Microsoft (amongst, many vendors) have provided development teams an ability to implement some form of CI in their Software Development Lifecycle (SDLC). In recent years, however, the focus of an effective CI strategy has expanded to include mechanisms of keeping track of database files. The possibility of this gradual expansion has largely been driven by two factors:

Stability and maturity of source control systems such as TFS and Git
Availability of freeware and proprietary tools that make it convenient for data professionals to keep control of their database source code

AlwaysOn Availability Groups – Curiosities to make your job easier – Part 4

October 13, 2015 by Murilo Miranda

Here we are with the last part of this series of articles! In this article we are going to see two curiosities:

How does in-memory OLTP work with Availability Group?
Can I migrate from Log Shipping or Database Mirroring to Availability Groups?

So, let’s start!

AlwaysOn Availability Groups – Curiosities to make your job easier – Part 3

October 9, 2015 by Murilo Miranda

In continuation to the Availability Groups series, here you have another curiosity coming to make your job easier and help you to provide a solution to your customers 🙂

This time we will be talking about listeners. Basically, we will be talking about the limitation of one listener per Availability Group. If you never tested / tried this, this is what happens when you already have a listener in the Availability Group, and try to create another one:

AlwaysOn Availability Groups – Curiosities to make your job easier – Part 2

October 6, 2015 by Murilo Miranda

In continuation to the previous article, where we talked about what happens when I add a readable secondary and why there are different options to control connections to the AG, it’s time to continue and demystify more curiosities about the beloved Availability Groups!

How to work with Visual Studio and VM databases in Azure

September 24, 2015 by Daniel Calbimonte

Introduction

In this new article, we will use Visual Studio to connect to a SQL Server Database Installed in a Virtual Machine in Azure.

Visual Studio is one of the most popular development tools in the world and connecting to Azure with Visual Studio is a very common need.

We will modify an existing SQL Server table in Azure from Visual Studio. Later we will create a stored procedure and finally, we will create an inline table-valued function.

Working with Visual Studio and SQL Azure databases

September 21, 2015 by Daniel Calbimonte

Introduction

In this chapter, we will work with Visual Studio to connect to SQL Databases in Azure.

The first part of this chapter will be to create a Database in the Azure Portal and the second part will be to connect in Visual Studio and create some Database Objects.

Using the Azure Portal to monitor your Azure Machines with SQL Server installed

September 17, 2015 by Daniel Calbimonte

Introduction

In this new chapter, we will show how to use the Microsoft Portal to monitor the Virtual Machines with SQL Server Installed. We will show how to do it with the current Portal and the New Microsoft Azure Portal. We will check how to monitor performance, watch the logs, review the settings and change them. Read more »

Behold the new live query stats in SQL Server 2016

September 11, 2015 by Brian Bønk Rueløkke

With the release of SQL Server 2016 also comes a great new feature to get a live view of the current execution plan for an active query.

This blogpost will cover the aspects of this new feature including:

Introduction
How to activate
How to use and read the output
Downsides – if any

AlwaysOn Availability Groups – Curiosities to make your job easier – Part 1

September 7, 2015 by Murilo Miranda

We all love Availability Groups! Since its introduction in the SQL Server 2012, some things changed. In the beginning it was seen as “just a replacement for the database mirroring”, but when we discovered that this would support readable secondary’s, the possibility of having a listener and get rid of the shared storage – even being based on a Failover Cluster – we saw that Availability Groups is a special feature. Read more »

Cloud Witness – A new feature to make our lives easier

September 4, 2015 by Murilo Miranda

In order to have a well-configured cluster we need to setup the quorum properly, but there’s more…Here is where the Cloud Witness comes to help.

In this article, I’m going to present something that would be very useful if was already been released, but no, I need to anxiously wait for the next version of Windows Server to be released, in order to start implementing this. Even though, I’m going to share with you, SQL Shack reader, why I’m so excited with that small and simple feature.

Low cost disaster recovery solution using Azure

August 31, 2015 by Murilo Miranda

We already talked a lot about Azure and hybrid deployments on this. In order to better understand this article I suggest the reading of the article “Azure Blob Storage – Placing database files in the cloud”, as the presented solution will be based on the same approach.

Using MS Excel to access to a relational SQL Database in Azure

August 14, 2015 by Daniel Calbimonte

Introduction

There are great Microsoft tools to access to SQL Server in Azure. However, as a Database Developers and Administrators we need to help the end user to access to the information using a familiar and intuitive interface.

Most of the end users have a good knowledge in MS Excel. That is why it is recommended to connect our SQL Azure database to Excel. That way, the end user will be able to easily create reports, charts and generate useful information.

In this chapter, we will show how to connect to SQL Azure using Microsoft Excel.

Row level security in SQL Server 2016

August 13, 2015 by Brian Bønk Rueløkke

With the release of SQL Server 2016 comes many great new features. One of these is the implementation of row level security in the database engine.

This blogpost will cover the aspects of this new feature – including:

Setup
Best practice
Performance
Possible security leaks

The DBAs guide to stretch database

August 11, 2015 by Brian Bønk Rueløkke

One of the new features in SQL Server 2016 – and there is a lot – is the ability to stretch the on premise databases to an Azure environment.

This blogpost will cover some of the aspects of this – including:

Primarily setup – how to get started
Monitoring state of databases that are in ‘stretch mode’
Daily work with stretch databases
Backup – what’s good to know

Stretch Database – The art of extend to reduce

August 7, 2015 by Murilo Miranda

Let’s continue the hybrid saga! After two articles talking about the Azure Blob Storage and what we can do using it, it’s time to check an alternative solution, that does the same of what was presented in the last article (a Hybrid Database used as archival solution for who didn’t read). This time we are not using the Azure Blob Storage, but we are still using Azure!

The feature is…Stretch Database

Azure Blob Storage – Literally a Hybrid database deployment

August 4, 2015 by Murilo Miranda

In continuation to our hybrid deployment series, we will check few more options in order to integrate on-premises and Azure, using of course SQL Server.

In the last article we talked about Azure Blob Storage and how to use this service to store a SQL Server database entirely there. Meaning that we can have our instance in our datacenter, see the server in front of you, and still have your database stored in the cloud, with your data and log files out of your disks.

Azure Blob Storage – Placing database files in the cloud

July 31, 2015 by Murilo Miranda

Do you know that since SQL Server 2012 you can have your entire database stored in Azure, even if you have your server on-premises? This article will introduce you to this capability, showing the basic steps to allow your instance to be in contact with your Azure Blob Storage Account.