THE SQL Server Blog Spot on the Web

Welcome to - 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

  • Two types of SSL available now for Azure Websites

    If you have followed any of the Azure website forums, you'd realize that having SSL available was one of the highest rated requests. That's hardly surprising given the number of clients that want to use these sites for eCommerce work.

    So it's really good to see that Scott Guthrie and the team have been listening and that SSL is now available. The pricing is shown here: To see the details, click on the "SSL" button in the "Learn More" area. An FAQ is also provided.

    SSL has been provided in two forms. SNI (Server Name Indication) is the method that allows multiple sites to share a common IP address yet have different certificates. That's the lower cost option currently at $6 USD per month.

    Most browsers nowadays work properly with SNI based SSL, but if you do have to deal with earlier versions that don't (such as IE6 or browsers on Windows XP), you can get an IP based SSL configured instead. That currently costs $26 USD per month.

    This is really good news.

  • Two New Course Offerings - (Sydney) Query Performance Tuning and Advanced T-SQL (Melbourne) SQL Admin

    I'm really pleased to see the number of courses that we're offering is increasing.

    By popular demand, we've extended our Query Performance Tuning and Advanced T-SQL class to Sydney. Previously, we've only been running this course in Melbourne but demand in Sydney for it has continued to grow. For those that might like a Sydney-based offering, you'll find details here now: (Also, as usual, we have several ways to attend the class. The first two days on query performance tuning, and the last three days on Advanced T-SQL are also available as standalone courses).

    In addition, we've had so many requests for a straightforward SQL Server administration class. In many cases, potential attendees are keen to prepare for Microsoft's 70-462 exam. We've now created a suitable course. We have focussed on practical knowledge that is useful to anyone that needs to administer a SQL Server system. Many administrators fit into the "accidental DBA" category. If you are in that category or if you are an existing developer or DBA that wants to refresh your skills, we'd love to see you attend. If you are keen to try to get certified, we think it now provides the best coverage of topics for that exam, when compared to any other 5 day course on the market. The first offering will be in Melbourne starting 15th July. Details are here:

    A strength of all our current courses is that we understand that not everyone is yet on SQL Server 2012 and that many students work in environments that have more than one version of SQL Server being used. While the courses are based on 2012, they are suitable for those working with SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2. We'll carefully point out the differences during the courses.

  • Performance Guidance for SQL Server in Windows Azure Virtual Machines

    One of the things that I've been busy with lately is reviewing a series of upcoming whitepapers from Microsoft.

    The SQLCAT team today launched one of those whitepapers. It's a performance guide for SQL Server when working with Azure VMs (Virtual Machines).

    It's great to see that it's been released and you'll find it here:

    There's more to working with SQL Server in Azure than just putting it onto a VM. It's great to see much of this now documented.


  • SQL Server Service won’t start after changing service account - service-specific error %%-2146885628

    Yesterday I was at a site where they decided to change the service account for the SQL Server services on a set of systems. After changing the service accounts, SQL Server restarted just fine on all machines except one.

    I had used the SQL Server Configuration Manager to make the changes (important to not just use the Services applet in Administrative Tools) but I got the typical error telling me that the service wouldn’t start in a timely fashion. The server was running SQL Server 2008 R2 SP2.

    Looking in the system event log produced the following errors:

    The SQL Server (MSSQLSERVER) service terminated with service-specific error %%-2146885628.

    A fatal error occurred when attempting to access the SSL server credential private key. The error code returned from the cryptographic module is 0x8009030d. The internal error state is 10001.

    I spent a while looking for info on the last error and found a site where they discussed that it was generated when the service account could not read the machine keys that were stored in the  C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys folder. The article then discussed how to add read permissions for the keys one by one.

    It suddenly dawned on me that it was probably a problem with the permissions on the folder instead. Checking the permissions on that folder made me realize that the local Administrators account should have the ability to read it. The service account was meant to be a member of the local Administrators account but had not been added to that group on this machine.

    Adding the service account to the local Administrators group on the machine (note: not the domain administrators account) fixed the issue and the service started again, until I could get the correct account permissions set in the morning when other staff came back. (In the comments I've added a list of what's actually required).

    Hope this helps someone else. (And helps me the next time I see this and have forgotten what it was J)

  • Huge news: Azure expanding to Australia!!!

    I was so glad to hear today that Azure is expanding to Australia. This helps with two remaining areas of concern that I've heard from a variety of customers:

    • Compliance and data sovereignty (not wanting to store data outside Australia)
    • Latency (previously high latency even to our nearest external data centres)

    With both these concerns now disappearing, it's time for more Australian customers to get involved with Azure if they've been resisting so far. 

    Two Azure sub-regions are to be added. One for New South Wales and another for Victoria. In addition, data geo-replication between the sub-regions will also be available.



  • Private Cloud and Virtualisation Strategy Event - Brisbane, Sydney, Melbourne

    Fellow MVP Alessandro Cardoso sent me information today about a private cloud and virtualisation event that's running later this month.

    It's being offered in Brisbane, Sydney, and Melbourne.

    If that's of interest, you'll find more details here:

  • SQL Down Under Show 59 - Guest Reza Rad now available for download

    Hi Folks,

    On Thursday night, I got to record a podcast with Reza Rad. I was interested to speak to Reza after reading his SQL Server 2012 Integration Services Cookbook.

    In the show, we discuss SQL Server Integration Services (SSIS) extensibility and some aspects of performance tuning for SSIS.

    You'll find the show here:


  • Another large set of Azure enhancements: including some for SQL folk

    I'm really impressed by the rate that the Azure team is making and releasing enhancements. Scott Guthrie posted about these again recently.

    The following items in the announcement were ones that I think can be useful to those with an interest in SQL Server:

    * General availability of Azure VMs. While we've had SQL Database and SQL Reporting available as services in Azure, we don't have the other parts of the database platform. With Azure VMs, you can spin up a VM to run SQL Server.

    * SQL Server templates. You don't need to install SQL Server when creating a VM. There are templates that have this already installed. You can also save your own templates. They've also provided BizTalk and SharePoint templates.

    * Hourly billing for the templates above. This means that if you just need a SQL Server system for a few hours, you can easily do that.

    * Virtual Networks. You can create a virtual network, including the ability to stretch that out to your on-premises systems.

    * Larger memory VMs. If you really want to try out tabular analytic models, etc. you can now get up to 8 cores and 56GB of memory in a VM.

    * Durable data disks from storage can now be up to 1TB in size each.

    * Significant reduction in the price of the VMs.

    You'll find Scott's announcement here:

  • Warning: Lost my stored Azure credential details in SSMS after applying CU3 + Hotfix

    I posted the other day about a hotfix that's needed after you apply SQL Server 2012 SP1 CU3, to still be able to open or edit SSIS projects or database maintenance plans in SSDT.

    However, I found that after I applied CU3 and the hotfix, that all my stored credential details for SQL Servers was gone. That was particularly nasty for all my Azure-related logons. I had to set them up again.

    No idea why that happened but figured I should give you all a heads-up about it!

  • New Features added to Azure today - AD, Backup & Website Monitoring

    It's great to see the constant roll-out of capabilities across the Azure platform.

    One in particular that many have been waiting for is Active Directory. Scott Guthrie posted today that it's now available for production use. This is particularly significant and also enables options for on-premises directory sync and federation. If you have been using Office365, you have already been using Azure AD.

    Scott also mentioned that they are about to open a preview of a link from OAuth 2 (or OpenID) to Azure AD. That will enable you to use alternate credentials (such as Twitter credentials) to authenticate against Azure AD.

    They have also added an Azure Backup option and added a variety of monitoring options for Azure Websites, including the ability to retrieve website logs via FTP.

    Scott's post about this is here:

  • Another government certifying Azure: UK

    One of the common objections that comes up when talking to people about storing their data in Azure, is that "we are not allowed to do it". In so many cases, that simply isn't true, or it's true for a small part of the overall data. In other cases, it's a case of no-one wanting to be seen to be the first in their industry to do so, for some perceived liability reasons. Again, most of these are unfounded.

    The thing that can change these opinions though is certification by governments. Some have already done so and it's great to see the announcement that Windows Azure received G-Cloud Impact Level 2 Accreditation from Cabinet Office for use across the UK Public Sector. You can read more about it here:

  • FIX: SSIS Project Issues with SQL Server 2012 SP1 CU3 (Also applies to Maintenance Plans)

    Cumulative Update 3 for SP1 for SQL Server 2012 caused a number of problems with SSIS packages. This also applies to database maintenance plans as they are also SSIS packages.

    After applying the update, you could get this error when opening an SSIS package:

    The type initializer for 'Microsoft.DataTransformationServices.Design.SharedIcons' threw an exception.
    Value of 'null' is not valid for 'stream'. (System.Drawing)

    If you edit a maintenance plan, you could get this error:

    Provide value on 'System.Windows.Markup.StaticExtension' threw an exception. (PresentationFramework)
    Exception has been thrown by the target of an invocation. (mscorlib)
    The type initializer for 'Microsoft.SqlServer.Graph.Extended.HighContrastSupport' threw an exception. (Microsoft.SqlServer.IntegrationServices.Graph) 

    A fix has been released to address these issues. You should apply it in conjunction with CU3, whenever you are deploying CU3. You'll find details here:



  • SQL Down Under Show 58 - Marco Russo - SQL Server Analysis Services Tabular Data Models

    In Melbourne, it was the coldest morning of the year so far but I didn't mind getting up at 5:30AM to record an interview with Marco Russo. Marco is well-known in the SQL BI community for his writing and presentations.

    SQL Down Under show 58 is now online:

    In this show, Marco discusses concepts and experiences from working with tabular data models in SQL Server Analysis Services.


  • Using Existing Windows Azure SQL Databases with Azure Mobile Services

    I was one of the speakers today at the 6 Weeks of Azure bootcamp event in Sydney today. During the day, as well as speaking, we have an "unconference". There's a lot of discussion on whatever the attendees want to discuss.

    One of the offline questions I got today though was about how to hook up Azure Mobile Services to an existing Windows Azure SQL Database rather than using the one that Mobile Services can auto-create. While getting that to work, we learned a few things:

    • Tables need to be created in a schema that has the same name as the Mobile Service. (ie: if I want a table called CurrentTasks, and my service is called TaskManager, I need to have my table called TaskManager.CurrentTasks.
    • Tables need to have a clustered primary key that is an integer and is called "id" (it appears case-sensitive in the app for some reason)
    • After you modify the mobile service to point to your database (or after you create a mobile service referencing the existing database), you need to add each table to the service by using the mobile services portal. (If you reference a table that doesn't exist, that's when it would also create the table)

    The attendee had already created his tables in the dbo schema. We moved his tables by using:

    ALTER SCHEMA servicename TRANSFER dbo.OldTableName;

    It's also worth noting that Mobile Services isn't yet available in all datacenters. The attendee's database was in a different datacenter and it's important to try to avoid that, or the latency can be a big issue. He and another attendee were asking about the best ways to move the database to another datacentre. One option for this is to export a BACPAC of the database to Azure Storage, and to then import the database to the alternate server.

    Unfortunately, even though CREATE DATABASE AS COPY OF works across servers within the same datacenter, it doesn't work for servers in different datacenters.


  • Whitepaper editing <sigh>

    Hi Folks,

    I posted a day or so back about the whitepaper on plan caching and recompilation being published.

    Today I had a query from a reader and found that the editor has messed up an example.

    For example, I had this example of a query that's almost identical but where the query plan would be different:

    SELECT ProductID
    FROM Sales.SalesOrderDetail
    GROUP BY ProductID
    HAVING AVG(OrderQty) > 5
    ORDER BY ProductID;

    SELECT productid
    FROM Sales.SalesOrderDetail
    GROUP BY ProductID
    HAVING AVG(OrderQty) > 5
    ORDER BY ProductId;

    "Helpfully" the editor changed it to:

    SELECT ProductID
    FROM Sales.SalesOrderDetail
    GROUP BY ProductID
    HAVING AVG(OrderQty) > 5
    ORDER BY ProductID;

    SELECT ProductID
    FROM Sales.SalesOrderDetail
    GROUP BY ProductID
    HAVING AVG(OrderQty) > 5
    ORDER BY ProductID;

    And undid the point of the example...

This Blog



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