Blog

Mar 03
Test

When I create a blog post, it should be posted on Twitter and Facebook.

Feb 14
SQL 2014 and Backing Up to Azure

SQL 2014 Backup to Azure.JPG

I have been watching some of the SQL 2014 courses over at Microsoft Virtual Academy.  The first video of the "Platform for Hybrid Cloud with SQL Server 2014 Jump Start" series talks about "Backup to Cloud".  Now, if you're like me, you treat "The Cloud" with suspicion, especially when it comes to SQL Server.  How can you guarantee performance?  How do you keep your data safe?  Doesn't it cost an arm and a leg?

But the idea of backing up your databases to an offsite location is still a compelling case, because you have to plan for the worst case.  If a fire could destroy your company's office, your data will go up in smoke too, and there goes your livelihood.  Many companies use the very manual method of a staged backup (backup to share, then back up the share to a tape) which you then take offsite.   But tapes wear out, people forget to do it, or someone stores the tapes next to a magnet... then you're back in the same boat. 

A better, automated way, would be to use a service that will consistently back up your databases in a way that makes it easy to retreive the data if needed.

Full disclosure, I work for a tech company that offers this kind of service.  We call it "Cloud DataSafe", which backs up entire servers, files and databases. Click here if you want to know more.

In SQL Server 2012, as of Cumulative Update 4 (for Service Pack 1), you can now "Back up to URL" in addition to specifying a tape drive and a file share location.  The URL is, of course, an HTTPS endpoint for the Azure Blob Storage cloud.  Currently, you have to use TSQL or PowerShell to provision the storage, set credentials, specify a container in the cloud, and then trigger the backup.  All scriptable, of course.

The Azure Blob Storage subscription seems pretty reasonable: you get 5GB free per month, with $.050 per each additional gigabyte.  For a small business this might be just about right. 

When SQL 2014 is released, this Backup to URL capability will be available in the GUI. 

But what if you aren't using SQL 2012 or 2014?  Well, Microsoft has a free tool called "SQL Server Backup to Azure Tool", which installs itself as a service.  The way it works is, you back up your databases to a file path, and the tool will automatically grab the backups, compress them, encrypt them, and stream them up to Azure. 

If you wanted, you could just compress/encrypt and then save them back to your file system. 

Another interesting feature is the Managed Backup capability: this is where you set up a plan to back up to URL.  The job only runs when data has actually changed, thus simplifying backups and restores.  This is essential for a cloud backup, where you might otherwise be streaming gigabytes every day.  So rather than having these regimented backups (e.g. Daily full + Houly transaction logs), the backup only runs if transactions have occurred.  Similarly the restore process simply selects the backup set, and gives you a timeline view to choose your restore point. 

If you'd like to learn more (and why wouldn't you?) then you should check out these free SQL 2014 courses at MVA:

 

Jan 11
Video for 1-9-14 Meeting is Online!

I've posted the recording for the January 2014 user group meeting (embedded below).  ​The topic was Introduction to High Availability in SQL Server, which ties in to the blog post I wrote earlier.  After the presentation, there was a Q&A, but the audio didn't turn out very well (it just sounded like I was talking to myself).  I've edited that part out. 


Jan 01
Optimizing SQL Server in a VM

While I was on vacation, I did some training on SQL Server (doesn't everyone spend their vacation that way?) -- In my experience, virtual SQL can run very well (and very reliabily) but there is an extra layer of complexity involved.  But I find it fascinating.

SQL MVP Denny Cherry tells you what you need to know about virtualizing SQL Server.   

The video below is from TechEd 2012 but it's more or less the same lecture:

 

Click here if the video doesn't embed properly for you.

Want more? SQL MVP Brent Ozar, who also covers best practices for virtualizing SQL Server, here:

http://www.brentozar.com/sql/virtualization-best-practices/

Here's a Q&A he did in May 2013 on the subject:

 


You might also be interested in shelling out a little money for this course on SQL virtualization, over at PluralSight:

SQL Server Virtualization - Jonathan Kehayias
Learn how to properly configure, control, and monitor virtual machines, and troubleshoot problems when running SQL Server in a virtualized environment using VMware or Hyper-V, applicable for DBAs, VM admins, and any other IT admins responsible for SQL Server and/or virtualization

PluralSight is high-quality training, and it starts at $29/month, which grants you access to their entire library. 
Dec 07
Alaska SQL User Group Meeting Video is online

Hi All,

The session video for the 12/5/13 meeting is now posted on Youtube.   View below:

 

Dec 05
Code and PDFs for SQL Server Security Session

Update 5:28PM: The link is fixed.

Update 11:57AM: The link isn't working yet...Greg is working on the problem.​

I've uploaded the code files and PDFs that I showed and talked about at the inaugural Alaska SQL Server user group meeting on 12/5/2013 in Anchorage. It contains way more code than I showed, as well as some articles and course materials that I've development for Learn Now Online (formerly AppDev). 

You can find the files here.

Thanks for letting me visit the group!

​Don Kiely

Nov 02
Some HA Considerations

Sooner or later you're going to get to the point where your data is so valuable you're never going to want it to go offline. (Well, maybe that's not entirely accurate. You might work in a place where the data is disposable or a low priority for backup or disaster recovery.) But most employers who hire an actual DBA are going to be those who own valuable data and are very concerned about losing it. That's where High Availability comes in, which is closely associated with plans for Disaster Recovery, which are collectively called "HADR". HADR lives under the umbrella of Business Continuity. This post isn't going to cover those in detail, but let's dip our toes into the water with High Availability.   

Recently a customer asked me about backup and High Availability options for SQL Server. They own SQL 2008 R2 Standard Edition. They'd heard about some cool things in SQL 2012, such as Backup to Azure and Availability Groups, but they were locked in to SQL 2008 R2 because of a vendor solution.

SQL Server has lots of options for HADR, but Microsoft, of course, saves the best bits for the Enterprise Edition. Standard isn't bad, but there are limitations.

I wrote the following:

SQL 2012 backup to Azure is brand new and can be done via SQL scripts or PowerShell.  I think I'd hold off until full support (via the SQL Management Studio) arrives.  I'll be testing it myself.

Clustering has a strict set of requirements for redundancy in hardware, shared storage, Active Directory, and configuration.  I've set it up successfully in lots of places, though.  SQL 2008 R2 Standard supports 2 nodes.

One baby step you might consider is Database Mirroring.  In SQL 2008 R2 Standard, you can create a synchronous mirror of a database in a second SQL instance, with all transactions streamed to the standby database.  If the primary database goes offline, the secondary can come online within a matter of seconds (this requires a third "witness" server). 

 

In order for the client to automatically fail over to the mirror server, your connection string must include a "FAILOVER PARTNER NAME".  That way, if the connection to primary fails, it will retry on the mirror before reporting an error to the client application. 

Database mirroring isn't without its limitations and it can be cumbersome if you have to manage a lot of mirrors (the mirroring is strictly one-for-one).  This is why Availability Groups are so much better. 

A third, tried and true option is Log Shipping (the predecessor to Database Mirroring).  Log shipping is more of a periodic backup, where you send your transaction logs to an offline database, which you could bring online if your primary database fails.  Database Mirroring was known internally as "automated log shipping).  You can run frequent jobs to multiple servers and monitor status independently.

You might also consider Database Replication, but this usually only involves tables and certain other database objects, not entire databases.

So, to review:

  • Failover Clustering
    • Requires:
      • SQL Standard
      • Windows Clustering service
      • Shared storage
      • Redundant hardware
      • Redundant configuration (e.g. same hotfixes on each node)
      • AD accounts with elevated rights and configuration
    • Setup: Very complex
    • Benefit:
      • Protects entire instance (all databases, configuration, security, etc). 
      • Fast failover (30 seconds)
      • No client configuration needed (connects to clustered IP)
  • AlwaysOn Availability Groups
    • Requires:
      • SQL 2012 Enterprise required
      • User databases only (no system databases)
      • Needs Full Recovery Model on databases
      • Windows Clustering service
        • No Shared storage needed
        • No redundant hardware
        • No redundant configuration
        • Does require AD accounts with elevated rights and configuration
    • Setup: Moderate
    • Benefit:
      • Very fast failover (doesn't have to wait for storage).  I've seen it recover in 10 seconds.
      • No client configuration needed (connects to "Listener" address)
  • Database Mirroring
    • Requires:
      • SQL Standard (Synchronous Mode only)
      • User databases only (no system databases)
      • Needs Full Recovery Model on databases
      • Does not use Clustering service – instance to instance only
      • Clients need to use modified connection string
    • Setup: Easy
    • Benefit:
      • Fast failover (can be pretty fast, but AlwaysOn is faster).
  • Log Shipping
    • Requires:
      • SQL Workgroup or above
      • User databases only (no system databases)
      • Needs Full Recovery Model on databases
      • Does not use Clustering service – instance to instance only
      • Manual failover.
      • Clients require manual configuration to connect to secondary
    • Setup: Easy
    • Benefit:
      • Protects user databases only
      • Good for DR

 

There is a lot more to know when it comes to actually using an HA or DR solution for SQL Server. For one thing, you need to actually have a plan for using it, and test it periodically (quarterly is about right). Doing test restores of data is also a good idea. You need to make sure that this solution you just sunk all your money into is actually going to come through for you if (and when) you experience a failure.

Oct 29
First User Group Meeting!

​We have a celebrity coming to our first in-person user group meeting!  The date is December 5th, 2013 at noon.  We'll be meeting at the Network Business Systems office in their spacious conference room at their new Midtown location: 3000 C Street, Suite 210.  Light refreshments will be served.

Click here for directions.

SQL MVP Don Kiely will be here to talk about Security for SQL Server 2012! 

Door Prize: We will be giving away one free license for Redgate's SQL Compare Pro!  Must be present to win.

 

The Evolution of Security in SQL Server 2012
Microsoft rebuilt SQL Security from the ground up in SQL Server 2005, and since then they've evolved database security in each new version. SQL Server 2012 continues that trend with some new features that can keep your data even more secure, such as new server-level role features, new hashing algorithms, and new permissions. It also adds contained databases, which let users connect to the database without authenticating at the server level. This is a great feature that solves problems when you move databases, but it sounds scary from a security perspective. During this session, we'll inaugurate the Alaska SQL Server user group by exploring the new security features in SQL Server 2012. Love it or hate it, security is a fact of life that both DBAs and database developers have to live with every day. Come learn how to live together in peace!

Don Kiely is Alaska's sole Microsoft MVP, a senior technology consultant specializing in developing secure desktop and Web applications that integrate databases and related technologies, using tools including SQL Server, Visual Basic, C#, and ASP.NET. Don has authored and co-authored several programming books, many of which you're likely to see in the bargain bin at your local mega-bookstore. He has written for many industry journals, including Visual Studio Magazine, MSDN Magazine, CoDe Magazine, and asp.netPRO. Don trains developers and used to speak regularly at industry conferences, including TechEd, SQL PASS, VSLive!, DevConnections, DevTeach, and others, and is a member of the INETA and MSDN Canada speaker bureaus. He writes courseware for LearnNow (formerly known as AppDev) and records instructional videos for them. Don is a full member of the Institute of Electronics and Electrical Engineers and the American Society of Civil Engineers. He earned a BS in Civil Engineering from the University of Notre Dame and an MBA from the University of Colorado, but fortunately learned the error of his establishment ways. In his other life he roams the Alaska wilderness by foot, dog sled, skis, and canoe and kayak, and his current passion is running trail marathons. He is the volunteer President of Second Chance League, a non-profit sleddog rescue organization in Fairbanks, which rescues sleddogs from the local shelter and finds them new lifelong homes. Contact him at donkiely@computer.org.

Oct 29
Free Online SQL Conference

Here's an online training session I plan on attending.  Check it out!

Whip that database into shape and optimize your performance with the help of SQL training experts Michael Otey and Bradley Ball. Whether your production databases are starting to look a little pudgy around the middle, your SQL Server storage configuration is substandard, or you're stumped over how to fix your query, this is THE online training and education event for you!

December 5, join hundreds of IT pros and industry experts for SQL Server Pro's online conference, "SQL Server Performance and Storage Best Practices". This free, one-day event will feature training, networking opportunities, engagement with vendors, and Q&A sessions with SQL experts, all from the comfort of your home or office!

Sessions include:

  • Trimming Indexes: Getting Your Database in Shape
  • SQL Server Storage Best Practices
  • Troubleshooting & Optimizing Performance—NOW!
  • Windows Server 2012 Storage Enhancements

Click here for a detailed agenda on each session.

Hope to see you at the conference! Click here to register.

Aug 19
Your SQL Career

An internet friend of mine recently asked this question:

Anyone know anything about SQL? Had an impromtu interview at work today for a account manager/SQL database manager role for one of the subsidiaries of the company I work for.

I know basically nothing about it, my lack of IT experience is my weak point in getting this role, my strong customer facing skills are my major strong point. They've seen loads of IT bods but none have any customer facing skills. They're gained from experience, you can train for IT.

What's the main things to know? Are there any good websites out there explaining it?

I have actually been asked this question more than once.  People are finding that, more and more, databases are a fact of life in their job.  Databases drive the internet (every single bit of interactivity you see on the web is because of databases).  A database is more than just a place to store data... it is likely to be the most crucial piece of your business.  For example, if your database server goes down, you might lose Quickbooks, Great Plains, SharePoint, or timekeeping system, bringing all operations to a screeching halt.

SQL Server is just one of many "database server" products out there, along with Oracle, Sybase, MySQL, Pervasive SQL, etc.  If you're using Microsoft products, chances are you're going to use their own database engine whether you want to or not.  Fortunately SQL Server has a lot in common with other brands; they all accomplish the same work using the same methods (and to a large degree, using the same basic SQL commands). 

Which one is best?  I don't know (or care, really).  I just know SQL Server better than the others. 

So when people ask me where to get started, it's a hard question. 

Where do you get started?  I know where I started, but it was a really challenging path I wouldn't recommend to anyone.  I basically got sucked into the job. 

I was a federal contactor working as an electronics technician at Raythone, and my boss said, "hey, Greg, you went to college, right?  Why don't you take a look at this Access 97 database?"  So I had to scramble to learn a new skill, on the job, with a deadline looming.  And succeeding at that, I was tossed more and more work, until I had a full-time software development project.   

We had grown beyond Access 97, turning instead to this database server called SQL Server 7.  I was actually pretty excited because SQL Server solved all kinds of limitations I'd run into using Access.  And there was a free Desktop version I could use for testing.  Along the way, I had to dive into all sorts of database design books and articles, learning a lot of it on my own time (my company had zero budget for training).  It was a lot to digest, but I loved every minute of it. 

What do I need to know?

I've been working with SQL Server in some way every day since then.  SQL Server is a very broad technology, and there's a lot to know.  

Here's a few pointers.

  1. SQL is all about I/O. It is about finding and retrieving the data in the fastest and most efficient manner. If you're in a small office with a 1GB database, this isn't really an issue, but the database is very large or has a lot of users, it can become important in a hurry. Database administration then becomes an art form in terms of tuning all the aspects of performance: Storage, Network, Memory, and CPU.
  2. SQL is all about DR. Disaster Recovery, that is. What defines a DBA's main job is the ability to recover lost data and restore it to operation as fast as humanly possible. For a small office, it's usually a pretty simple task: you back the server up, you take teh tapes offsite. If someone needs to recover the 600 records they deleted last August, you are the one who knows which tape to use. For a large scale environment, it becomes much more complicated in a hurry. You have to figure out service level agreements, guaranteeing how quickly a database can be brought back into service, and learn about high availability (e.g. clustering and replication) and business continuity (e.g. multi-site failover).
  3. SQL is all about database design. Some DBAs are responsible not just for keeping the systems online and running smoothly, but also about knowing exactly how the databases are structured, including any business logic and functionality the database provides. If data is screwed up, it could be your job to sort it out. If you have support a third-party application's database, you'll be the on in charge of upgrading the databases, and it'll be you on the phone if the application crashes. You need to know about the SQL language, how to construct a query, and understand table joins. For starters.
  4. SQL is all about programming. Some DBAs also wear the developer hat. You'd need to know how to create or debug software the connects to your databases, or at least be able to assist another programmer in doing this.
  5. SQL is all about customer service. Above all, even if the previous four skills are lacking, you need to be able to talk to your customers. You must relate complex ideas to them while being friendly and helpful. If you are good with customers, this skill alone may be what gets you hired.
  6. SQL is all about best practices. There is a bit of a schism in the database world as to whether the best DBAs start out as software developers, or start out as network admins.
    • The problem with developers is that they see SQL Server as a container for their precious databases and they ignore best practices just to get their software working. Developers often make all users administrators and are (sometimes) clueless about how SQL uses memory and CPU. They buy enormously powerful servers and are dismayed because performance remains sluggish. They complain that Microsoft is "buggy" and "slow". But they can make good admins because they have a great deal of insight about how the database clients are accessing and manipulating the data.
    • The problem with network admins is that they see SQL Server as just another box, and they treat it with no more care or interest than they would with a file server. SQL Server can scale very well, but when it does, you really need to know your stuff. Server admins are often clueless about how the databases actually work, and they (sometimes) find developers annoying and unreasonable. They are quick to buy lots of third party tools to make their job easier, but there's a good chance they won't understand the information these tools give them. But they can make good admins because they understand about performance, DR, and security.

So obviously, the superlative DBA is a person who can sit on the fence between Developer World and Admin World. All while keeping abreast of the latest technologies and knowing how to leverage them to improve the performance and stability of all your systems.

If you're a developer, you need to know all about writing queries and stored procedures and triggers, designing tables and reports, and digging into data types.  You need to understand how a client machine will connect to SQL Server across the network, and how much work SQL will do to respond to each request.

If you're an administrator, you need to know about networking and storage and memory and CPU, how SQL Server services run in Windows, how to maintain, backup, and restore databases, how to secure your environment, and minimize downtime.

I learned most of this stuff a piece at a time, as-needed, whenever the job demanded it.  Your mileage may vary.  The best way to start, of course, is with some guided learning, classes, labs, and practice.  I do wish I'd had the time and money to go take some classes, back in the day.  I'll be linking to some good training resources, videos, articles, low-cost training, etc. 

If you want to get started with SQL Server, now is the perfect time.  You will ALWAYS have a job.