Apr 29

AlwaysOn Multi-Subnet Configuration

There are several ways to configure AlwaysOn, and even more ways to configure networking behind the cluster. A very common configuration I’ve setup and seen in production environment is a Active site and Passive disaster recovery site. In this scenario the cluster configuration needs the ability to bring the AlwaysOn resource online, even if it will never have the need to actually do that. Because of this you will need to assign an IP address from all subnets to the AlwaysOn group. This can potentially cause problems with DNS if all addresses are registered to the CNAME. There are some steps to overcome this here. In summary this is what action I take on my clusters:

  1. Open PowerShell with “Run as Administrator”
  2. Run command to list all clustered resources. We are focused on the AlwaysOn listener name:

  3. Run the following command to unregister the IP address that are not in use:
  4. Restart the Availability Group clustered resource.
Apr 25

Failover Clustering Tools Install Failure | Object reference not set to an instance of an object

There have been several times I’ve attempted to install Failover Clustering Tools on a server and received the following error: “Object reference not set to an instance of an object”

Here are the steps I’ve taken in the past to correct this:

  1. Open PowerShell with “Run as Administrator”
  2. Run this command:
  3. Reboot

Additional instructions can be found here.

Dec 01

Backup Restore Progress

I cannot take credit for this script, but I use it all the time:

 

Source

Nov 18

Group Managed Service Accounts (gMSA) for SQL services

How to run SQL services using a Group Managed Service Account (gMSA). The process is very similar to using a Managed Service Account, here are the steps to get this up and running:

  1. Open a PowerShell prompt with “Run as Administrator”.
  2. Run the following commands:
  3. Lastly in PowerShell, run this command (replacing ACCOUNTNAME with your Group Managed Service Account):
  4. Open SQL Server Configuration Manager and select SQL Server Services.
  5. Open the Properties of the service you wish to edit.
  6. Under the Log On tab, Browse or type in the gMSA name. The gMSA should end with a $. The password field should remain blank:
  7. A prompt will appear asking for confirmation. Select Yes.
Feb 27

Definition of Transaction (ACID)

Just a quick bit of knowledge:

In the context of a transactional database, regardless of type (MSSQL, Oracle, MySQL, etc.), the following rules must apply to any transaction:

  1. Atomic: the entire transaction must be committed. If a single part of the transaction fails the entire transaction must fail.
  2. Consistent: each transaction that is committed to the database must not violate any keys, relations or constraints. In other words, a transaction in not allowed to leave a database in an inconsistent state.
  3. Isolation: a transaction must be isolated from any other transaction running at that time. Transactions running concurrently must be executed as if they were sequential. If two transactions can/need to run concurrently they will need to operate isolated from the data that the other is manipulating.
  4. Durable: once a transaction is committed it must become a permanent part of the database.  It must be stored on a medium that can be powered off and the data will remain (i.e. Hard Disk, SSD, SAN, etc.). Durable also means that a confirmation of the committed transaction is provided.
Feb 13

Difference Between ALTER DATABASE WITH ROLLBACK IMMEDIATE and WITH NO_WAIT

For quite some time I didn’t know the difference between ROLLBACK IMMEDIATE and NO_WAIT when altering a database. These are similar in nature, but perform very different tasks. Lets look at the differences:

  • ROLLBACK IMMEDIATE
      Specifies whether to roll back after the specified number of seconds or immediately
  • NO_WAIT
      Specifies that if the requested database state or option change cannot complete immediately without waiting for transactions to commit or roll back on their own, the request will fail.

If at first glance you still don’t understand the difference don’t fret…I didn’t either. Here is the major difference:

ROLLBACK IMMEDIATE will take all uncommitted transactions and running queries and roll them back immediately. NO_WAIT is a more polite method; it will wait for transactions to commit or roll back and running queries to complete before the ALTER DATABASE statement executes.

You can read more about the differences on Microsoft’s MSDN site:
http://msdn.microsoft.com/en-us/library/bb522682.aspx.
Definitions above are taken from there.