Feeds:
Posts
Comments

Archive for the ‘Matrix’ Category

Since Sql Server 2005, High Availability (HA) has become very easy to implement in a Sql Server environment. Also, starting Sql 2005, there are 4 methods to implement HA.

  • Failover Clustering
  • Database Mirroring
  • Replication
  • Log Shipping

Though, Failover clustering still is the most popular option, my guess is the other options might make it a more level field going forward.

In the following matrix, I compare some features of each HA option in one table. This gives a good comparison picture of the features available in each option.

Property \ Feature FailoverClustering DB Mirroring Replication Log Shipping
Server Level Yes No No No
Database Level No Yes Yes Yes
Automatic Failover Yes Yes (Only HA Option) No No
Automatic Failure Detection Yes Yes (Only in HA option) No No
Hot, Warm or Cold standby Hot Hot and Warm Options Warm Warm
Server Level Objects Failover Yes (Automatic) Manual Manual Manual
Is data on Failover available for queries while the Primary server is running? No Yes (use Database Snapshots) Yes Yes (Standby Mode)
Are changes allowed at Failover location? No No Yes (Merge and TRN Replication with updating subscribers) No
Is Main and Failover server in a 1-1 relationship? Yes Yes Not Required Not Required
Is 1-Many relationship possible between the Main and Failover servers? No No Yes Yes
Is Many-1 relationship possible between Main and Failover server? No No Yes No
Is Automatic Application redirection possible after failover? Yes Yes No No
Recovery model required for the participating databases? Any FULL ANY FULL & BULK
Can you filter what data is sent to the Failover server? No No Yes No
Where does the logic HA reside? OS + Sql Sql Replication Agents (they run outside Sql Server Sql Agents (Run outside Sql Server)
If you want a reports server, what is the best solution? Not Possible Works, but needs constant refresh of the Snapshot database for latest data. Best Option. Works, but users connections need to be kicked out, when new log is applied.
Best solution if the Main and Failover server are separated by long distance? Not good. Works, but not recommended. Works, but with high latency Best Option. (Log compression reduces latency significantly.

Note: Please know that this is not all the features, these are just he ones I could think of off the top of my head. If you add your comments below, I can add them to the list.

Hope it helps,

_Sql Times

Advertisements

Read Full Post »

In previous two posts, (comparison with HA and other features) we looked at the new features compatibility with other existing features like Replication, Database Mirroring, Database Snapsho, etc.. Now we look at their compatibility with each other.

  • Can you configure FILESTREAM filegroup on a database set of Compression?
  • Is Compression allowed on a database encrypted with TDE?
  • Can you create FILESTREAM filegroups on a database encrypted with TDE?

Lets look at the matrix below for answers:

Compression

FILESTREAM

TDE

Compression

1

NTFS Only

N

FILESTREAM

NTFS Only

1

N

TDE

N

N

1

As you can see, TDE is not compatible with either Compression or FILESTREAM. A database set of Compression cannot be encrypted with TDE and vice versa. Since Compression requires Disk Cluster size to be 4096 bytes you cannot compress FILESTREAM data. For FILESTREAM data, the recommended Disk Cluster is 64K. I would recommend any one implementing FILESTREAM to read Paul S. Randal’s (SQLskills.com) article on MSDN

“The recommendation for using FILESTREAM is that the individual units of BLOB data be 1 MB in size or higher. If this is the case, it is recommended that the NTFS cluster size for the FILESTREAM data container volume is set to 64 KB to reduce fragmentation. This must be done manually as the default for NTFS volumes up to 2 terabytes (TB) is 4 KB. This can be done using the /A option of the format command. For example, at a command prompt type:

          format F: /FS:NTFS /V:MyFILESTREAMContainer /A:64K

Why is it this way and all other implementation details will be covered in future articles.

Hope this helps,
_SqlTimes

Read Full Post »

In the previous post we saw the compatibility of new features like TDE, FILESTREAM and Compression with High Availability (HA) options. Now we continue the compatibility matrix with other existing features.

 

Compression

FileStream

TDE

Database Snapshot

Y

No

Y

Full Text Indexing

Y

Y

Y

Note: Sql Server does not support Database Snapshot for FILESTREAM filegroups. But you can create Database Snapshot for the rest of the non-FILESTREAM filegroups. If you try to query FILESTREAM data from the Database Snapshot you get the following error.

Could not continue scan with NOLOCK due to data movement.

When you look at any set of features compared in a matrix like this, it gets easier to remember.

In future, I will try to add details about each matrix cell.

Hope this helps,
_SqlTimes

Read Full Post »

Some of the new features in Sql Server 2008 are very useful (and long over due). Features like

  • Compression
  • FileStream
  • TDE (Transparent Data Encryption).

Though they have their own limitations, they are very useful when available natively.

Here we’ll look into their compatibility with existing High Availability (HA) features.

  • FailOver Clustering
  • Database Mirroring
  • Replication
  • Log Shipping

Lets start with the matrix, and then we’ll go into details:

Compression

FileStream

TDE

FailOver Clustering

Y

Y

Y

Database Mirroring

Y

No

Y

Replication

Y

Y

Y

Log Shipping

Y

Y

Y

When you look at it this way, it makes it much easier to remember all the options. Of all, as you can see, only one stand out i.e. FileStream is not configurable in Database Mirroring. All the other configurations are fairly well supported. Please know that there are, in fact, some limitations for these features in each HA implementation (but, that is for an article some other day).

Hopefully, future versions of Sql Server will support FileStream in Database Mirroring.

In future, I will try to add implementation details about options in each cell.

Hope this helps,

_SqlTimes

Read Full Post »