Recovery

How to reconnect to a SQL Server instance when all credentials have been lost

March 3, 2017 by

The Problem

I came across a strange and rare situation at a client recently, where they asked me to resolve a problem on Microsoft SQL Server but did not give me any credentials to connect to the system. I asked them to state the actual problem and the reply was “we lost all credentials”. A number of questions started to generate in my mind. How are applications running? The Answer was “The application login is embedded in a DLL and it’s a third party application which we don’t have support”. How can you lose all the SQL Server credentials? The AnswerIt was saved in a file and it was lost and the employee who knew it is no more working with the company”. Anyways, I had no choice but to have a solution for this strange problem.

Read more »
Prashanth Jayaram

SQL Server Database Recovery Process Internals – database STARTUP Command

February 14, 2017 by

A database recovery process is an essential requirement for database systems, It can be a tedious job and the process of recovery varies on lot of scenarios. The desire to improve recovery results has resulted in various procedures, but understood by few and prone to errors. In this article, I’ll illustrate the impact of stopping the database instance in the middle of a large transaction that was running and discuss several techniques and tools that are available for faster and successful recovery.

Read more »
Dinesh Asanka

Point in Time Recovery with SQL Server

December 19, 2016 by

Introduction

How often are you working with multiple environments? For example, if you are a database administrator who is responsible for a production environment as well as another environment, it most likely that you will be working with both environments simultaneously. What is the probability that you will execute a script on production, which actually needs to be executed on the other environment? I would say it is high. To prove this point let me present you an example.

Read more »
Ahmad Yaseen

Database checkpoints – Enhancements in SQL Server 2016

September 21, 2016 by

When a new row is inserted or an existing one is updated in your database, the SQL Server Database Engine will keep that change in the buffer pool in the memory first, without applying each change directly to the database files for IO performance reasons. These data pages located in the buffer pool and not reflected yet to the database files are called Dirty Pages. The SQL Server Database Engine uses a special kind of processes to write these dirty pages to the data and log database files periodically. These processes are called Checkpoints. The Checkpoint creates a mark that is used by the SQL Server Database Engine to redo any transaction that is committed, written to the database transaction log file without reflecting the data changes to the data files due to an unexpected shutdown or crash. Also, this recovery point that is created by the Checkpoint will be used to roll back any data changes associated with uncommitted transaction, by reversing the operation written in the transaction log file. In this way the SQL Server Engine will guarantee the database consistency. The time that is taken by the SQL Server Database Engine to redo and undo the transactions is called the Recovery Time. All information about the Checkpoints will be written to the database boot page to identify till which point the database files are synchronized with the buffer pool when the system wakes up after crash or shutdown.

Read more »
Derik Hammer

Measuring Availability Group synchronization lag

August 9, 2016 by

With all of the high-availability (HA) and disaster recovery (DR) features, the database administrator must understand how much data loss and downtime is possible under the worst case scenarios. Data loss affects your ability to meet recovery point objectives (RPO) and downtime affects your recovery time objectives (RTO). When using Availability Groups (AGs), your RTO and RPO rely upon the replication of transaction log records between at least two replicas to be extremely fast. The worse the performance, the more potential data loss will occur and the longer it can take for a failed over database to come back online.

Read more »
Marko Zivkovic

Temporal tables in SQL Server

July 4, 2016 by

What is a temporal table?

Temporal tables give us the possibility to retrieve data from any point in the past and for every data change (Update, Delete, Merge). With temporal table users can recover data from accidental changes (update/delete) as well as audit all changes to data, etc. Temporary table is a new feature in SQL Server available from SQL Server 2016 CTP2 version.

Read more »
Murilo Miranda

AlwaysOn Availability Groups – How to setup AG between a clustered and standalone instance (Part 1)

April 24, 2015 by

In this article we are going to explore how to configure an Availability Group between a clustered instance and a standalone instance, showing, step-by-step, how to setup a possible Disaster Recovery environment.

Introduced on SQL Server 2012, the Availability Groups brought the expectation to be an improved version of the database mirroring, which will be discontinued soon. The AlwaysOn Availability Groups was improved on SQL Server 2014, giving the capability of have more replicas, better troubleshooting possibilities and improving its availability. Comparing the Availability Groups with the database mirroring, in a very high level, we gained the possibility of have a listener to dynamically redirect the connection to the current active instance and also the capability of distribute the read workload between readable replicas. However, only the primary replica is able to write.

Read more »
Derik Hammer

Disaster Recovery Planning with Always-On Availability Groups

December 17, 2014 by

When I configured my first Always-On Availability Group, I setup a Windows Cluster and started with SQL Server Management Studio’s New Availability Group Wizard, scripting out the steps along the way. This entire process took only a matter of minutes. The minimum required steps for configuring the cluster and getting a database into an Availability Group (AG) are very few. This process, however, is deceptively simple. What some don’t realize is that the majority of work required for setting up an AG needs to occur in the planning phase, before a server is even requisitioned. Let us examine the questions that you should ask before implementing an AG.

Read more »
Ivan Stankovic

What is SQL Server database mirroring?

March 15, 2014 by
SQL Server database mirroring is a disaster recovery and high availability technique that involves two SQL Server instances on the same or different machines. One SQL Server instance acts as a primary instance called the principal, while the other is a mirrored instance called the mirror. In special cases, there can be a third SQL Server instance that acts as a witness Read more »
Ivan Stankovic

What is a SQL Server disaster recovery?

March 14, 2014 by
A SQL Server disaster is an event that causes data loss or any serious SQL Server disruption. Causes of a disaster can be numerous: power failure, hardware failure, virus attack, natural disaster, human error, etc. Some SQL Server disasters cannot be prevented. That’s why a good disaster recovery plan is important Read more »
Ivan Stankovic

Top 10 articles on the SQL Server transaction log

February 13, 2014 by

Understanding Logging and Recovery in SQL Server

In “Understanding Logging and Recovery in SQL Server”, Paul S. Randal has given a complete overview of the transaction log. In four sections he explains: what is logging, what is recovery, how the transaction log works, and which recovery models are available. This article can be used as a starting point in understanding fundamentals of the transaction log and the logging process Read more »