Blog

Apr 21
4-17-14: Designing Stored Procedure Solutions

Guest speaker Jen McCown (Midnight DBA) delivered this excellent presentation on Stored Procedures: "In this session, you'll learn about SQL Server stored procedures (SPs): what they are, when and why you'd use them, and how you'd go about developing a solution with one. We will address common SP myths and learn about planning for performance. Most of all, we'll walk through examples to explore the process of solution building in SQL Server. "

 

​Downloads: Designing Stored Procedure Solutions – Demo Code

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.

1 - 10Next
SharePoint Help  
Looking for recommendations...

How to

There is no recommended article for this page. Try searching help and community.
This shouldn't take long.

Community

Can't find what you are looking for? Post your question to the Office 365 Community
Sorry, we couldn't find an answer. Post your question to the Office 365 Community
Sorry, the help service is down, but you can find answers at Office support