Thanks for stopping by and checking out the new blog site. Over the next few weeks I will be moving content here, so any previous information you have seen will available again soon. Keep checking back!
In a recent discussion, the issue of breaking changes in SQL Server 2012 came up, and I realized that no one in the room had a thorough knowledge of the breaking changes and discontinued features. Since we were trying to determine a problem was a bug or a discontinued feature, this was important.
That made me realize that I had not seen all of this information gathered into a single location, so I decided to get all of that information and post it here. The information on SQL Server 2012 will change through Release Candidates and RTM, so I will update this article with those links as that information becomes available.
There are 4 categories of information listed below:
Today we went live with SQL Server “Denali” for our Tier-1 systems. We did it with only 3 minutes 45 seconds of downtime, and zero problems. We will make up that downtime between now and the end of the year with the improvements in the online indexing feature alone!
SQL Server “Denali” has introduced a new way to capture SQL Server health and diagnostic information with the sp_server_diagnostics stored procedure:
sp_server_diagnostics [@repeat_interval =] 'repeat_interval_in_seconds'
The stored procedure runs in “repeat mode” and periodically returns results. Specifying 0 as the @repeat_interval causes it to run only once.
This is a great way to get a lot of diagnostic information very quickly. The procedure returns five columns and multiple rows. The columns are:
The five components contain a ton of great data. Here is a brief description of the data that is collected about each component:
You can execute this query interactively and view the results on the screen, or you can save those results to a table or a file (see Books Online for details). Microsoft recommends creating an extended session to save the output to a file so that you can still access it outside of SQL Server if there is a failure. For systems that are not suffering problems that dramatic, you can capture the data to a table and query it easily.
This is a great way to get a quick snapshot of the system to narrow down the problem quickly. If you work with remote clients that call you when there is a problem, this is also a very quick and simple way to have them gather information for you. Thank you, Microsoft, for making this data so easy to capture!
Detailed information about sp_server_diagnostics is available in Books Online.
Note: this information is based on pre-release documentation. I will update this post and the links as new “Denali” versions are released.
In the first two posts on CTP3 features, I discussed the new AlwaysOn and Security features in SQL Server “Denali” CTP3, and then the Business Intelligence and SSIS/Data Tools features. This post is about programmability enhancements and miscellaneous features that do not fall into other categories. As before, these are not all of the features included in Denali, only the ones that I think will be of most interest to a wide audience, or features that I am excited about for one reason or another.
There are many programmability enhancements in Denali, but here are a few that I think are worth noting:
There are several features which don’t really fit into a single category, so I have put them in the “Other Features” bucket. Some of these are great features though, so don’t think that because I left them to last that they are insignificant! Explore these new features and I think you will find that there are multiple features that may be of use to you.
At this point I have given a pretty good overview of all of the features. In the coming weeks I will deal with more specifics on many of these and go into much more depth about how they are used and what it means to you.
In my last post, I discussed the new AlwaysOn and Security features in SQL Server “Denali” CTP3. This post is about some of the Business Intelligence (BI) improvements, and changes in SSIS and Data Tools.
I am going to focus on three different areas of BI changes that I think are notable:
There is really a lot of work that has taken place in Denali around data quality, moving data between systems and data modeling. For lack of a better way to organize this data, I have lumped it all together under “SSIS / Data Tools” because to me, most of these features are related and will be used by the same groups of users. All of this falls under what Microsoft is calling “Enterprise Information Management (EIM)”.
If you are a BI user or work with SSIS or Data stores, I think you will find quite a few features in Denali that can help you with your job.
Check out Part 3 for Programmability and Other enhancements in CTP3.
Now that CTP3 has been released to the public, I can start to blog about a huge list of content that I have been working on related to Denali. Although this is technically a post about CTP3 features, CTP3 is very close to what I expect the final product to be, with only a limited set of enhancements from here until RTM.
This post is about features. After these three posts, where I briefly discuss some of the new bells and whistles of the product, I can start to do some deep dives into functionality and configuration. I have divided the features up into the following categories:
I will discuss the new features in that order. I have devoted a significant amount of time to working with the SQL team on the HA/DR (AlwaysOn) features of Denali, so of course that gets put at the top of the list. 🙂
Over the course of the last two years, we have referred to the high availability features as “HADR”, “HADRON”, and “AlwaysOn”. From here on I will simply call these features “AlwaysOn”. AlwaysOn incorporates these new features:
There are several new security features which are compelling. I will briefly mention them here, but you should definitely look into these features in the product. I think you will find some features that can both save you headaches and enhance your auditing.
You can see by the list of just AlwaysOn and Security features in Denali, that this version of SQL Server is packed full of new stuff. As I mentioned in a post earlier this year, you need to start spending some time working with the CTPs so that you get up-to-speed quickly.
Check out Part 2 for BI and SSIS/Data features.
In addition to CTP3 of SQL Server code name “Denali”, Microsoft has also released a feature pack (http://www.microsoft.com/download/en/details.aspx?id=26726) that is full of useful tools that work with CTP3. Some of the features I find most exciting in the feature pack:
Today Microsoft announced the release of SQL Server “Denali” CTP3, available at https://www.microsoft.com/betaexperience/pd/SQLDCTP3CTA/enus/. CTP3 contains most of the features that will be released at RTM, so download the bits today and start getting familiar with SQL Server “Denali”.
Now that CTP3 has been released, I can discuss many of the new features that were previously covered under NDA. Check back here in the coming weeks for more information on new features!
In March, 2011, Microsoft added asymmetric storage support for Windows Server 2008 and 2008 R2 failover clustering. That means that not all nodes in the cluster have to share the same storage; some nodes can use one set of storage, and some nodes can use another. A likely scenario for this is in a multi-site cluster, where each site has its own storage array.
When adding nodes in a cluster that has asymmetric storage, you may see an error similar to “There was an error creating, configuring, or bringing online the Physical Disk resource (disk) ‘Cluster Disk 1’ “. You may see this error in several different locations, but generally you will first see this on the report screen at the end of the Add Node wizard, where it will appear similar to this (click for full-size image):
The reason for this is logical, but not obvious, nor is the solution obvious. First, and most importantly, the nodes were added successfully and there is no problem – you just can’t use the disks yet. Do not remove the nodes and go through the Add Node process again.
Starting with Windows Server 2008, when you expose disks to the cluster, those disks are placed in a hidden cluster resource group called “Available Storage”. Any of your “Services/Applications”, like SQL Server or MS-DTC, are also resource groups, but that has all been abstracted to make the interface more friendly and less technical. Every resource group is owned by a node, and the active ownership changes when you fail between nodes. If you click on any “Service/Application” in Failover Cluster Management, the top, right pane will show the current owner.
So what happened with your installation and why did it give you an error? Imagine that you have a single cluster with three local nodes (LocalNode1, LocalNode2, LocalNode3) and two remote nodes (RemoteNode1, RemoteNode2). The local nodes use one storage array, and the remote nodes use another storage array. You initially set up the cluster with the local nodes and everything works correctly. You complete the setup by installing SQL Server, or another application, using all of the disks in Available Storage.
At that point, the “Available Storage” resource group has no disks, but the resource group is owned by one of the local nodes. Although you can’t manage Available Storage like other resource groups, behind-the-scenes, it is a resource group just like any other.
You then use the Add Node wizard to perform cluster validation, and the nodes are added successfully, except that you get the error mentioned above. The reason for this is simple:
Therefore, the disks are successfully added, but they cannot be brought online by the owner of Available Storage (a local node) because it cannot be a possible owner of the remote disks. This is also indicated further in the error message screen above: Resource for ‘Cluster Disk 1’ has been created but will not be brought online because the disk is not visible from the node which currently owns the “Available Storage” group. To bring this resource online, move that group to a node that can see the disk. The possible owners list for this disk resource has been set to the nodes that can host this resource.
The solution is simple – you just need to change the current owner of Available Storage to be one of the nodes that is a possible owner of the new disks, in this case RemoteNode1 or RemoteNode2. Unfortunately, there is currently no way to do this through the Failover Cluster Management GUI, and you must resort to cluster.exe:
C:\>cluster.exe group "available storage" /moveto:RemoteNode1
You will immediately see a message saying “Moving resource group ‘available storage’…”, followed by Available Storage with a Status of “Online” on the new node. If you watch Available Storage in Failover Cluster Management, you will see the disks come online as the move happens.
While this is not initially straight-forward, it is very simple. In more complex setups, you may have unused cluster disks on several different asymmetric storage devices. In this case, you will always have at least some disks that are offline, because Available Storage can only be owned by one node at once, and that node will not have access to the asymmetric disks.
The Windows Clustering team is aware of this issue, and I fully expect this to be addressed in a future release. For now, I am thankful that I have the ability to create an asymmetric storage cluster and will deal with some of the minor issues like this.