Multi server PowerShell script to backup SQL Server databases automatically

Eighty-two percent of the enterprises expect the number of databases to increase over the next twelve months. An increase in data volumes can have negative effects on the performance of databases. Think about the storage requirement and backup strategy to meet the Recovery Time Objective and the Recovery Point Objective. RPO and RTO are two of the most important parameters of a disaster recovery or data protection plan.

September 27, 2017

How to use source control with SQL Server Management Studio (SSMS)

Introduction

The growing adoption of Continuous Integration development practice implies that developers have to work in a collaborative manner by storing and sharing their source code into a central repository. For SQL Server Business Intelligence (SQL Server BI) developers, the collaboration is usually made possible by the Team Explorer plug-in for Visual Studio which provides an interface for integrating SSRS, SSIS and SSAS solutions into source control environments such as Team Foundation Version Control (TFVC) or Git. However, several aspects of SQL Server BI solutions involve T-SQL development (i.e. defining of SSRS datasets, SSAS data source views, as well as SSIS source data flow tasks).

September 25, 2017

Creating and automating Tableau Site documentation

One of many Tableau administrator responsibilities is to be familiar with the server configuration, managing users, automated server job process management, knowing Tableau metadata and being familiar with migration and scheduling. To stay updated and keep everything under control, good Tableau documentation can come in handy. Such documentation can be created using ApexSQL Doc, a tool for creating SQL Server, BI, SharePoint as well as Tableau site documentation, which will be covered in this article.

September 22, 2017

How to create and use DML triggers to audit data changes

A DML trigger is a special kind of procedural code programmed to fire when a data manipulation language (DML) event occurs in the database server. DML events include UPDATE, INSERT, or DELETE statements issued against a table or view. SQL Server has three types of triggers: DML triggers, DDL triggers, and logon triggers. For the purpose of this article, only DML triggers will be used to demonstrate and achieve a specific goal.

September 21, 2017

How to customize policies for automatically defragmenting SQL Server indexes

This article explains how to create customized policies for index defragmentation jobs for SQL Server

Introduction

When creating indexes, database administrators should look for the best settings to ensure minimal performance impact and degradation. However, over time, indexes will get fragmented, which can severely impact server performance.

September 18, 2017

How to Import and Export SQL Server data to an Excel file

There are several ways to Export/Import SQL Server data to an Excel file. In this article, a couple of ways to import and export SQL Server data to an Excel file will be explained:

  • Export SQL Server data to an Excel file using
    • the SQL Server Import and Export Wizard
    • T-SQL code
    • Export to Excel from ApexSQL Complete
  • Import SQL Server data into Excel using the Data Connection Wizard
September 11, 2017

SQL query performance tuning with I/O statistics and execution plans

Introduction

In a busy system, many things can cause a SQL query to run more slowly than expected or desired. Sometimes, getting a handle on the root cause can involve digging into wait statistics, CPU, memory or network pressure, ferreting out problems in the I/O subsystem, looking for issues in a virtual machine configuration and many other things. Often though, the problem comes down to a simple symptom: the query is doing too much I/O.

August 31, 2017

Multi server script to automatically monitor SQL Server availability

Introduction

As database administrators, we obsess over a few things, things we don’t compromise on. One of them is SQL Server availability. We may use several tools, scripts, and/or configurations, in addition to constant oversight to ensure that the database is always available. We sometimes even go that extra mile to ensure availability, because we know it is easier to ensure a database is available than deal with the outcome of unavailability. In this post, let us look at some of the easiest ways to ensure availability.

August 29, 2017

Creating SharePoint farm documentation

Installing SharePoint farms can go from “click-click-next” to a full day work depending on the configuration level for the server farm. For administrators that are new to the farm, it can be quite troublesome to learn the farm’s configuration. In those cases, it is important to have a farm documentation with all the setting information. SharePoint documentation is also important for recreating sets of configurations for when failure occur.

August 25, 2017

How to create and use CRUD stored procedures in SQL Server

Working with a database, at some point, usually means working with CRUD operations. Although not complex, CRUD operations are often used with SQL, so it is of great importance for developers to learn about how to create them efficiently and easily.

August 25, 2017