SQLAuthority News – 2 Whitepapers Announced – AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution

Understanding AlwaysOn Architecture is extremely important when building a solution with failover clusters and availability groups. Microsoft has just released two very important white papers related to this subject. Both the white papers are written by top experts in industry and have been reviewed by excellent panel of experts. Every time I talk with various organizations who are adopting the SQL Server 2012 they are always excited with the concept of the new feature AlwaysOn. One of the requests I often here is the related to detailed documentations which can help enterprises to build a robust high availability and disaster recovery solution. I believe following two white paper now satisfies the request.

AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using AlwaysOn Availability Groups

SQL Server 2012 AlwaysOn Availability Groups provides a unified high availability and disaster recovery (HADR) solution. This paper details the key topology requirements of this specific design pattern on important concepts like quorum configuration considerations, steps required to build the environment, and a workflow that shows how to handle a disaster recovery.

AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using Failover Cluster Instances and Availability Groups

SQL Server 2012 AlwaysOn Failover Cluster Instances (FCI) and AlwaysOn Availability Groups provide a comprehensive high availability and disaster recovery solution. This paper details the key topology requirements of this specific design pattern on important concepts like asymmetric storage considerations, quorum model selection, quorum votes, steps required to build the environment, and a workflow.

If you are not going to implement AlwaysOn feature, this two Whitepapers are still a great reference material to review as it will give you complete idea regarding what it takes to implement AlwaysOn architecture and what kind of efforts needed. One should at least bookmark above two white papers for future reference.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Weekend Project – Experimenting with ACID Transactions, SQL Compliant, Elastically Scalable Database

Database technology is huge and big world. I like to explore always beyond what I know and share the learning. Weekend is the best time when I sit around download random software on my machine which I like to call as a lab machine (it is a pretty old laptop, hardly a quality as lab machine) and experiment it. There are so many free betas available for download that it’s hard to keep track and even harder to find the time to play with very many of them.  This blog is about one you shouldn’t miss if you are interested in the learning various relational databases.

NuoDB just released their Beta 7.  I had already downloaded their Beta 6 and yesterday did the same for 7.   My impression is that they are onto something very very interesting.  In fact, it might be something really promising in terms of database elasticity, scale and operational cost reduction. The folks at NuoDB say they are working on the world’s first “emergent” database which they tout as a brand new transitional database that is intended to dramatically change what’s possible with OLTP.  It is SQL compliant, guarantees ACID transactions, yet scales elastically on heterogeneous and decentralized cloud-based resources. Interesting note for sure, making me explore more.

Based on what I’ve seen so far, they are solving the architectural challenge that exists between elastic, cloud-based compute infrastructures designed to scale out in response to workload requirements versus the traditional relational database management system’s architecture of central control. Here’s my experience with the NuoDB Beta 6 so far:

First they pretty much threw away all the features you’d associate with existing RDBMS architectures except the SQL and ACID transactions which they were smart to keep.  It looks like they have incorporated a number of the big ideas from various algorithms, systems and techniques to achieve maximum DB scalability. From a user’s perspective, the NuoDB Beta software behaves like any other traditional SQL database and seems to offer all the benefits users have come to expect from standards-based SQL solutions.

One of the interesting feature is that one can run a transactional node and a storage node on my Windows laptop as well on other platforms – indeed interesting for sure. It’s quite amazing to see a database elastically scale across machine boundaries. So, one of the basic NuoDB concepts is that as you need to scale out, you can easily use more inexpensive hardware when/where you need it.  This is unlike what we have traditionally done to scale a database for an application – we replace the hardware with something more powerful (faster CPU and Disks).

This is where I started to feel like NuoDB is on to something that has the potential to elastically scale on commodity hardware while reducing operational expense for a big OLTP database to a degree we’ve never seen before. NuoDB is able to fully leverage the cloud in an asynchronous and highly decentralized manner – while providing both SQL compliance and ACID transactions.

Basically what NuoDB is doing is so new that it is all hard to believe until you’ve experienced it in action.  I will keep you up to date as I test the NuoDB Beta 7 but if you are developing a web-scale application or have an on-premise app you are thinking of moving to the cloud, testing this beta is worth your time. If you do try it, let me know what you think.  Before I say anything more, I am going to do more experiments and more test on this product and compare it with other existing similar products. For me it was a weekend worth spent on learning something new. I encourage you to download Beta 7 version and share your opinions here.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Download SQL Server Product Documentation

Today I just returned from Bangalore User Group Meeting. Attending User Group meeting is indeed fun and really great experience. The best part of the User Group is meeting like minded people and have a great conversation with them. During the meeting I was asked why one has to go online to access SQL Server Product Documentation. I can clearly see there can be many reasons for why one wants the documentation to be available offline. The reasons can be anything but not limited to

  • Company Firewall
  • No Internet (power failure, on road or disaster)
  • Internet Bandwidth Limitatoin
  • Company Proxy Issues
  • … and much more…

In this kind of situation one needs documentation which is offline and easily available in local computer. The Microsoft SQL Server 2012 setup installs only the Help Viewer and related components for managing Help settings. Setup does not directly install any documentation. All of the SQL Server documentation is available online.

Well, Microsoft has recently released Production Documentation available for offline users as well. You just have to download the executable file and follow all the instructions. Please follow the link below for download of the file. There is detailed instruction on how to download and install the documentation offline.

Product Documentation for Microsoft SQL Server 2012

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Free eBook Download – EPUB, MOBI, PDF Format

freetags SQL SERVER   Free eBook Download   EPUB, MOBI, PDF Format Microsoft has released recently free eBooks on various Microsoft Technology. The best part is that all these books are available in ePub, Mobi and PDF. You can download them to your local machine or eBook reader and read them. This is a great start as many important subjects are now covered and converted into an eBook. I personally read through a few of the books and found they are very comprehensive and and detailed. The goal is not to cover complete technology in a single book but rather pick a single topic and discuss it in detail. The source of the book is white paper, Technet wiki as well book online and it is clearly listed right bellow the book title.

Following are the books available for SQL Server Technology and I encourage all of you to have a look at them as they are great resources.

Master Data Services Capacity Guidelines
Microsoft SQL Server AlwaysOn Solutions Guide for High Availability and Disaster Recovery
Microsoft SQL Server Analysis Services Multidimensional Performance and Operations Guide
QuickStart: Learn DAX Basics in 30 Minutes
SQL Server 2012 Transact-SQL DML Reference

You can download above eBooks from here.

This is indeed a great attempt as each book talks about the a single subject in depth keeping author focus on the single and simple subject. I have previously written two books by focusing on the same subject and I had great pleasure writing it as well. Writing on focus subjects gives complete freedom to author to explore the a single subject without having burden to cover everything which is associated with that technology at large.

Just like eBooks mentioned earlier my SQL Server Wait Stats was inspired from my article series on SQL Wait Stats. The latest book SQL Server Interview Question and Answers was derived from my article series on SQL Interview Q and A. Writing book is an absolutely different concept than writing blog posts. When I was converting my blog posts to books, I ended up writing 50% new material and end up removing many repetitive content which shows up in blog series. It was indeed fun to focused book at the same time it was a great learning experience as an individual.

Reference: TechNet Wiki, Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – SQL Server 2012 Upgrade Technical Guide – A Comprehensive Whitepaper – (454 pages – 9 MB)

Microsoft has just released SQL Server 2012 Upgrade Technical Guide. This guide is very comprehensive and covers the subject of upgrade in-depth. This is indeed a helpful detailed white paper. Even writing a summary of this white paper would take over 100 pages. This further proves that SQL Server 2012 is quite an important release from Microsoft. This white paper discusses how to upgrade from SQL Server 2008/R2 to SQL Server 2012. I love how it starts with the most interesting and basic discussion of upgrade strategies: 1) In-place upgrades, 2) Side by side upgrade, 3) One-server, and 4) Two-server. This whitepaper is not just pure theory but is also an excellent source for some tips and tricks. Here is an example of a good tip from the paper:

“If you want to upgrade just one database from a legacy instance of SQL Server and not upgrade the other databases on the server, use the side-by-side upgrade method instead of the in-place method.”

SQLServer2012big SQLAuthority News   SQL Server 2012 Upgrade Technical Guide   A Comprehensive Whitepaper   (454 pages   9 MB)

upgradenow SQLAuthority News   SQL Server 2012 Upgrade Technical Guide   A Comprehensive Whitepaper   (454 pages   9 MB)

There are so many trivia, tips and tricks that make creating the list seems humanly impossible given a short period of time. My friend Vinod Kumar, an SQL Server expert, wrote a very interesting article on SQL Server 2012 Upgrade before. In that article, Vinod addressed the most interesting and practical questions related to upgrades. He started with the fundamentals of how to start backup before upgrade and ended with fail-safe strategies after the upgrade is over. He covered end-to-end concepts in his blog posts in simple words in extremely precise statements.

A successful upgrade uses a cycle of: planning, document process, testing, refine process, testing, planning upgrade window, execution, verifying of upgrade and opening for business. If you are at Vinod’s blog post, I suggest you go all the way down and collect the gold mine of most important links. I have bookmarked the blog by blogging about it and I suggest that you bookmark it as well with the way you prefer.

Vinod Kumar’s blog post on SQL Server 2012 Upgrade Technical Guide

SQL Server 2012 Upgrade Technical Guide is a detailed resource that’s also available online for free. Each chapter was carefully crafted and explained in detail. Here is a quick list of the chapters included in the whitepaper. Before downloading the guide, beware of its size of 9 MB and 454 pages.

Here’s the list of chapters:

Chapter 1: Upgrade Planning and Deployment
Chapter 2: Management Tools
Chapter 3: Relational Databases
Chapter 4: High Availability
Chapter 5: Database Security
Chapter 6: Full-Text Search
Chapter 7: Service Broker
Chapter 8: SQL Server Express
Chapter 9: SQL Server Data Tools
Chapter 10: Transact-SQL Queries
Chapter 11: Spatial Data
Chapter 12: XML and XQuery
Chapter 13: CLR
Chapter 14: SQL Server Management Objects
Chapter 15: Business Intelligence Tools
Chapter 16: Analysis Services
Chapter 17: Integration Services
Chapter 18: Reporting Services
Chapter 19: Data Mining
Chapter 20: Other Microsoft Applications and Platforms
Appendix 1: Version and Edition Upgrade Paths
Appendix 2: SQL Server 2012: Upgrade Planning Checklist

Download SQL Server 2012 Upgrade Technical Guide [454 pages and 9 MB]

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Developer Training Kit for SQL Server 2012

Developer Training Kit is my favorite part of any product. The reason behind is very simple because it give the single resource which gives complete overview of the product in nutshell. A developer can learn from many places – books, webcasts, tutorials, blogs, etc. However, I have found that developer training kits are the best starting point for any product. Start with them first, see what are the new features as well what is the new message a product is coming up with. Once it is learned the very next step should be to identify the right learning material to explore the preferred topic.

The SQL Server 2012 Developer Training Kit includes technical content including labs, demos and presentations designed to help you learn how to develop SQL Server 2012 database and BI solutions. New and updated content will be released periodically and can be downloaded on-demand using the Web Installer.

Download SQL Server 2012 Developer Training Kit Web Installer.

This training kit was available earlier this year but it is never late to explore it if you have not referred it earlier. Additionally, if you do not want to download complete kit all together I suggest you refer to Wiki here. This wiki contains all the same presentations and demo notes which web installer contains.

Refer to SQL Server 2012 Developer Training Kit Wiki

Wiki contains following module and details about Hands On Labs
Module 1: Introduction to SQL Server 2012
Module 2: Introduction to SQL Server 2012 AlwaysOn
Module 3: Exploring and Managing SQL Server 2012 Database Engine Improvements
Module 4: SQL Server 2012 Database Server Programmability
Module 5: SQL Server 2012 Application Development
Module 6: SQL Server 2012 Enterprise Information Management
Module 7: SQL Server 2012 Business Intelligence
Hands-On Labs: SQL Server 2012 Database Engine
Hands-On Labs: Visual Studio 2010 and .NET 4.0
Hands-On Labs: SQL Server 2012 Enterprise Information Management
Hands-On Labs: SQL Server 2012 Business Intelligence
Hands-On LabsHands-On Labs: Windows Azure and SQL Azure

As I said, if you have not downloaded this so far, it is never late to explore it. Trust me you will atleast learn one thing if you just explore the content.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQLAuthority News – Migration Guide: Migrating to SQL Server 2012 Failover Clustering and Availability Groups from Prior Clustering and Mirroring Deployments – Part 1

Migration is always a challenge. How many times we have stayed away from migrating product to another server or next version because we are worried what will happen once we migrate. There are two main reasons we stay away from migration 1) Everything is working fine at this moment. 2) Fear of everything will not work fine after migration.

Let us address two of this fear in brief words.

1) Everything is working fine

Even though everything is working fine there are need to upgrade to next version because new version often brings improved features as well new enhancement which can help in many ways, i.e. scalability, manageability etc. There are often cases when something is often done very complex way in current version and in next version right away out of the box the same thing is supported. I am big fan of the going to next version when it is stable.

2) Fear of application not working on next version

This is very common and we all have faced it one time or another time. We always have fear of unknown. We often worry too much because we do not know things.

The best way to solve above two problem is – Knowledge. Here is the whitepaper which talks about migration from earlier solution of the Mirroring to SQL Server 2012 solutions. This is first part of the whitepaper and second part of the whitepaper release soon.

Following is the abstract of the whitepaper.

This paper provides guidance for customers who prior to SQL Server 2012 have deployed SQL Failover Clustering for local high availability and database mirroring for disaster recovery, and who want to migrate to SQL Server AlwaysOn. It describes the corresponding SQL Server AlwaysOn scenario and the migration paths to SQL Server AlwaysOn. It also contains the important knowledge and considerations that you must know in order to successfully migrate to a HADR solution based on SQL Server AlwaysOn technology, which implements AlwaysOn Failover Cluster Instances for high availability and AlwaysOn Availability Groups for disaster recovery.

Download Whitepaper.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Cheatsheet – Released for SQL Server 2012 Edition

SQL Server Cheatsheet has been extremely popular download from my blog. There are plenty of request for me to update it with SQL Server 2012 features. I have finally upgraded the cheatsheet with SQL Server 2012 features. The new cheatsheet has following updates

  • SSMS Shortcuts
  • Columnstore Index
  • SQL Server 2012 Datetime Functions
  • SQL Server Ranking Functions
  • SQL Server 2012 Analytic functions
It has been excellent experience to upgrade the cheatsheet. However, as the new cheatsheet contains way more information than earlier version of the cheatsheet, the size of the page is also increased. The page size is now no more A4 size, it has been increased to A3 size. When printed on A3 page it looks very clean and easy to read. If you print that on A4, you will find the pages are bit small.
Cheatsheet is free for anybody and everybody to download and print it, as long as you do not modify it. However, it is now allowed to store, host or upload anywhere without prior permission. 
Previous cheatsheet was very popular and I have received many good comments. I am eagerly waiting for your feedback about this new updated cheatsheet.

Download SQL Server Cheatsheet

I have printed limited copies of the cheatsheet on photo quality laminated A3 size page. This looks AWESOME!

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQLAuthority News – Download SQL Azure Labs Codename “Data Explorer” Client

Microsoft SQL Azure labs has recently released Data Explorer client. I was looking forward to visualizing tool for quite a while and I am delighted to see this tool. I will be trying out this tool in coming week and will post here my experience. I have listed few of the resources which are related to Data Explorer at the end. Please let me know if I have missed any and I will add the same.

With “Data Explorer” you can:

  • Identify the data you care about from the sources you work with (e.g. Excel spreadsheets, files, SQL Server databases).
  • Discover relevant data and services via automatic recommendations from the Windows Azure Marketplace.
  • Enrich your data by combining it and visualizing the results.
  • Collaborate with your colleagues to refine the data.
  • Publish the results to share them with others or power solutions.

The Data Explorer Client package contains the Data Explorer workspace as well as an Office plugin that integrates Data Explorer into Excel.


Download Data Explorer
Data Explorer Blog
Desktop Client
Video of  Contoso Bikes and Frozen Yogurt (Data Explorer)

Please note that this is not the final release of the product. Please do not attempt this on production server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Install Samples Database Adventure Works for SQL Server 2012

AdventureWorks is a Sample Database shipped with SQL Server and it can be downloaded from CodePlex site. AdventureWorks has replaced Northwind and Pubs from the sample database in SQL Server 2005.The Microsoft team keeps updating the sample database as they release new versions.

For SQL Server 2012 RTM Samples AdventureWorks Database is released:

You can download either of the datafile and create database using the same. Here is the script which demonstrates how to create sample database in SQL Server 2012.

CREATE DATABASE AdventureWorks2012
ON (FILENAME = 'D:\AdventureWorks2012_Data.mdf')

Please specify your filepath in the filename variable. Here is the link for additional downloads.

Watch a quick video on the same subject.

Reference: Pinal Dave (http://blog.sqlauthority.com)