Database Reference
In-Depth Information
■
even though SQL Server does not prevent you from creating hundreds or even thousands CheCK constraints
per table, you should be careful about doing just that. an extremely large number of CheCK constraints slows down query
optimization. Moreover, in some cases, optimization can fail due to stack size limitation. With all that being said, such an
approach works fine with a non-excessive number of constraints.
Note
Tiered Storage and High Availability Technologies
Even though we will discuss High Availability (HA) Technologies in greater depth in Chapter 31, “Designing a High
Availability Strategy,” it is important to mention their compatibility with Tiered Storage and data movement in this
chapter. There are two different factors to consider: database files and filegroups management and data movement
overhead. Neither of them affects the SQL Server Failover Cluster, where you have a single copy of the database.
However, such is not the case for transaction-log based HA technologies, such as AlwaysOn Availability Groups,
Database Mirroring, and Log Shipping.
Neither of the High Availability technologies prevents you from creating database files. However, with
transaction-log based HA technologies, you should maintain exactly the same folder and disk structure on all nodes
and SQL Server must be able to create new files in the same path everywhere. Otherwise, HA data flow would be
suspended.
Another important factor is the overhead introduced by the index rebuild or
DBCC SHRINKFILE
commands.
They are very I/O intensive and generate a huge amount of transaction log records. All of those records need to be
transmitted to secondary nodes, which could saturate the network.
There is one lesser-known problem, though. Transaction-log based HA technologies work with transaction log
records only. There is a set of threads, called
REDO threads
, which
asynchronously
replay transaction log records and
apply changes in the data files on the secondary nodes.
■
even with synchronous synchronization, available in alwayson availability groups and Database Mirroring, SQL
Server
synchronously
saves (hardens) the log record in transaction logs only. the reDo threads apply changes in the
database files
asynchronously
.
Note
The performance of REDO threads is the limiting factor here. Data movement usually generates transaction log
records faster than REDO threads can apply the changes in the data files. It is not uncommon for the REDO process
to require minutes or even hours to catch up. This could lead to extended system downtimes in the case of failover
because the database in the new primary node stays in a recovery state until the REDO stage is done.
You should also be careful if you are using readable secondaries with AlwaysOn Availability Groups. Even though
the data is available during the REDO process, it is not up to date and queries against primary and secondary nodes
will return different results.
■
Note
any type of heavy transaction log activity can introduce such a problem with readable secondaries.
You should be careful implementing Tiered Storage when transaction-log based HA technologies are in use.
You should factor potential downtime during failover into availability SLA and minimize it by moving data on an
index-by-index basis, allowing the secondaries to catch up in between operations. You should also prevent read-only
access to secondaries during data movement.
