![Minette Steynberg](/wp-content/uploads/2015/02/7_OsKl0L_400x400.png)
![Minette Steynberg](/wp-content/uploads/2015/02/7_OsKl0L_400x400.png)
![Daniel Calbimonte](/wp-content/uploads/2014/10/DanielCalbimonte.jpg)
Azure SQL Database vs SQL Server on Azure Virtual Machines
February 22, 2016Introduction
There are two main options in Azure to handle SQL databases:
- Azure SQL Database.
- SQL Server on Azure VM.
In this chapter, we will talk about the advantages and disadvantages of each option.
Read more »![Sifiso Ndlovu](https://s33046.pcdn.co/wp-content/uploads/2022/10/Sifiso.jpg)
SQL Server pivoting on non-numeric data types
February 19, 2016Introduction
In the article, Multiple Options to Transposing Rows into Columns, I covered various options available in SQL Server to rotating a given row into columns. One of the options included the use of a PIVOT relational operator. The mandatory requirement of the operator is that you must supply the aggregate function with only a numeric data type. Such a mandatory requirement is usually not an issue as most aggregations and subsequent pivoting is performed against fields of numeric data type. However, sometimes the nature of business reporting requests may be such that you are required to cater for pivoting against non-numeric data types. In this article we take a look at how you can deal with such requirements by introducing a workaround to pivoting on non-numeric fields.
Read more »![Ahmad Yaseen](/wp-content/uploads/2015/12/Ahmad2.jpg)
How to configure SQL Server mirroring on a TDE encrypted database
February 19, 2016Securing and encrypting sensitive data stored in your production databases is a big concern, especially the databases storing the organization’s financial data and customers’ confidential information.
SQL Server offers multiple encryption methods in the cell, table and database levels. And in this article, we are interested in a SQL Server database encryption method, introduced in SQL Server 2008, called Transparent Data Encryption (TDE). SQL Server TDE provides encryption on the database file level; it encrypts the database (.MDF), (.LDF), (.NDF), (.BAK), (.DIF), (.TRN) and snapshot files.
The main purpose of this article is showing how we could setup a mirroring site for a database encrypted using SQL Server Transparent Data Encryption. But before starting the demonstration, it is better to introduce TDE first.
Read more »![Joshua Feierman](/wp-content/uploads/2015/12/GetPersonaPhoto.jpg)
A DBAs Introduction to Mercurial – Branching and merging
February 19, 2016Introduction
In my previous article, we went over the basics of Mercurial, as well as some arguments why using it is critical for database administrators. Among many reasons, it allows us to easily track history and changes to our scripts, which in turn makes it easier for us to experiment and enhance our toolkit, since we can do so safely without fear of permanently causing damage. In this installment, we are going to go into more depth on the specifics of two feature of Mercurial that, once harnessed, can add significant efficiency to our coding workflows.
Read more »![Sifiso Ndlovu](https://s33046.pcdn.co/wp-content/uploads/2022/10/Sifiso.jpg)
SQL Server convenience names
February 19, 2016Introduction
A while ago I discovered that you can conveniently connect to a local instance of SQL Server by typing one of the following names:
Read more »![Ed Pollack](/wp-content/uploads/2015/12/EdwardPollack.jpg)
Insight into the SQL Server buffer cache
February 18, 2016When we talk about memory usage in SQL Server, we are often referring to the buffer cache. This is an important part of SQL Server’s architecture and is responsible for the ability to query frequently accessed data extremely fast. Knowing how the buffer cache works will allow us to properly allocate memory in SQL Server, gauge accurately how databases are accessing data, and ensure that there are not inefficiencies in our code that cause excessive data to be cached.
Read more »![Marko Zivkovic](https://s33046.pcdn.co/wp-content/uploads/2021/01/ZivkoM.jpg)
![](https://s33046.pcdn.co/wp-content/uploads/2016/02/word-image-13-624x294.png)
How to connect to a remote SQL Server
February 18, 2016In this article, we will explain step by step how to connect remotely to a SQL Server Express instance. Remote access is the ability to get access to a SQL Server from a remote distance in order to manipulate data which are located on that SQL Server.
Read more »![Rajendra Gupta](https://s33046.pcdn.co/wp-content/uploads/2022/02/rajendra_gupta.png)
Peer to peer replication
February 18, 2016Nowadays many customers are spread globally and need to manage the requirement for users to connect from/to any location, perform an activity (insert, update, delete), and the databases should be kept synchronized across multiple sites.
Read more »![Daniel Calbimonte](/wp-content/uploads/2014/10/DanielCalbimonte.jpg)
How to execute jobs on multiple SQL Servers
February 18, 2016Introduction
In earlier chapter, we explained how to run queries in multiple SQL servers using the SQL Central Management Server. In this new chapter, we will show how to propagate a job from a SQL Server Master Agent Job to a target server.
This feature is called Multiserver Administration. In a multiserver administration, you need a Master Server and one or more target servers. In the master server, you create a copy of the job and then it is copied and executed in the target servers.
Read more »![Ed Pollack](/wp-content/uploads/2015/12/EdwardPollack.jpg)
Searching the SQL Server query plan cache
February 8, 2016Whenever a query is executed in SQL Server, its execution plan, as well as some useful execution data are placed into the plan cache for future use. This information is a treasure trove of metrics that can allow some very useful insight into your server’s performance and resource consumption. Much of this information would be difficult or impossible to acquire otherwise.
Understanding how to access and use the metadata about query execution will provide us the tools we need to answer questions about our server and gain fascinating performance data. I’ve found myself spending more and more time writing, tweaking, and using queries against the plan cache lately and look forward to sharing these adventures with you!
Read more »![Kimberly Killian](/wp-content/uploads/2016/01/IMG_4959.jpg)
What is causing database slowdowns?
February 2, 2016Why is my database so slow? This query used to be so much faster. Why does it take so long to rebuild my index? How come it was fine last month? Every day I am asked these types of questions by clients. Every day! A lot of database developers and application developers do not realize that indexes are ever changing entities within your database or rather they need to be monitored closely and managed periodically to remain efficient. I cannot even count the times someone tells me “but we have index’s on this or that column and it was fine last month” and so on. All while they fail to realize or even tell me that the database just took on, updated or deleted 1,000,000 records for example, which would definitely change the footprint of the data, making the index’s unsound or in need of help. Even adding 50 new users that use the data differently could require new indexes. That being said, I decided to automate a quick and easy data gathering and reporting job that helps to answer these questions. Most of the time query performance questions can be answered by determining the fragmentation levels of index’s, if there are missing index’s, duplicate index’s, unused index’s and what are the heavy hitters in regards to queries and are queries running in memory or to disk and how many executions. My favorite thing to do with SQL Server is automate, automate and automate the tasks that are asked of me over and over.
Read more »![Luan Moreno M. Maciel](/wp-content/uploads/2016/01/Pq4LqDtZ.jpeg)
In-Memory OLTP Series – Data migration guideline process on SQL Server 2016
January 29, 2016On the last article about the best modes to move the disk-based tables to using the In-Memory feature we covered all the aspects and styles available on SQL Server 2014. Continuing on the migration process now we’re going to look at some of the new enhancements of makes SQL Server 2016
Read more »![Daniel Calbimonte](/wp-content/uploads/2014/10/DanielCalbimonte.jpg)
How to run a local SQL Report from an Azure VM Database
January 28, 2016Introduction
In this new article, we will create a SQL Server Report using SQL Server Reporting Services from a SQL Server machine installed in an Azure Virtual Machine. We will show step by step how to connect from a local machine to a VM Azure Database and generate a SQL report.
Read more »![Joshua Feierman](/wp-content/uploads/2015/12/GetPersonaPhoto.jpg)
A DBAs introduction to Mercurial – Working with files and changes
January 28, 2016Introduction
In my previous article, we went over the reasons why DBAs should use version control, as well as the benefits of Mercurial as a specific choice. We also gave three examples of instances where source control can come in handy (though to be honest they were picked from a much longer list). In this article, I’m going to go a step further and actually walk you through setting up your first repository (locally for now, we’ll go into setting up a remote one later), making your first commit, and making (and viewing) changes to your newly tracked files. Let’s get started!
Read more »![Rajendra Gupta](https://s33046.pcdn.co/wp-content/uploads/2022/02/rajendra_gupta.png)
Database snapshot in SQL Server
January 28, 2016Database snapshot is a great feature that offers virtual read only consistent database copy. When we create the database snapshot in the live operational database, it takes a database point in time static view and Rollback all uncommitted transactions in the snapshot database so we will not be having any inconsistent data that is yet to be committed. Database snapshot always exists on the Source database server.
Read more »![Luan Moreno M. Maciel](/wp-content/uploads/2016/01/Pq4LqDtZ.jpeg)
In-Memory OLTP Series – Data migration guideline process on SQL Server 2014
January 28, 2016In this article we will review migration from disk-based tables to in-memory optimized tables. This article assumes that you already understand the pros and cons about In-Memory Technology, for more articles about this, please refer here.
There are some options available on SQL Server 2014 and SQL Server 2016 that will help you to identity, discover and track the tables and stored procedures that can be good candidates to be ported to memory. Furthermore it’s possible to check if the table meets to all the pre-requisites established. Here is the list of the features that we will take a look into.
Read more »![Kimberly Killian](/wp-content/uploads/2016/01/IMG_4959.jpg)
Simple SQL Server automated user auditing
January 25, 2016As a DBA I am often asked to provide lists of all active users ID’s or groups for a specific server or database. In addition to this, I am also asked to provide a list of failed logins. These requests are frequent enough that I decided to automate the process of gathering this data and provide two nicely formatted HTML emails. I am not going lie, manager types love these reports, and anything that makes managers love my work I am all about! I’m also all about automating anything that makes my job easier. Call me lazy or call me prepared, I hate having to do something over and over that I could easily throw into an SSIS package or Agent Job and just schedule it to do it for me. This entire process consists of using SQL Server Integration Services (SSIS), 4 tables and a SQL Agent Job containing 2 reports (Failed Logins and Active SQL Server Users). The SSIS package, along with all of the queries and scripts are attached at the end of the article.
Read more »![Timothy Smith](/wp-content/uploads/2016/01/head.png)
Understanding the distribution scale of transactional and snapshot replication
January 25, 2016Background
If an environment chooses to use snapshot or transactional replication, one useful exercise is to ask the technical end user (or client) what they think replication does. If you have access to a white board, you can even ask them to demonstrate what they think replication will do for their data. Generally, these technical end users will plot something similar to the below image, where we see a table with data being copied to another table with data.
Read more »![Daniel Calbimonte](/wp-content/uploads/2014/10/DanielCalbimonte.jpg)
How to run multiple queries using the Central Management Server
January 25, 2016Introduction
When you have thousands of SQL Servers, it is very hard to administer all of them. This article will show some tips to help you with these types of tasks.
In this new article, we will show how to run T-SQL scripts against multiple Servers using SQL Server Management Studio (SSMS). To do this, we will use the Central Management Server. The main idea of this feature is to administer multiple servers in a centralized way using queries or policies. This feature is available in SQL Server 2008 or later versions and cannot be applied in older versions.
Read more »![Eli Leiba](/wp-content/uploads/2015/12/eli_leiba.jpg)
Creating a stored procedure to fix orphaned database users
January 25, 2016The Problem
This problem demonstrates a very common scenario. When a backed up database is restored to a different location or server, the restored database users that exist inside of it, lose their association to the new server login objects.
This problem occurs because the server id in the user data is still the old server and must be changed to the new one.
The DBA task is then to restore the connection between the login and the database user object and to link them together again.
Read more »![Marko Zivkovic](https://s33046.pcdn.co/wp-content/uploads/2021/01/ZivkoM.jpg)
How to install SQL Server Management Studio 2008
January 20, 2016SQL Server Management Studio is a software application that helps users to easily access the SQL Server database engine and manipulate objects and data stored on SQL Server databases. The first version was released with Microsoft SQL Server 2005, replacing Enterprise Manager as the primary interface for Microsoft SQL Server.
Read more »![Kaloyan Kosev](/wp-content/uploads/2014/12/EgJdp2Xo.jpeg)
MS SQL Server backup optimization
January 19, 2016Backup and recovery are some of the most important DBA tasks, although they look simple enough and usually you setup them, leave them running on schedule and only come back if they fail – there is a whole new world in regards to optimization you can do to make them faster, better and … smaller.
But why bother? Considering that the modern databases grow at such fast pace you may face a situation where you are not able to fit in your maintenance windows or service contract obligations. Let us take a look on the three main areas where we can work on:
Read more »![Ed Pollack](/wp-content/uploads/2015/12/EdwardPollack.jpg)
Removing the risk from important maintenance tasks in SQL Server
January 18, 2016As database professionals, we are often in very close proximity to important processes, data, and applications. While we adopt the mantra of “Do no harm”, many maintenance or reporting tasks that we create carry unseen risks associated with them.
What happens when a drive fills up during daily differential backups? What if an index rebuild job runs abnormally long and interferes with morning processing? How about if a data load process causes extensive resource contention, bringing normal operations to their knees? All of these are planned events, yet can cause considerable disruption to the very processes we are trying to safeguard.
Read more »![Shawn Melton](/wp-content/uploads/2016/01/shawn.jpg)
![](https://s33046.pcdn.co/wp-content/uploads/2016/01/word-image46-624x296.png)
Connecting PowerShell to SQL Server
January 13, 2016Introduction
PowerShell (aka Posh or just PS) is becoming more and more of a tool for operational support and some deployment scenarios. If you need to pull or place data into SQL Server, PS can be a handy way of doing it in both one-off and automated work.
There are a number of ways to connect to SQL Server via PS. In this article I wanted to go over the options that are available to you. If you have used PS for any number of months or years, you know there tends to be multiple ways of performing a task. So it goes without saying if you are building out scripts to use in production to test, test, and test…then test it one more time.
Read more »