Helpful stored procs:
sp_configure
Displays (or changes) server config settings
sp_configure 'show advanced option', '1';
go
reconfigure
go
sp_configure
go
Will show advanced options, not just the basic ones
sp_createstats
Creates statistics that are used by Query Optimizer for all tables in a database
sp_help
Displays summary information of objects of all types that exist in the current database such as tables, views, functions, and stored procedures
sp_help <object_name>
Displays information about the object_name
sp_helpdb
Returns a list of database names (and information about each)
sp_helpdb <db_name>
Returns additional information about the db_name such as the physical filenames.
sp_helpfile
Returns physical information about the current database such as the logical names, physical file names, and other information.
sp_lock
Displays current locking information for the entire SQL instance
sp_spaceused
Returns used/unused disk space of the current database
sp_spaceused <db_objectname>
Returns space utilization of db_objectname (like a table and its index size).
Also tells the number of rows.
sp_who
Returns a list of current processes that are connected to the SQL instance
sp_who2
Same as above, but more information about each process
(Partially lifted from SQL 2008R2 Unleashed)
Wednesday, April 20, 2011
Monday, April 18, 2011
T-SQL Admin 101: Retrieve list of databases
select * from sys.master_files
Returns database names (including log and ndf files), physical file locations, size, and more.
sp_helpdb
Stored procedure that returns database name, easy-to-read size, and recovery model (full/simple).
select * from sys.database_files
Returns database file names but limited to current database only.
select * from sys.databases
Returns database names, database_id, create_date, snapshot info, recovery info (full/simple), and many other options. This is a good one.
select * from sys.master_files
More oriented toward the physical files, this returns physical filenames and many other options. However, it does not return the database name (just the logical name of each file).
select * from sys.sysdatabases
Returns a list of databases and a few other options. Not as thorough as sys.databases
select * from sys.sysfiles
Returns a list of file names for the current database
Returns database names (including log and ndf files), physical file locations, size, and more.
sp_helpdb
Stored procedure that returns database name, easy-to-read size, and recovery model (full/simple).
select * from sys.database_files
Returns database file names but limited to current database only.
select * from sys.databases
Returns database names, database_id, create_date, snapshot info, recovery info (full/simple), and many other options. This is a good one.
select * from sys.master_files
More oriented toward the physical files, this returns physical filenames and many other options. However, it does not return the database name (just the logical name of each file).
select * from sys.sysdatabases
Returns a list of databases and a few other options. Not as thorough as sys.databases
select * from sys.sysfiles
Returns a list of file names for the current database
Tuesday, April 12, 2011
HADR modes
This page indicates that Denali CTP1 is limited to supporting only a single, asynchronous secondary replica. What other kinds of replicas can we look forward to?
If we look to database mirroring in previous versions of SQL, we can identify both synchronous and asynchronous mirrored database operations. When data is committed synchronously, this means the primary server waits for the secondary server to commit the mirrored transaction before telling the client that the transaction was committed. This is better from a DR perspective because it means that a committed transaction is known to be committed on both nodes. However, the penalty for this high degree of safety is seen in performance. The primary has to wait for the secondary to commit.
When data is committed asynchronously, the primary does not need to wait for the secondary to commit the transaction. This means that the data may be out of sync between the primary and the secondary when a disaster occurs on the primary. In fact, if the secondary is super busy (responding to read-only requests), it might fall behind the primary.
Thus, we need to exercise great caution in choosing which database mirroring mode:
Async = Faster but not as up-to-date ("High Performance")
Sync = Slower but safer ("High safety")
Database mirrors operating in "High safety" mode can also be configured to automatically fail over. If we want this capability, we must install a third SQL server to act as a witness. (Each server gets one vote, the witness breaks ties...).
Recall that CTP1 only supports an async availability group. This means that when we fail over an availability group to the secondary server, we might have data loss. This is why this page indicates that we can force a failover to the secondary, but we must be prepared to accept possible data loss. Hopefully the next CTP or beta will provide a synchronous high availability option.
If we look to database mirroring in previous versions of SQL, we can identify both synchronous and asynchronous mirrored database operations. When data is committed synchronously, this means the primary server waits for the secondary server to commit the mirrored transaction before telling the client that the transaction was committed. This is better from a DR perspective because it means that a committed transaction is known to be committed on both nodes. However, the penalty for this high degree of safety is seen in performance. The primary has to wait for the secondary to commit.
When data is committed asynchronously, the primary does not need to wait for the secondary to commit the transaction. This means that the data may be out of sync between the primary and the secondary when a disaster occurs on the primary. In fact, if the secondary is super busy (responding to read-only requests), it might fall behind the primary.
Thus, we need to exercise great caution in choosing which database mirroring mode:
Async = Faster but not as up-to-date ("High Performance")
Sync = Slower but safer ("High safety")
Database mirrors operating in "High safety" mode can also be configured to automatically fail over. If we want this capability, we must install a third SQL server to act as a witness. (Each server gets one vote, the witness breaks ties...).
Recall that CTP1 only supports an async availability group. This means that when we fail over an availability group to the secondary server, we might have data loss. This is why this page indicates that we can force a failover to the secondary, but we must be prepared to accept possible data loss. Hopefully the next CTP or beta will provide a synchronous high availability option.
Friday, April 1, 2011
HADR, HADRON
HADR is the new hot DR model in SQL 2012. It looks to answer some of the complaints I had about database mirroring. I'll adress those complaints as the need arises. For now, a quick overview of HADR.
HADR = High Availability, Disaster Recovery. Sometimes it is called HADRON, I guess ON = "Always on".
With a typical active/passive failover cluster, SQL has high availability but no disaster recovery. This is because there is only a single copy of the database shared by both nodes. If the active node fails, the passive takes ownership of the database. The client communicates with the "instance" of SQL that runs on the cluster - it does not need to know that node2 now runs the instance.
SQL 2008 brought database mirroring which was a way to do DR. The client connection could be changed to connect to both nodes. If node1 failed, the client could redirect queries to node2.
A "HADR" solution combines High Availability with Disaster Recovery to make a sort of "super" database mirror.
From books online, "Deploying HADR involves configuring one or more availability groups. Each availability group establishes the context within which a selected set of user databases can fail over as a single unit." In other words, I can combine a couple of databases in the same availability group so they fail over together. Note, however, that this high availability solution still works at the database level and not at the instance level.
In my simple scenario involving two nodes, we identify the two nodes as failover partners known as availability replicas. HADR is different than an active/passive failover cluster because each availability replica houses a copy of the availability group - the set of user databases that we are trying to protect. In my simple scenario, node1 maintains the primary copy of the user databases and allows users to read/write to them. Node1 is called the primary replica. Node2 houses a duplicate set of these databases and is known as the secondary replica.
So how is this different from database mirroring?
For starters, there were no availability groups with database mirroring. This meant I could run into weird failover situations where some of the user databases were on node1 and others had failed over to node2. In applications that needed to update both databases (yes, they do exist, and yes, "ick!"), this would cause some problems because the application would not know which server to use.
Another difference is that the secondary replica allows read access so that reports can be run from the secondary. In the past, this was possible, but we had to set up a database snapshot. If I understand SQL 2012 correctly, this means we can have reports run from data that is almost up-to-date and we don't have to mess with snapshots which are frozen at whatever time they were created. I suspect there are performance considerations if we run a lengthy report from the secondary. For example, what happens if we bog down the secondary so much that it is unable to pull data over from the primary? If that happens and the primary fails, we have fallen behind in our DR copy of the data.
HADR is also better than database mirroring in that we can now have more replicas. In the past, we were limited to one; that limit is now increased to four. I do not know if they can be time delayed. For example, it might be nice to have a replica that is immediately up-to-date and another that is two hour delayed. For example, if a developer were to truncate all the tables in a database, those changes would be immediately applied to the up-to-date standby. Wouldn't it be nice to have a two-hour delayed database available?
HADR claims to do faster client failover and redirection.
Database mirroring had several different availability modes. In a future article I will take a look at what kind of availability modes are available in HADR.
More to follow.
HADR = High Availability, Disaster Recovery. Sometimes it is called HADRON, I guess ON = "Always on".
With a typical active/passive failover cluster, SQL has high availability but no disaster recovery. This is because there is only a single copy of the database shared by both nodes. If the active node fails, the passive takes ownership of the database. The client communicates with the "instance" of SQL that runs on the cluster - it does not need to know that node2 now runs the instance.
SQL 2008 brought database mirroring which was a way to do DR. The client connection could be changed to connect to both nodes. If node1 failed, the client could redirect queries to node2.
A "HADR" solution combines High Availability with Disaster Recovery to make a sort of "super" database mirror.
From books online, "Deploying HADR involves configuring one or more availability groups. Each availability group establishes the context within which a selected set of user databases can fail over as a single unit." In other words, I can combine a couple of databases in the same availability group so they fail over together. Note, however, that this high availability solution still works at the database level and not at the instance level.
In my simple scenario involving two nodes, we identify the two nodes as failover partners known as availability replicas. HADR is different than an active/passive failover cluster because each availability replica houses a copy of the availability group - the set of user databases that we are trying to protect. In my simple scenario, node1 maintains the primary copy of the user databases and allows users to read/write to them. Node1 is called the primary replica. Node2 houses a duplicate set of these databases and is known as the secondary replica.
So how is this different from database mirroring?
For starters, there were no availability groups with database mirroring. This meant I could run into weird failover situations where some of the user databases were on node1 and others had failed over to node2. In applications that needed to update both databases (yes, they do exist, and yes, "ick!"), this would cause some problems because the application would not know which server to use.
Another difference is that the secondary replica allows read access so that reports can be run from the secondary. In the past, this was possible, but we had to set up a database snapshot. If I understand SQL 2012 correctly, this means we can have reports run from data that is almost up-to-date and we don't have to mess with snapshots which are frozen at whatever time they were created. I suspect there are performance considerations if we run a lengthy report from the secondary. For example, what happens if we bog down the secondary so much that it is unable to pull data over from the primary? If that happens and the primary fails, we have fallen behind in our DR copy of the data.
HADR is also better than database mirroring in that we can now have more replicas. In the past, we were limited to one; that limit is now increased to four. I do not know if they can be time delayed. For example, it might be nice to have a replica that is immediately up-to-date and another that is two hour delayed. For example, if a developer were to truncate all the tables in a database, those changes would be immediately applied to the up-to-date standby. Wouldn't it be nice to have a two-hour delayed database available?
HADR claims to do faster client failover and redirection.
Database mirroring had several different availability modes. In a future article I will take a look at what kind of availability modes are available in HADR.
More to follow.
Subscribe to:
Posts (Atom)