MySQL Maintenance

My main focus is Microsoft SQL on Windows.  I was playing with MySQL and want to share MySQL equivalents to common functions in MS SQL.

Back up all MySQL databases:
$ mysqldump --user=root --password=whatever --result-file=backup_file.sql --all-databases

Look into the "lock-tables" option.
Also look into mysqlhotcopy
Also look into the log-bin option to get something like transaction logs.

For data integrity checks, look into mysqlcheck and myisamchk (is there something similar for Innodb?)

For optimization:
Index defrag - mysqlcheck --optimize -A -u username -ppassword

Mouse Without Borders

I used Synergy for several years to run multiple computers & monitors with a single keyboard and mouse.  I stopped because it had trouble with beta (unreleased) Windows products.  Here's one from Microsoft that seems to work better in a Microsoft-only (not cross platform) environment:

http://www.physorg.com/news/2011-09-no-borders-mouse-screens.html

T-SQL Admin 101: Helpful stored procedures

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)

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

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.

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.

Qualcomm Mirasol Display

Keep an eye on this tech:  Qualcomm Mirasol Display

iPads are awesome.  I love the OS and the video is great for playing games & watching movies.  However, as an e-reader, the Kindle is better.

The Amazone Kindle has the best display for reading, but its operating system is not so great for web browsing and add-on apps.

What I'd really like is a color Kindle screen on the iPad.

This tech might provide that.

Addendum: 2011-04-18

Apple Insider reports on a patent filed by Apple that may solve my text reading complaint. The patent involves layering an e-ink display over the top of the typical color display; the operating system dynamically figures out which one to use for the data displayed. More info here: http://www.appleinsider.com/articles/11/04/07/apple_exploring_hybrid_e_ink_lcd_displays_with_independent_regions.html

SQLServerpedia

http://sqlserverpedia.com/

Run by Quest Software, this site is a Wikipedia-style collection of SQL articles.

Concerns about multi-subnet failover clustering

According to Microsoft’s Denali books online, a multi subnet failover cluster is a cluster in which each node of the cluster can be located in different subnets.  In my case, I want a two node active/passive failover cluster, with each node in a different geographic location.  This is known as a stretch cluster.  Reading through Microsoft’s documentation, I noticed this point:

As there is no shared storage that all the nodes can access, data should be replicated between the data storage on the multiple subnets.

In our previous implementation, we replicated the data between the two locations using an EMC-based hardware mirror at the SAN level.  I was naively hoping that Microsoft's implementation would own the replication when I read the statement that followed:

With data replication, there is more than one copy of the data available. Therefore, a multi-subnet failover cluster provides a disaster recovery solution in addition to high availability.

After reading through various documents, I get the impression that Microsoft still expects us to use third party technology to replicate the data between the sites.  For example, the graphic displayed by this guy shows "SAN Replication".  For now, I guess we will continue to use EMC's SAN-level replication.  It might be worth checking into SteelEye -- at last year's TechEd conference, a Microsoft rep pointed me in their direction for a software-based mechanism that could do the necessary replication.  Oh well, at least our network guys will be happy about doing away with the stretch VLAN.

Other thoughts about multi-site clustering --

A few paragraphs later, Books Online gives as an example exactly what we plan to do:

SQL Server failover cluster SQLCLUST1 includes Node1and Node2. Node1 is connected to Subnet1. Node2 is connected to Subnet2. SQL Server sees this configuration as a multi-subnet cluster and sets the IP address resource dependency to OR.

This bit about setting the IP address resource dependency to OR is rather intriguing and points to one of the changes in SQL 2012.  It seems that the IP addresses are not owned by all the nodes in the failover cluster.  The IP address resource dependency can be set to either “OR” or “AND”.  Books online offers several examples where we would use “OR” as well as an example when we would use “AND”.  Of particular interest is the following example consistent of THREE nodes:

SQL Server failover cluster SQLCLUST1 includes Node1, Node2, and Node3. Node1 and Node2 are connected to Subnet1. Node 3 is connected to Subnet2. SQL Server sees this configuration as a multi-subnet cluster and sets the IP address resource dependency to OR. Because Node1 and Node2 are on the same subnet, this configuration provides additional local high availability.

This example raises another concern – that of DNS latency issues.  In the example above, recall that both nodes 1 and 2 are in the same subnet; node 3 is on a different subnet.  If SQL fails over to node 3, a DNS record must be updated to point to the new IP address on node 3.  Books online indicates that this could be a problem if we have multiple DNS servers (think DNS synchronization issues).  My real concern is this statement:

The SQL Server cluster will not come online on Node3 until the DNS synchronization is complete.

So I guess this is why some shops choose to go with three nodes in a cluster instead of our typical two.  It's something that I will have to consider going forward.

One other concern – be aware that multi-subnet failover clusters are only supported on SQL Datacenter, Enterprise, Developer, and Evaluation editions.  Notice what is missing – Standard (and Workgroup).  Bummer.

SQL 2012 - Books online

Books online for Denali (SQL 2012) can be found here:

http://msdn.microsoft.com/en-us/library/ms130214%28v=sql.110%29.aspx

Incidentally, any recommendations for a good e-reader?  I would like to use a tablet-style reader to view Microsoft's books online.  Given my collection of iOS devices, an iPad is the obvious first option but I am concerned about eye fatigue with the iPad's glossy screen.  I previously had an original iPad and - while I loved the device - I was never convinced it was a good e-reader.

Amazon's "E Ink" screen could solve the eye fatigue problem but I don't know how well the Kindle's "experimental" web browser would process Microsoft's books online.

And no, reading off the computer screen is not appealing in this case - neck strain, eye fatigue.  I want to use my bifocals and read this like a book!

Suggestions?

Why is multi-subnet failover clustering important?

My network team did not like the way SQL 2008 R2 and previous versions of SQL handled failover clustering.  We were comfortable with setting up active/passive failover clusters and were attempting to place a second node in a disaster recovery site located half a mile or so away from our primary.  By mirroring the SAN storage, we had a good way to set up a simple active/passive failover cluster that would be usable in case of a disaster.

However, previous versions of SQL did not support setting up the second node in a subnet different than the first.  We could still set up a second node in the DR site by extending the subnet between the two physical locations, but this was a work-around -- not ideal according to our network engineers.  It required stretching a V-LAN across locations to expose a single IP address across the two sites.

The key feature that was missing was multi-subnet failover clustering.  With Denali's multi-subnet failover clustering, we can now configure SQL Server failover clusters across multiple sites to create stretch clusters without implementing stretch V-LAN technology.

This is why I am so excited to see this feature in SQL 2012.  In future posts, I will investigate to see if this feature solves our problem.

Geekstorm.net

My old website --

http://geekstorm.net/stuff.html

At Geekstorm you will find a list of stuff that I find useful. Utilities, apps, product recommendations, and the like.

SQL 2011 - What's new?

Currently studying the next version of SQL code named Denali.  Potential release date in July of 2011, so I'll call this SQL 2011. (Edit: It was delayed and is called 2012....)

What's new?

• Contained databases - This is supposed to make it easier to move databases between servers
• HADRON - New High Availability & Disaster Recovery options
• Support for Server Core - Look ma, no GUI!
• A bunch of changes in SSMS that I don't really understand yet
• Column-based query accelerator
• Multi-Subnet Clustering
• Itanium no longer supported

These are just the highlights.  I'll drill into each one of them (plus much, much more) in the following posts.

Hello World

Over the years, I found the best way for me to grok a new technology was to leave myself a trail of bread crumbs.  I need to play with it, read about it, immerse myself in it, and write about it.  Many moons ago, I used to make my notes in HTML with notepad or vim.  Maybe this Blog interface will be easier... by making notes here, I hope to leave a virtual path that I can later follow to re-learn those things that I forgot.  Theseus had a ball of string.  Hansel had bread crumbs.  These are mine...