THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Bit Bucket (Greg Low): IDisposable

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

  • Invalid Quorum Configuration Warnings when failing over SQL Server Availability Group

    At a client site today and they asked me about a warning that they got every time they manually failed over their SQL Server availability group.

    It said: “The current WSFC cluster quorum vote configuration is not recommended for the availability group.” They were puzzled by this as they had a valid quorum configuration. In their case, they had a two node cluster using MNS (majority node set) and a fileshare witness.

    The problem with that message is that it is returned when the node voting weight is not visible.

    Windows Server 2008 failover clustering introduced node-based voting but later an option was provided to adjust the voting weight for each node. If the cluster is based on Windows Server 2008 or Windows Server 2008 R2, and KB2494036 has not been applied, even though each node has a vote, the utilities that check voting weight are not supplied a weight value. You can see this by querying:

    SELECT * FROM sys.dm_hadr_cluster_members;

    This will return a row for each cluster member but will have a missing vote weight.

    Applying the KB hotfix will make this DMV return the correct values, and will make this invalid warning disappear.

  • Online certification exams are now available in Australia

    I’ve been hoping this would happen for a while and now it’s here (in beta).

    Whenever I take a certification exam, I find it removes my ability to work for most of a day, so I tend to schedule myself for two or three exams in a day, to avoid the overhead. It also means that I tend to limit the number of exams that I would take.

    Online proctoring of exams changes all that for me. If I can just schedule an exam for lunch time or night, or weekend from my own office, I’ll be much more inclined to take more certification exams.

    There are some rules:

    • You'll be recorded—both video and audio—for the duration of the exam.
    • You can't take notes during the exam.
    • You can't eat, drink, or chew gum while you take the exam.
    • You can't take a break—for any reason.

    They seem reasonable to me and if you don’t like them, you can always attend an in-person exam. Better make sure you get to the rest room beforehand though Smile It’s not available in all countries yet but fortunately Australia is one of the countries in the list. I’m not sure how the countries are chosen because I notice that our Kiwi buddies aren’t in the list yet. I’m sure that will change over time.

    Regardless, this is a really good initiative. Well done Microsoft Learning.

    You’ll find more information here: https://www.microsoft.com/learning/en-us/online-proctored-exams.aspx

  • Telerik Dineissimo Sample App–Interesting Marketing Approach

    I’ve liked the Telerik tools for a long time. I see many of my clients using either their controls for Webforms, and many using KendoUI for newer MVC development. I suppose it’s a challenge for such vendors to work out how to best market their products, but also a challenge to show developers how to best use them.

    With KendoUI, I particularly liked the Kendo Dojo idea, where you could just interactively learn to use the framework directly from inside your own browser without having to install anything locally.

    Today, I’ve noticed they released a sample application called Dineissimo but what caught my eye was the marketing approach. Basically, you need to visit: https://www.telerik.com/campaigns/platform/run-hybrid-app and download the app. You then need to find a particular part of the code, then run the program and use a QR (quick response) code to get things going. Finally, in the app, they get you to edit your profile and add a selfie or other image before sending off the details. And for a bunch of early takers, they’ll send an Amazon gift card. There’s a walk-through here: https://www.telerik.com/campaigns/platform/run-hybrid-app

    I like the fact that they are releasing a bunch of source code to show how to use their product but it’s the marketing approach that interests me. It combines getting genuine interest in the product, making sure it’s actually seen, then getting the user further involved.

    Nice job Telerik.

  • Determining your session’s transaction isolation level

    A question came up from a developer yesterday. He could see how to set a transaction isolation level but didn’t know how to determine the current transaction isolation level. That detail is available in the sys.dm_exec_sessions DMV.

    Here’s an example:

    image

     

    And if you are running SQL Server 2012, you could always use CHOOSE instead:

    image

  • Should there be code differences between Azure SQL Database editions?

    I spend a lot of time working with software houses, helping them to make their applications work well with SQL Server. One thing that I’ve heard loud and clear over the years is that most software houses won’t write a single line of code that will only run on the enterprise edition of SQL Server, because they are not prepared to limit their potential pool of customers to those running enterprise edition.

    This is completely at odds with the discussions that I’ve had with the SQL Server marketing team members who think that having feature differences will cause people to purchase enterprise edition instead. I’m sure that’s true for customers who write their own applications in-house and is also why at promotional events, the customers that you see mentioned are often those types of customers. However, most SQL Server customers run 3rd party applications written by other companies. The customers will often ask the software houses what software is required to run their applications and they then purchase what they need, unless they have some other pre-existing form of relationship with Microsoft.

    So this means that having a difference in features can actually cost Microsoft money as the customers will often purchase standard edition because that’s all the software that they will be running requires.

    Worse, when software houses are comparing SQL Server to other database engines, they compare SQL Server standard edition to the other engines, not the enterprise edition. This makes SQL Server compare badly for marketing reasons instead of technical reasons. For example, I saw a software house the other day comparing SQL Server with PostgreSQL. Their contention was that PostgreSQL (a free database engine) had a good high availability story and that SQL Server did not. Their logic was that SQL Server only had mirroring (and log shipping) and Microsoft had announced the deprecation of mirroring. So their contention was that SQL Server did not have a good availability story. The fact that enterprise edition had a really good story was irrelevant as they don’t consider anything in that version.

    A further issue appears with coding. There is no developer edition of SQL Server that is limited to standard edition features. Software houses want to write code once and have it work across all target editions.

    Another core issue is that this focus on enterprise edition has removed the upgrade reasons for standard edition customers. I think that every edition should have a compelling upgrade story, for every version. As an example, in SQL Server 2014, the reasons to upgrade for standard edition customers are the ability to use 128GB of memory and to have backup encryption. I’ll leave it to the reader to decide if that’s a strong story. I don’t think it is.

    The final issue with the existing situation is that the product is moving into areas that need support from software houses. SQL Server 2014 introduced a range of in-memory options. For any customer that can’t change the code (ie: most customers), this is irrelevant. Again you’ll see the same large customers who write their own apps being mentioned in the launch events. I this case, I think the marketing team really have made a mistake. While new HA features, etc. can be retrofitted by a DBA to an existing database, the new in-memory options really need to be architected into the design of the applications. And that’s where it’s a real problem that it’s in enterprise edition only. The software houses are unlikely to use it, and yet they are exactly the same people that we need to embrace it.

    So what does this have to do with Azure?

    Bob Beauchemin wrote a great blog post today about how Azure SQL Database is moving to a SQL Server 2014 code base. That’s a great thing but one aspect that caught my eye was the mention that this is the first version of Azure SQL Database where features like columnstore indexes, etc. will only appear in the premium editions of Azure SQL Database.

    While I’ve had concerns about how the licensing has been handled in the on-premises versions of SQL Server, in Azure SQL Database this concerns me even more. I really think that Azure SQL Database should offer the same code surface no matter which edition you are using. It makes sense to have performance and availability (including HA) options differ between Basic, Standard, and Premium but I really don’t like the idea of coding/feature differences. First up, it will again see software houses ignoring useful features. But worse, in the Azure SQL Database arena, customers are much more likely to use a mix of database editions than they currently do on-premises.

    For example, if I am offering an application as a service, I want to be able to have different databases for different tenant customers. I really want to be able to choose the performance, reliability, availability options, etc. on a tenant by tenant basis, not across all tenants that are using my application. Having coding differences across the editions would make this a mess, or at least I think so.

    I’d love to hear your thoughts.

  • Office 365 coming to Australian Data Centres

    We’ve been so excited having local data centres for Microsoft Azure.

    The one disappointment has been that Office 365/Power BI has still been based out of Singapore data centres. That has an effect on people that worry about data sovereignty and it also means that we’ve had higher latency on the connections.

    But no more! I was really pleased to read an article in the newspaper yesterday that mentioned that Microsoft is moving Office 365 to the Australian data centres. The article claims this is happening in April next year. I can only hope it’s true as this will be a really good outcome thanks Microsoft!

    Here’s the article: http://www.theaustralian.com.au/business/latest/microsoft-to-deliver-office-365-from-australian-data-centres/story-e6frg90f-1227149621552?nk=41c535e280e2d2c90d15e82eefcb763c

  • DAX Studio 2.0 is out the door

    Learning to write DAX queries is important in the new world of tabular data models. DAX Studio is the best environment right now for learning to write and test DAX queries.

    Today Darren Gosbell announced on his blog that version 2.0 of DAX Studio is out the door. This is a superb effort and one that you should both applaud and use.

    The biggest improvements seem to be around the UI. It looks very impressive but there is much more to it than just the UI.

    image

    DAX Studio can be run standalone (as an exe) or as a ribbon within Excel. Nice!

    If you need to develop and/or run DAX queries, this is now a far superior option than creating them in SQL Server Management Studio.

    Go and download it now from here: http://daxstudio.codeplex.com/

    And also go back to Darren’s blog and leave a note thanking him for the early Christmas present !

  • Microsoft Action Figures–SQL Server and Heroes

    I’ve ended up with a number of Microsoft figurines over the years. It all started with Nine Guy, then went on to the whole Source Force team:

    imageimage

    It’s been interesting to see how they’ve evolved over the years. Here are the SQL Server ones:

    SQL Server 2005 had a demure little lady that was all Red. She morphed into an orange version by SQL Server 2008 (shown here with me at TechEd USA) in 2008. Later she took on a Neo look from the Matrix, and now there’s a guy, the Query Controller.

    image   image

    I love the way that the tradition continues. Microsoft Virtual Academy recent had a Heroes program where you got sent a figurine if you completed one of a specific set of app dev and server courses. (Each course had a number of sub-courses). Here’s the full set:

    image

  • Rett Syndrome Research needs help

    Very proud of my daughters this week. As some of you will know, one of my daughters has Rett syndrome. It's a rare chromosomal disorder that basically only impacts girls. (Boys can get it but they usually do not survive long after birth). Girls develop fairly normally up to about 12 to 18 months of age, then regress markedly. As it affects only about 1 in 10,000 girls, it's not the sort of thing that gets much research funding.

    So my other two daughters have decided to raise some funding to help with a local Rett research program. If anyone is interested, you can make a donation directly here: https://www.mycause.com.au/…/fundraiserforrettsyndromeresea…

    Alternately, my youngest daughter is organising a staged musical for early next year (in Brisbane) with all profits again going directly to the Rett research team. I’ll post more about it closer to when tickets for the musical are available.

  • Perth SQL Server User Group this Thursday 5PM

    Looking forward to seeing the Perth locals at the SQL Server user group this Thursday night at 5PM.

    The topic that the group has chosen is an Introduction to SQL Server High Availability Options.

    It’s on at the BHP offices at 125 St Georges Terrace. If you are coming, please ping Jenny to let her know.

    I look forward to seeing many of you there.

  • Allowing specific non-sysadmin users to query group membership for a login

    I had a lot of good feedback about my post the other day about how to query group membership for a given login.

    One tricky question was about how you could let a specfic user be able to find the group membership for another login, without the user being a sysadmin to run the code. Doing that is a bit trickier but can be done by creating a certificate, a login from the certificate, then assigning permissions to that login, and finally applying a digital signature to the procedure using the certificate.

    Here’s a walkthrough:

    Let’s start by creating a certificate that we’ll use for this purpose:

    image

    Next, we create a login from that certificate. Note that this isn’t a login that can actually log in, but is used as a container for the required permissions:

    image

    We need to then add that special new login to the sysadmin role so they can IMPERSONATE any login:

     

    image

     

    Next we create the stored procedure using the same techniques as I mentioned last time. It would be much simpler if we could just use WITH EXECUTE AS in the procedure to temporarily become an administrator but that doesn’t work.

    image

    We then digitally sign the stored procedure using the certificate. This will give anyone that runs the stored procedure sysadmin permission but only while they are running the procedure, and only if the procedure has not been modified in any way. Any change to the procedure will cause the digital signature to be dropped:

    image

    Just to check that the procedure works ok, I’ll run it as myself first:

     

    image

    That’s all good so let’s now work out if another user can run it:

    image

     

    Looks good to me so let’s clean up:

     

    image

    Hope that helps someone!

  • MVP ComCamp Round 2

     

    The regional MVP folk have organised another MVP ComCamp event. Sessions are available in 5 different languages and many sessions are being broadcast. (Most are English) There are two tracks:

    • Enjoy the PC Life
    • Embrace Cloud and Apps

    If you’d like to attend any of these events, you’ll find more detail here: http://mvp.microsoft.com/en-us/comcamp.aspx

    The list of sessions is here:

    Live Webcasts Nov 17 - Nov 21, 2014

    56 live webcasts, in 2 tracks, featuring MVP speakers will be broadcasted during ComCamp Week, covering a wide range of topics about Office applications and Cloud.

    Session Schedule

    Start Time shown in the tables below is UTC (Universal Time Coordinated). You may use the Time Zone Converter to find out the time based on your location.

    • 1 Mumbai, New Delhi (UTC+5:30)
    • 2 Jakarta, Jakarta Special Capital Region, Indonesia (UTC +7:00)
    • 3 Kuala Lumpur, Singapore, Perth, Hong Kong (UTC+8)
    • 4 Adelaide, Darwin (UTC+9:30)
    • 5 Canberra, Melbourne, Sydney, Brisbane, Hobart (UTC+10)
    • 6 New Zealand (UTC+12)
    • 7 Seattle, Redmond. (UTC-8)

    Click HERE to get to My Schedule on MSEvents

    Track 1 - Enjoy the PC Life

    Day 1: Learn Phase Office 365 - Improve your productivity with Microsoft Office8:30 am (UTC)

    Session Level: Entry

    In this Webcast, we will learn how Microsoft Office products can enhance people’s daily work and collaborative work. We will also take a look at ways to utilize free OneDrive to co-work with Office365 which can result in much effective work management online.

    Debbie Ireland (New Zealand)

    Microsoft MVP for SharePoint Server

    11:00 am (UTC)

    Session Level: Entry

    See how the latest features of Microsoft Office enables the professional worker to continue to be productive on the go. Learn how the latest tools can enable you to determine deeper insight on data, have a broader source of information, and how to present and collaborate to bring the correct data, to the correct people.

    Eduardo Lorenzo (Philippines)

    Microsoft MVP for ASP.NET/IIS

    Day 2: Learn Phase - Next Step with OneNote Nov 18, 2014

    11:00 am (UTC)

    Session Level: Entry

    OneNote has the power to link nearly all your Office applications together. In just 45 min see how you can save yourself time, work as a team with others and bring your work anywhere with you on the road across devices.

    Tan Tian-An (Singapore)

    Microsoft MVP for Office System

    Day 2: Learn Phase - Better Project Outcome with Project Online Nov 18, 2014

    8:30 am (UTC)

    Session Level: Entry

    Microsoft Office 365 allows organizations to manage Projects, Portfolios and Resources within Project Online. Learn how you can manage the utilization of your Global Resource Pool and ensure that your projects are managed in a consistent and repeatable manner.

    Marc Soester (Australia)

    Microsoft MVP for Project

    Day 3: Use Phase - Excel - Beyond the Calc. Nov 19, 2014

    8:30 am (UTC)

    Session Level: Entry

    In this webcast, we will take a look at basic skills, yet very useful, that can help you manage your work with Excel more effectively as you can be more time savings. It will cover up to intermediate level Excel users.

    Ingeborg Hawighorst (New Zealand)

    Microsoft MVP for Excel

    8:30 am (UTC)

    Session Level: Entry (English)

    Many people stop learning beyond simple formulas like SUM(), COUNT() and IF(). Do you know that Excel has 100s of powerful formulas to help you analyze data, solve business problems and get the information you need in an instant? In this session, learn some of the most useful, powerful & easy to understand Excel formulas.

    Chandoo (India)

    Microsoft MVP for Excel

    Day 4: Use Phase – Enriching presentations with PowerPoint Nov 20, 2014

    8:30 am (UTC)

    Session Level: Entry

    We will be learning necessary and powerful PowerPoint functions that will enable our business presentations to be more effective. All beginners and intermediates are welcome.

    Lucy Thompson (Australia)

    Microsoft MVP for PowerPoint

    8:30 am (UTC)

    Session Level: Entry (English)

    In this session, we will explore easy to use, lesser known ways in which you can create more effective presentations within PowerPoint, really fast!

    Geetesh Bajaj (India)

    Microsoft MVP for PowerPoint

    11:00 am (UTC)

    Session Level: Entry

    Microsoft PowerPoint 2013 introduces many built-in new features, tools and settings that can be used to create rich presentation slides. By selecting the right content or media and setting it up properly, we can create a good presentation with a great impact message.

    Bambang Indarto (Indonesia)

    Microsoft MVP for Office System

    Day 5: Try Phase - Hybrid solutions with OneDrive and Office365 Nov 21, 2014

    11:00 am (UTC)

    Session Level: Entry

    Find out how OneDrive as a file sync and share solution lets people work seamlessly together with Office 365 enterprise search, personalization and Delve.

    Patrick Yong (Malaysia)

    Microsoft MVP for SharePoint Server

    Track 2 - Embrace Cloud & Apps

    Day 1: Learn Phase - How to design your application Nov 17, 2014

    12:00 pm (UTC)

    Session Level: Entry

    How do you go about creating your great app idea? Would you design it for the web, mobile, or desktop? If you want to go for all three, which one goes first? What tools would you use? How do you wrap your head around all of it? When will the questions end?! In this session, Jon Limjap will speak on "How to Design your Application".

    Jacinto Limjap (Philippines)

    Microsoft MVP for Visual C#

    Day 2: Learn Phase - Understanding Microsoft Azure Websites & AMS Nov 18, 2014

    8:30 am (UTC)

    Session Level: Intermediate (English)

    The session covers how to Create and deploy your websites on Microsoft Azure platform with easy steps to configure, monitor and scale on demand. Also the session covers using Microsoft Azure mobile services as a back-end service for your application on the go.

    Karthikeyan Anbarasan (India)

    Microsoft MVP for Windows Platform Development

    12:00 pm (UTC)

    Session Level: Intermediate

    This session will cover the basic understanding of Azure Website/AMS, the deployment scenarios and the continuous delivery. It intends to provide the beginner a guidance package that you can start using for your first application from the beginning (development and design) to the end (release).

    Michael Sync (Philippines)

    Microsoft MVP for Windows Platform Development

    Day 3: Use Phase – Advanced Cloud based Application Development Nov 19, 2014

    8:30 am (UTC)

    Session Level: Intermediate (English)

    This session talks in detail about advanced development and deployment scenarios in PaaS - using Azure Cloud Service.

    Kunal Chandratre (India)

    Microsoft MVP for Microsoft Azure

    Day 4: Use Phase – New App development method with HTML5/CSS Nov 20, 2014

    8:30 am (UTC)

    Session Level: Entry (English)

    Are you a Web Developer and wondering how to develop a Windows Store or Windows Phone App? Join this session to explore how to use your existing HTML, JavaScript and CSS skills to develop Apps for Windows Phone and Windows Store.

    Senthil Kumar (India)

    Microsoft MVP for Windows Platform Development

    9:30 am (UTC)

    Session Level: Intermediate

    In this session, you will learn the basics of developing Windows Store 8.1 apps using your existing skills. You will also learn the Windows Library for JavaScript (WinJS) and how to create and debug Windows Store apps in Visual Studio. If you already know the basics of web development, this session will help you to build your first App for Windows Store.

    Bing Xie (Australia)

    Microsoft MVP for ASP.NET/IIS

    12:00 pm (UTC)

    Session Level: Intermediate

    Developing Advanced Universal Windows App using HTML5. This session will discuss how to create Background Tasks and Tile Notification using HTML5/JS programming in the Windows App.

    Senthamil Selvan Velumuthu Samy (Singapore)

    Microsoft MVP for Windows Consumer Apps

  • Identifying Columns with Out-Of-Row Data

    In a previous post, I was talking about how changing data types from the older ntext, text, and image data types to the current nvarchar(max), varchar(max), and varbinary(max) data types doesn't achieve the same outcome as having defined the tables that way in the first place, unless you subsequently rebuild the tables.

    I also had a question about how you can find out which columns still have pointers to out of row data. Unfortunately, finding that out doesn't seem so easy and it would vary row by row in the table.

    To see this, let's start by creating a table, populating it, then changing the data type the same way that I did in the last post:

    clip_image002

    First I ran a query to find the pages that are allocated to the table:

    clip_image004

    Note the there are in-row data pages and LOB data pages. Let's now investigate the contents of page 2579316 from file 1:

    clip_image006

    If we scroll down through the contents of the page, we can find the row data and pointers:

    clip_image008

    Note that this shows that column 3 (RequestDetails) is a Textpointer. You can see from the RowId value where the data is located (File 1, Page 2044944, Slots 0).

    I'd like to automate these steps but that's a project for another day.

  • Table Rebuild Avoids Excessive Lookups After Data Type Change

    I've run into a situation at a number of sites where the following occurs

    • An excessive number of logical page reads during query execution
    • Changes have occurred from ntext, text, or image data types have been replaced by nvarchar(max), varchar(max), or varbinary(max) data types, as part of a clean-up of deprecated data types.
    • Rebuilding the table greatly reduces the number of page reads and the customer is puzzled about why.

    One of the causes for this situation is related to how the data in these columns is stored. The ntext, text, and image data types defaulted to having their data stored out of row. The row contained a pointer to where the data was located. By comparison, the nvarchar(max), varchar(max), and varbinary(max) data types default to storing data in-row where possible.

    The issue is that when the data type is changed, the data isn't moved in-row and won't move until the column is updated. Here's an example:

    Let's start by creating the table:

    clip_image002

    Note that the RequestDetails column is of the ntext data type. It will default to being stored out of row. Let's start by inserting some data into the table:

    clip_image004

    If we query the allocation units that have been created for this table, we see the following:

    clip_image006

    Note that there are both IN_ROW_DATA and LOB_DATA allocation units and both have been used.

    For a comparison, let's create another table dbo.RequestsX that uses nvarchar(max) instead and see the difference:

    clip_image008

    Note that although there are LOB_DATA and ROW_OVERFLOW_DATA allocation units, no pages have been allocated to either of those.

    Now, let's go back to the original table and change the data type of the RequestDetails column to nvarchar(max) and see the difference:

    clip_image010

    Note that the ROW_OVERFLOW_DATA allocation unit has appeared but isn't used. Also the original two allocation units are unchanged in how much they are used. The bottom line is that the data hasn't moved yet.

    Let's now update the column by setting it to its own value and compare the outcome:

    clip_image012

    Notice that the data has basically moved in-row now but we now have some fragmentation.

    So just for completeness, let's rebuild the table completely and compare the outcome:

    clip_image014

    So the important message is that if you go through your code and dutifully replace all the ntext, text, and image data types with nvarchar(max), varchar(max), and varbinary(max) data types, you'll need to rebuild the table to get the best results.

  • Any Australian up for doing a short MVA course?

    OK, been doing a bunch of MVA courses as part of the local Microsoft AU dev div heroes campaign. I need to find 5 Australian citizens who have done at least one of the courses below, and get their email addresses.

    You don't have to do the whole of any badge. For example, you could do SQL Server 1, SQL Server 2, or SQL Server 3.

    Anyone up for it? Or do all of the ones for a badge for a figurine. They are cute. There are some t-shirts on offer too. Details are here:

    http://technet.microsoft.com/en-au/dn771776

     

    image

More Posts Next page »

This Blog

Syndication

Tags

No tags have been created or used yet.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement