Kevin3NF

SQL 101 – Replication vs. Log Shipping vs. Clustering

Continuing the “Englishification” of SQL Server for those new to the product…

These three terms are the most incorrectly used terms in all of SQL Server, not just by CEOs and pointy haired managers but by some very sharp developers and more than a few experienced DBAs. If you don’t know them, pull up a virtual chair for a 3 minute primer.

Clustering – its all about high availability and uptime

In its most basic form, a Windows/SQL “Cluster” is two or more servers (nodes) attached to a shared storage – a SAN. Only ONE of the nodes is running the SQL Server instance at any given time. So…you can have a 4 node cluster with one SQL Server instance, and it will NOT be running 4 times faster. If the node you are running on suffers a meltdown, the Cluster service moves it to another node. Key Point: This is NOT a fully redundant solution!!! If the SAN dies, your data is gone. Period. Go find your backups.

Log Shipping – move that data!

Log Shipping’s sole purpose is disaster recovery. There is a secondary benefit that you can use the destination server as a reporting server if you set it up in a specific configuration.

LS is nothing more than a glorified backup/copy/restore process with a GUI and jobs:
Backup the data on Server A.
Copy the backup files to Server B
Restore the files on Server B.

You cannot edit the data on Server B…just read it.

Replication (no, I won’t discuss the different types here):

Replication is all about distributed processing. This means having the data in two different places (Walla Walla, WA and Kissimmee, FL for example) so users don’t have to depend on the server and WAN in the remote city. Or for sales/field personnel entering data from their cars.

Replication can be a partial DR solution, but understand that not everything gets replicated (security changes), and only new data gets sent automatically. Schema changes, new tables, etc. do not.

Summary:

• Clustering – High availability is its only purpose.
• Log Shipping – Disaster recovery/possible reporting server
• Replication – Distributed data processing with some DR benefit.
• NONE of these are gonig to increase performance!!!!

Yes, you can combine some of these. Set up two clusters in different cities and Log Ship between them. Now you have HA and DR. Expensive, but effective.

For all the SQL Experts that are chomping at the bit ready to scream that I left out what LUN is, or didn’t discuss Geoclustering, please see the post title…this is a 101 level post 🙂

Thanks,

Kevin3NF

SQL 101 – Understanding Transaction logs

I posted this in the microsoft.public.sqlserver.server newsgroup (response to questions about log file space):

If you build a bookshelf (physical .ldf file) and fill it up with books (transactions), its full.
If you loan 5 books to a friend (backup the t-log), there is space available on the shelf, but the shelf size didn’t change, correct?

If you buy another book (DML transaction), it goes where one of the others was.
If you fill up your bookshelf and then buy 3 more books, your only choices (besides stacking) are to expand the size of the shelf (grow the physical .ldf file) or add a 2nd one (MyDB_Log2.ldf) to the wall. Or return the books (failed transaction).

Chopping off the end of the bookshelf (Shrinking the .ldf file) makes no sense, nor does making a shelf that can hold 1000 books, when you’ll never have more than 100 there….wasted wall space (disk).

Hope that helps.

Kevin3NF

Dateline: Sunday morning, 2:30am (my week on call)…

Ring!!!!!
Kevin3NF: {snort} thisiskevin
Caller: Hey man…sorry to wake you but we have some t-log backups failing for Acme Widgets
Kevin3NF: whatstheerrormessagein the job (waking up a bit, not out of bed)
Caller: I can’t find the job in the SQL Agent job list
Kevin3NF: WhatIsTheExactAlertFromNetIQ? (slightly clearer now)
Caller: SQL Task failed: ‘Backup Job 17’ failed
Kevin3NF: The job list is alphabetical…did you sort by name?
Caller: Yeah…but theres like 2000 jobs…most have numbers and letters and stuff
Kevin3NF: Those are reporting services jobs…just ignore them and look for the Backup job
Caller: I’m looking for Job 17, but its not in the ‘J’s
Kevin3NF: Did you look under ‘B’, since the job’s name starts with ‘Backup…’ ?
Caller: silence
Caller: I’m an idiot
Kevin3NF: Goodnight.
Caller: Click
Kevin3NF: snore.

Still makes me laugh…I went back to sleep knowing I would post this little 2 minute interaction 🙂

Kevin3NF

Why we maintain databases…

I was blissfully unaware of this issue until I came in this morning and saw the debris…

The players: Freakshow and Tiny (my co-workers), the Customer, and the application Vendor.

Customer: Everything is slow and my cluster keeps failing over! Must be SQL Server!
Freakshow: We didn’t change anything on your servers sir…
Customer: You must have!
Freakshow: Could be bad indexes or stats…
Customer’s Vendor: Oracle doesn’t have this issue
Freakshow: Grrrr….
Tiny: Double Grrrrrr…..
Freakshow: You have 49 million rows, and are table scanning/locking…
Customer: We have 3 years of data
Vendor: You should have 90 days, and you are 3 years behind on versions of the application…
Freakshow: Let me update the stats….
Customer: Hey…its working!!!
Vendor: You know that ‘purge’ utility we provide?….it comes with fries 🙂
Freakshow: I’m going back to bed
Tiny: I’m going home (6 hours past end of the shift…)

Moral: Purge your data, keep your versions up, and maintain your indexes folks…