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

  • 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

  • Perth SQL Server User Group–November 27th–High Availability–Love to see you there

    Looking forward to delivering another session for the Perth SQL Server user group. Will be the night of 27th November. Here's the session details:

    "Understanding SQL Server High Availability Options

    While more and more systems need ever increasing levels of availability, many customers are confused about when to use each of the high availability options provided by SQL Server. In this session, Greg will provide a detailed overview of log shipping, database mirroring, failover clustering and availability groups, with recommendations on where and when to use each, and the pros and cons of each option. He will discuss all currently-supported versions of SQL Server from 2008 to 2014. If you are confused about SQL Server HA options, this session is for you."

    Contact Jenny Richardson for more details (or ping myself or Mai).

    Hope to catch up with many of our Perth friends while there.

  • Big Changes for Visual Studio and .NET–Where is the Ecosystem for SQL Server?

    Lots of big changes for Visual Studio and .NET were announced today.

    The biggest items are:

    • .NET becoming open source
    • Microsoft work to help move .NET onto Linux and Mac
    • Visual Studio 2013 Community Edition
    • Visual Studio 2015 Preview available
    • Lots of integration for Xamarin developers including Xamarin install from within Visual Studio

    The one that I like most here is the Visual Studio 2013 Community Edition. We’ve had Visual Studio Express for some time but it was very limited. In particular, it blocked any attempt to extend it with plug-ins. Plug-ins are where the real creativity with the product can appear. The new community edition is full-featured and free for all except enterprise application development.

    Full details from Soma are here: http://blogs.msdn.com/b/somasegar/archive/2014/11/12/opening-up-visual-studio-and-net-to-every-developer-any-application-net-server-core-open-source-and-cross-platform-visual-studio-community-2013-and-preview-of-visual-studio-2015-and-net-2015.aspx

    I do hope the SQL Server team are watching this. I like Jamie’s suggestion here about doing the same with SQL Server Developer Edition. As Jamie points out, it barely adds to revenue. Making it free would seem a good idea.

    Cost is one thing but extensibility is another. Whenever there are MVP meetings on campus, I always feel like I’m the one in the room endlessly asking about extensibility when each new feature is shown. And the answer from the SQL Server team is invariably “we haven’t allowed for extensibility in this version but might in the future”. But that almost never happens.

    So many new features fall short of the mark when they are first released but if there were extensibility points, others could contribute to make them more useful. Without those extensibility points, new incomplete features can just flounder. There have been many examples of this over the years. (As an example, ask where the UI for Service Broker is. Klaus had some wonderful work done on building one that he showed us back in 2006 but there’s no supported way to make add-ins for SQL Server Management Studio either. You can hack it but then you need to worry about it being broken by every new update or release that comes out).

    I think this is the difference between shipping a product, and building an ecosystem around a product. I’d love to see SQL Server morph into something that has an ecosystem.

  • Naming CHECK and UNIQUE Constraints

    I’m not a fan of letting the system automatically name constraints, so that always leads me to thinking about what names I really want to use. System-generated names aren’t very helpful.

    Primary keys are easy. There is a pretty much unwritten rule that SQL Server people mostly name them after the table name. For example, if we say:

    image

     

    A violation of the constraint will return a message like:

    image

     

    The name isn’t helpful and it shows us the key value but not which column was involved.

    So, we might say:

    image

    Even in this case, there are a few questions:

    • Should the name include the schema? (ie: PK_dbo_Clients) If not, this scheme has an issue if there are two or more tables in different schemas with the same table name.
    • Should the name include the columns that make up the key? (ie: PK_dbo_Clients_ClientID) This might be useful when an error message is returned. A message that says that you violated the primary key, doesn’t tell you which column (or columns) were involved.

    So perhaps we’re better off with:

    image

     

    I do like to name DEFAULT constraints in a similar consistent way. In theory it doesn’t matter what you call the constraint however, if I want to drop a column, I first have to drop the constraint. That’s much easier if I have consistently named them. I don’t then have to write a query to find the constraint name before I drop it. I include the schema, table, and column names in the DEFAULT constraint as it must be unique within the database anyway:

    image

     

    CHECK constraints (and UNIQUE constraints) are more interesting. Consider the following constraint:

    image

     

    The error returned is:

    image

     

    Note how (relatively) useless this is for the user. We could have named the constraint like so:

    image

    Note how much more useful the error becomes:

    image

    And if we are very keen, we might remove the underscores and delimit the name to make it more readable:

    image

    This would return:

    image

     

    I’d like to hear your thoughts on this. How do you name your constraints?

  • RESOLVED: Missing Checkboxes in Table Memory Optimization Advisor in SQL Server 2014

    I was teaching a SQL 2014 class yesterday and the students were using the current SQL Server 2014 Enterprise (on Windows Server 2012 R2) template.

    We were using the Table Memory Optimization Advisor (right-click a table in Object Explorer within SQL Server Management Studio). I had several people in the class that reported that when they got to the primary key migration screen, that they couldn’t interact with the screen because the checkboxes were not present in the displayed list of columns.

    This is what the screen should have looked like:

    image

     

    This is what it did look like:

    image

     

    Note that there are no checkboxes in the left-hand column. I had never seen that happen before.

    We tried clicking, etc. in the area (wondering if there was some odd font problem or something) to no avail. There seemed to be plenty of room for a checkbox so it seemed like there must be some logical reason why it didn’t want any of these columns as the primary key. But it only happened on some machines.

    Eventually, one of the students resized the rows that were displayed. The checkboxes then appeared.

    This is a basic UI issue. I’ve recorded it here in case anyone else runs into it.

  • Updated Oracle and Teradata Connectors for SQL Server Integration Services

    Nice to see some updated connectors for Oracle and Teradata for SQL Server Integration Services developers/users.

    Version 3.0 of the Attunity connectors have been released. Some of these have substantial improvements. For example, the list of enhanced features for the Teradata connector includes:

    • Expose additional TPT Stream Attributes(TD_PACK and TD_PACKMAXIMUM) to provide maximum tuning flexibility.
    • Support for loading table with columns using reserved words.
    • Fix mapping for TIME(0) to DT_STR SSIS datatype.
    • Can display table name more than 30 characters correctly.
    • Support for block mode and set as default mode.
    • Expose TD_SPOOLMODE for TPT Export for faster extracts.
    • Support for Extended Object Names(EON), which allow UNICODE object names in the Data Dictionary tables.
    • Adding new datatypes (TD_PERIOD_DATE, TD_PERIOD_TIME and TD_NUMBER)

    You’ll find details of the enhancements and downloads at: http://www.microsoft.com/en-us/download/details.aspx?id=44582

  • Minion Reindex 1.0

    I got an email the other day from Sean and Jen at Midnight DBA (www.midnightdba.com) about their new tool Minion for managing index rebuilds and fragmentation:

    image

    You can find details of Minion here: http://www.MidnightSQL.com/Minion

    With these tools, they have been a little more ambitious in some ways than the tools provided by Ola Hallengren (https://ola.hallengren.com/) that have been our favourite tools for this work. I quite liked many of the concepts they have put into the tool. It still feels a bit version-1.0-ish to me but shows lots of promise. I liked the way that it’s all set up with a single script. I would, however, like to see more error handling, etc. in that script. For example, you should be able to run it twice without errors. With the script I looked at, that’s not possible.

    I liked the way they are providing some capture of details from sys.dm_db_index_usage_stats.

    For both this tool, and for Ola’s tool, I wish there was more focus on the index usage stats. Rather than basing decisions about rebuilding or reorganizing indexes based only on fragmentation level, I’d like to see details of how the indexes are used (ie: user seeks vs user scans) playing a much larger role in deciding the operations to be performed. Overuse of reindexing is a primary cause of bloated logs, log shipping failures, mirrors that fall behind, etc.

    Regardless, it’s great to see a new entrant in this area. I encourage you to check it out, see what you think, and more importantly, provide feedback to them. Sean has recorded a video demo of the product which is also available at the site.

  • Determining the Windows Groups for a SQL Server Login

    There was a question this morning on the SQL Down Under mailing list about how to determine the Windows groups for a given login.

    That’s actually easy for a sysadmin login, as they have IMPERSONATE permission for other logins/users.

    Here is an example procedure:

    image

     

    When I execute it on my system this way:

    image

    It returns the following:

    image

    Note that the Usage column could also return “DENY ONLY” or “AUTHENTICATOR”.

  • SQL Down Under Demographics and Technologies

    As most websites do, we collect analytics on the people visiting our site http://www.sqldownunder.com

    I thought it might be interesting to share the breakdown of visitors to our site. Keep in mind that we have a primarily Microsoft-oriented audience. Enjoy!

    No surprise on the native languages:

    image

    Country breakdown reflects the amount of local traffic we have for instructor-led courses. Most others are podcast listeners:

    image

    We first noticed Chrome slightly outstripping IE a while back but recently, it’s changed a lot. I suspect that IE11 will have been as issue here:

    image

    No surprises on the operating systems but Linux continues to disappear from our clients. It used to be higher:

    image

    The big change has been in mobile operating systems. It’s the first time that iOS has only managed 50%. It used to be 82% for us:

    image

    We’re also seeing a shift in screen resolutions:

    image

    And this is the mix of where our site visitors come from:

    image

  • SQL Down Under Show 64 – Ryan Crawcour–What SQL Server DBAs and Developers Need to Know About Azure DocumentDB

    I had the pleasure of recording another SQL Down Under show today.

    Show 64 features Microsoft Azure DocumentDB product group member discussing Azure DocumentDB and what SQL Server DBAs and developers need to know about it.

    JSON-based storage has been one of the highest rated requests for enhancements to SQL Server. While we haven’t got those enhancements yet, DocumentDB nicely fills a gap between NoSQL databases (I use the term loosely Smile ) and relational databases.

    You’ll find the show here: http://www.sqldownunder.com/Podcasts

    Enjoy!

  • Partner events for SQL Server 2014 and Power BI

    Over the last year, I’ve delivered a number of partner enablement events for Microsoft. These events are low cost training sessions that run for three days. Days 1 and 2 cover SQL Server 2014 content, mostly regarding in-memory OLTP, clustered columnstore indexes, and Azure integration with hybrid systems. Day 3 covers the full Power BI stack.

    We’re pleased to be running another set of these around the country:

    Melbourne: November 5th to 7th

    Perth: November 24th to 26th

    Sydney: December 8th to 10th

    I’d love to see many of you there. I’m looking forward to delivering them. To find out more, follow these links:

    Customers: https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032594188&Culture=en-AU&community=0

    Partners: https://training.partner.microsoft.com/learning/app/management/registrationex/LMS_Registration.aspx?UserMode=0&Mode=0&ActivityID=878275

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