Category Archives: AlwaysOn

Online Restores

I was doing some mentoring today with online restores, so I thought I would share it with my blog readers.  The most important thing about online restores is that it is only supported in SQL Server 2005 and later versions.  In addition, for the bulk-logged recovery model the following conditions must be met in order to complete an online restore:

  1. All of the log backups must have been taken and completed before you start the online restore.
  2. If you have bulk changes in the database, the files must be online.
  3. All of your bulk changes must be backed up before starting the online restore process.

In addition, I have seen it recommended that you switch your bulk-logged recovery model to the full model before starting the online process and then switching it back afterward for smoother recovery.  Enjoy!


Random Mirroring Facts

Today is a hodge podge of facts about mirroring, since I am in learning mode I thought I would share with you.

  • Mirroring is deprecated, meaning it is going away and moving to AlwaysOn availability groups.  Now is probably not the time to devise a mirroring plan.
  • You can mirror some or all of your databases on a particular instance.
  • You can only mirror databases in the FULL recovery model.
  • Mirroring supports only one principal and one mirror.
  • All versions support witness modes but only Enterprise supports high-performance mode.
  • Enterprise, Standard and the new BI version all support the high-safety mode.
  • You cannot mirror a database that is using FILESTREAM file groups.
  • You can only mirror user databases.


Replication Series, Part Five: Creating a Snapshot Replication


Figure 1 – New Publication

Continuing our series on replication, today we look a little deeper at Snapshot Replication.  After you have ensured that replication is installed on both nodes, open the replication node in SQL Server management studio on the node that you want to be the publisher and right-click Local Publisher and select New Publication (See Figure 1).

This will open the New Publication Wizard.  Select Next if you are presented with a starting page.  Choose the defaulted server which should be your local server and select Next.

Now we are prompted for the snapshot folder.  This is the network share that needs to have permissions enabled for the publisher and subscriber.

Next we will choose the database that we wish to replicate and then select Next.  Then we will select the Snapshot publication type as shown in Figure 2.

Figure 2 - Replication Type

Figure 2 – Replication Type

Then we will select the articles or tables and views that we wish to replicate.  Here, I have selected all tables and views in Figure 3, but you can drill down and select only certain ones if you prefer.  The next screen indicates if there are any issues and how to correct them.


Figure 3 – Select Articles

Now we must setup the agent and create the snapshot so that we may initialize the subscription as shown in Figure 4.  Select the Change button to set up your scheduled agent times to generate your snapshot.

Figure 4  - Snapshot Agent

Figure 4 – Snapshot Agent

On the final page of the wizard, we must select an account that will run the agent. You can run this under a Windows account or the SQL Server Agent service but I would not recommend that as it is not best practices.  After you name the publication you will see a status screen as your publication is set up by SQL Server.  You’re done!

Stay tuned and enjoy!

Replication Series, Part Four: Snapshot Replication

Continuing our series on replication, today we look at Snapshot Replication.  When is the best time to use snapshot replication?

  1. Your data changes infrequently or you have a set of static tables such as lookup tables.
  2. You have a small amount of data with which to replicate.
  3. You make a great deal of changes over short periods of time.  For example, you have a database that only gets updated once a year like for open enrollment.

One of the caveats to snapshot replication is the snapshot folder permissions.  The replication agents must have the appropriate permissions to read and write to the snapshot folder.

Another caveat is that you can only compress the snapshot if it is less than 2GB in size.

Stay tuned and enjoy tomorrow as we configure the snapshot replication!

Replication Series, Part Three: Preparation

Continuing our series on replication, today we look at the steps to prepare for replication found within SQL Server.

The first step is to ensure that you have replication installed on each of the nodes that you wish to use replication.  One good way to determine if you have it installed is to run the Installed SQL Server features discovery report from the tools menu of the SQL Server Installation Center.  If you do not see the feature installed then you will need to install it.

Figure 1 - Discovery Report

Figure 1 – Discovery Report

Stay tuned and enjoy!

Replication Series, Part Two: Replication Types

Continuing our series on replication, today we look at the different types of replication found within SQL Server.

Within the replication realm we have four different forms of replication:

  1. Transactional replication: transactions are updated as soon as they happen (near real time) between the publisher and subscriber.
  2. Snapshot replication: replicates data at a point-in-time, typically used for data that does not change often.  Completes refreshes of data entirely instead of incrementally.
  3. Peer-to-Peer replication: similar to transactional replication, replicates data to multiple server instances or nodes.  Changes occur as they happen and allows for data redundancy.
  4. Merge replication: similar to transactional replication, but changes are made via synchronization triggers when the nodes are reconnected.

Stay tuned and enjoy!

Replication Series, Part One: The Beginning

Fresh back from a week of intense training in the Immersion Event Two, I thought I would start a new learning series on replication.  Today we will start with the basics, definitions.

  1. Publisher: A database instance that publishes data to other instances.
  2. Subscriber: A database instance that receives publications.
  3. Article: A published object which can be a table, view, user-defined function, indexed view, or stored procedure.
  4. Publication: A collection of articles to be published.
  5. Distributor: A database instance that manages the distribution between publisher and subscriber.
  6. Local Distributor: A distributor located on the same LAN as the publisher.
  7. Remote Distributor: A distributor located on a remote network from the publisher.
  8. Agent: A service enabling replication tasks such as publishing, distributing, and subscribing.

Stay tuned and enjoy!

%d bloggers like this: