SQL

Azure Updates Arrive En Masse

Richard Green on

It seems that the folks in the various Azure teams at Microsoft have been really busy since the Build conference earlier this year getting products to various states of ship and it appears that this month is the mountain when it comes to releases and announcements. I got my usual Microsoft Azure new features and pricing announcements email last night and the number of new features here and features entering GA this month is astonishing.

Azure AD Connect

First and foremost, we have Azure AD Connect entering general availability. This is the replacement to all existing versions of DirSync and the Azure AD Sync tools. Whilst the directory synchronisation feature in itself isn’t anything special to talk about, for anyone still using DirSync, this is certainly a lot nicer to interact with and operate. Additionally, we have a number of the optional features available (a number of which are still classed as preview) to make synchronising your users between on-premise and Azure Active Directory that bit more rich. Sadly, there is still no Yammer integration with this version of Azure AD Connect so you still need to run the Yammer Directory Sync tool.

Azure Key Vault

This is a new service for Azure as opposed to an update to an existing service. Key Vault provides FIP 140-2 certified HSMs in the cloud. A HSM is a Hardware Security Module, a device used to manage security keys in encryption and it commonly found in Certificate Authorities and SQL Servers. I’ve worked with a number of PKI and SQL projects and none of them have ever had technical requirements for HSMs to be in-place. I suspect this has been added to Azure as a service by the request of one or more major companies sitting on the fence over public cloud right now because they need HSMs in order to meet some kind of regulatory or certification.

Azure Application Gateway

This is another new service and one which is really quite amazing in both it’s simplicity and the features that it brings. Azure Application Gateway is essentially an application request routing engine or reverse proxy as an Azure service. It allows you to publish Azure hosted, non-Azure publicly hosted or even on-premise applications to the world. It supports SSL offload to take the key processing workload away from your servers and it can be integrated with Azure Traffic Manager to provide geographic awareness for your applications.

Where I see this being especially useful is for companies currently hosting applications on-premise that are either publicly accessible or published to allow employees or partners access to a service. By publishing applications via Azure Application Gateway instead, companies can have their applications published but without the need to break holes in their own firewalls for those incoming connections.

The pricing on Azure Application Gateway seems very reasonable to me also which is going to make it extremely popular I think.

Azure SQL Data Warehouse

While all the previous service offerings have been general availability, this one is just a limited public preview however Azure SQL Data Warehouse was one of the brand new services announced earlier this year at Build so it’s great to see it moving along. I tend not to get involved so much with large SQL data warehousing work being more of an infrastructure specialist than a data specialist however I can see how if priced suitably, this is going to be a major service of interest for some businesses who want to leverage the cost benefits of the public cloud due to the fact that Data Warehousing can be extremely expensive to properly implement on-premise. This coupled with the fact that Azure SQL Data Warehouse is going to be tightly integrated with Power BI from Office 365 as well as Azure services like Machine Learning is going to make it very easy for customers to do more with their data.

Monitoring SQL Server Agent Jobs with SCOM Guide

Richard Green on

Late last night, I published a TechNet Guide that I have been working on recently entitled “Monitoring SQL Server Agent Jobs with SCOM”. Here’s the introduction from the document.

All good database administrators (DBAs) create jobs, plans and tasks to keep their SQL servers in tip top shape but a lot of the time, insight as to the status of these jobs is left either unturned like an age old stone or is done by configuring SQL Database Mail on your SQL servers so that email alerts are generated which means you have additional configuration being done on every server to configure this and it’s yet another thing to manage.

In this guide, I am going to walk you through configuring a System Center Operations Manager 2012 R2 environment to extend the monitoring of your SQL Servers to include the health state of your SQL Server Agent Jobs, allowing you to keep an eye on not just the SQL Server platform but also on the jobs that run to make the platform healthy.

You can download the guide from the TechNet Gallery at https://gallery.technet.microsoft.com/SQL-Server-Agent-Jobs-with-f2b7d5ce. Please rate the guide to let me know whether you liked it or not using the star system on TechNet. I welcome your feedback in the Q&A.

System Center Service Manager 2012 R2 Data Warehouse Reports Unavailable

Richard Green on

Late last week, I had the pleasure of deploying and configuring a System Center Service Manager 2012 R2 Data Warehouse. I got informed today that none of the reports were available in the Reporting tab in SCSM so I had a look at what the problem might be.

With the SCSM Data Warehouse, the most important job during setup is one of the Data Warehouse Jobs named MPSyncJob. The MPSyncJob has the purpose of deploying all of the management packs from SCSM into the reports folders in SQL Reporting Services (SSRS).

When I looked at this job in the Data Warehouse Jobs tab under Data Warehouse in the SCSM Console, 175/181 has the status Associated but 6 of them were stuck with the status Pending Association and these were all the reporting management packs with this status. Viewing the Management Packs tab under Data Warehouse in the SCSM Console, I could see that these same 6 management packs had a Deployment Status of Failed which is obviously not good.

I logged on to the SCSM Data Warehouse server and poked into the Operations Manager log which is where SCSM records all it’s events and there were a number of critical alerts in the log with the Event Source Deployment and the message went along the lines of insufficient permissions to complete the requested operation so I knew immediately there was a permissions issue with SSRS. I headed over to the SSRS Report Manager URL which normally looks like https://SERVERNAME.domain.suffix/Reports_InstanceName and logged in as myself.

Viewing the permissions on the System Center and Service Manager report folders, I quickly could see that the account that I specified during the setup of the SCSM Data Warehouse was missing, the installer had not properly assigned the permissions to the account.

I manually added the permissions to the account and restarted the deployment of the management packs in a failed state and the Operations Log has now reported that they have successfully been deployed, happy days. Now I just need to wait for SCSM to complete all of the other jobs in the appropriate order to get the full functionality through from our Data Warehouse.

 

SQL Server Maintenance Solution

Richard Green on

Earlier this year, I posted about a tool from Brent Ozar called spBlitz and how it gives you amazing insight into configuration problems with your SQL Servers. Well today, I am here to tell you about another great SQL tool available for free online and that is the SQL Server Maintenance Solution by Ola Hallengren, a Swedish database administrator and was awarded a Microsoft MVP this year for the first time.

You can download his tool from https://ola.hallengren.com/ and on the site, there is full documentation for all of the features of the tool including the most common configuration examples and its use so you can get up and running really quickly with it.

The SQL Server Maintenance Solution is a .sql file that you download and allow it to install itself as a series of Stored Procedures in your master database. The tool works by invoking its Stored Procedures as SQL Agent Jobs and by default will create a number of these unless you opt not to during the install by changing one of the lines in the .sql file.

I opted to not install the default jobs but to create my own so I could configure how and what I wanted the scripts to do but it really is so simple that no administrator of SQL has any reason to not be performing good routine maintenance. I am using Ola’s scripts to both perform routine DBCC CHECKDB consistency and also to perform index defragmentation on databases which is it’s real power.

The reason Ola’s scripts beat a SQL Maintenance Plan for index defragmentation and the main reason I wanted to use them is that Ola gives us the flexibility to perform different actions according to the level of fragmentation so for example, I could do nothing if fragmentation in an index is below 10%, reorganise an existing index if fragmentation is between 10% and 30% and completely rebuild the index if it is over 30%. Compare this to a SQL Maintenance Plan where your option is reorganise or rebuild regardless of fragmentation level and you can see the advantage.

So now, that’s to the community and Brent and Ola, we can check the configuration of our SQL Servers to make sure they are happy and safe as well as easily configure our daily and weekly checks and maintenance on databases to keep our server and our databases happy and we all know that happy databases means happy software.

In another post coming up soon, I will show you how we can update the configuration of our SCOM Management Pack for SQL Server so that we can receive alerts for failed SQL Server Agent Jobs, allowing us to centralise our knowledge, reporting and alerting for SQL maintenance tasks.

A Swathe of Microsoft Azure Updates

Richard Green on

I’ve been a bit lazy over the last couple of weeks when it’s come to blogging a) because I’ve been on the road quite a bit with work and I haven’t fancied sitting in front of my PC when I got home in the evening and b) I’ve been too hooked watching Ray Donovan on TV to think about picking up the laptop.

The problem with not blogging for a while is that I have a lot of pent up desire to post things that I’ve been thinking about and doing over the last couple of weeks, not enough time to do it, nor the will power to type it all out.

As we all know, Azure is fairly close to my heart these days and three’s been a lot of activity in Azure across a whole host of offerings.

The biggest changes are covered in full in the blog post by Scott Guthrie over at http://weblogs.asp.net/scottgu/azure-sql-databases-api-management-media-services-websites-role-based-access-control-and-more.

Azure SQL Service Tiers

For me and my love obsession with running WordPress on Azure, the biggest changes here are the General Availability of the Azure SQL Database Service Tiers. These are the tiers which have been in preview since early this year and are due to replace the legacy tiers next year. The good news here is that Microsoft appear to have made a change during the course of the year which means you don’t need to actually migrate your data and you can simply switch between the tiers so there’s no excuse now.

Azure Websites

Another big change is to Azure Websites. Azure Websites have previously not been able to integrate with a Virtual Network to allow you to easily consume on-premise resources as part of a website. You could get around this to an extent using a BizTalk Hybrid Connection however the setup of this required agents to be deployed across the servers you wanted to connect to and meant extra configuration and complexity. We can now consume resources on-premise via our Virtual Network to on-premise resources whether it be a SQL Server, a back-end application server or whatever your website needs.

As part of the website changes, there is a new gallery template available for Websites named Scalable WordPress. This is a WordPress site deployment on Azure Websites designed for Azure which includes pre-configuration to use Azure BLOB Storage and easy configuration for Azure CDN. This new template potentially puts all my work to hone WordPress for Azure to the waste heap. As a WordPress user and fan, I’m going to be deploying one of these sites in the next few days (maybe longer) to see how Microsoft have built the site template. My money is on either they have used plugins to achieve it in the same way I do or they’ve customized the code base to make it work. Either way, I’ll be interested to see.

Azure RBAC

Finally, at last, the feature that we’ve all been wanting, needing and waiting for. No more, is a subscription the boundary for security and access control in Azure as with the release of Role Based Access Control (RBAC), we can now control access to resources in our Azure subscriptions. I’m really looking forward to having a poke around with this feature as I see this being one of the biggest features ever with Azure.

Azure Active Directory (AAD) Sync

In a separate article over at http://blogs.technet.com/b/ad/archive/2014/04/21/new-sync-capabilities-in-preview-password-write-back-new-aad-sync-and-multi-forest-support.aspx it was announced that the latest version of the AAD Sync tool has come out of Preview and is now in General Availability.

This new version supports Self-Service Password Reset write-back to Active Directory Domain Services (AD DS) with DirSync and Multi-Forest sync for complex domain and Exchange Server topologies.

Password Write-Back for organisations using AAD could be really good thing, just bear in mind before you get too excited about the reduction in service desk calls you can achieve through self-service password reset, you need to meet the prerequisites for the writeback agent which are pretty simple but you also need to be paying for Azure Active Directory Premium.

All in all, this has been a great month for Azure and I’m looking forward to trying to get my teeth into some of these new features.

Brent Ozar and the Free SQL Server Content

Richard Green on

SQL Server is a great product however it’s not something I often talk or rave about. It’s the unsung hero of the majority of the software we use and a lot of the time, we don’t look after it properly and that’s assuming we deploy it properly in the first place. A colleague and friend of mine @LupoLoopy was at a SQLBits conference last week where Brent was speaking and it pipped my forgotten interest for SQL Server so I took to Brent’s site for some SQL inspiration.

It didn’t take long for me to find some great material. If you are in the SQL Server business then I’d really recommend some if not all of this material to you. I haven’t gotten through them all myself yet, but the eBooks I have no doubt are great insightful reads and the tools, sp_Blitz and sp_BlitzIndex will be so useful to you, you’ll probably wonder how you lived without them as I did when I first saw them.

Please don’t thank me for any of these tools and documents as they are all property of Brent Ozar Unlimited, his SQL Server practice but please do thank me for showing them to you if you haven’t already heard of Brent. If you haven’t heard of Brent then he is a SQL Server Master and a Microsoft MVP for SQL Server: a big deal basically.

SQL Server Tools

sp_Blitz is a free tool that gives your SQL server a full bill of health and tells you everything you want to know but didn’t know was wrong with it. My personal feeling is that this tool should be made mandatory to run against all SQL servers at periodic intervals to keep them in a sensible state of health.

sp_BlitzIndex is another tool but instead of checking out the health of your SQL Server, this checks the health of your database indexes so that you can get the most performance out of your databases.

SQL Server eBooks

SQL Server 2005, 2008, 2008 R2, 2012 and 2014 Setup Guide. This is a full on how to setup SQL Server by Brent book and probably number one on your reading list if you are ever installing SQL Server.

AlwaysOn Availabity Groups Setup Checklist isn’t a book as such but it’s a very helpful ticksheet you can use to make sure that when configuring SQL Server AlwaysOn Availability Groups that you haven’t missed a step and be left scratching your head wondering why it isn’t working as you had designed.

High Availability and Disaster Recovery Worksheet is the final example I like from Brent which helps you to decide which HA and DR technologies you should employ in your SQL Server designs. This is a really simple yet effective sheet to have with you if you design SQL Server deployments.

New Service Tiers for Azure SQL Databases

Richard Green on

Last night I received an email from the Microsoft Azure team with an announcement for a change to the functionality of Azure SQL Databases. At present, there are two service tiers available for Azure SQL Databases, being Web and Business with limits on size relative to each. As anyone who has read my guide on TechNet Gallery entitled Configuring a SQL Azure Sync Group will know, I’m quite into these DBaaS offerings in Azure. Yesterday, they announced in preview the release of three new service tiers for the Azure SQL Databases service.

What’s in the Announcement

The three new tiers announced are named Basic, Standard and Premium. In twelve months time, Microsoft will be ceasing the current Web and Business tiers in favour of these new tiers currently in preview.

  • Basic: Designed for applications with a light transactional workload. Performance objectives for Basic provide a predictable, hourly transaction rate.
  • Standard: Standard is the go-to option for getting started with cloud-designed business applications. It offers mid-level performance and business continuity features. Performance objectives for Standard deliver predictable, per-minute transaction rates.
  • Premium: Designed for mission-critical databases, Premium offers the highest performance levels and access to advanced business continuity features. Performance objectives for Premium deliver predictable, per-second transaction rates. In addition to this, there are going to be revisited scaling limits for the tiers, uptime SLA, backup and recovery options and disaster recovery options.

Basic will have a 2GB limit, an increase from the 1GB limit in the current Web tier. Standard will have a 250GB limit whilst Premium will have a 500GB limit. Restore points for recovering the databases will be available for 24 hours on Basic, 7 days on Standard and 35 days on Premium. All the tiers come with a 99.95% uptime SLA.

New Tiers Pricing

The good news is that if you jump on the band-wagon early, you get reduced pricing during the preview. In an example scenario, using the North Europe Dublin datacentre and billing in Pounds (GBP) on a Pay as You Go tariff, Web and Business edition for a 100MB database is £3.179 per month. A Basic database of the same size is £1.60 per month, Standard is up to £64 per month according to usage and Premium varies wildly between £296 and £2,368 according to usage. It’s interesting to note the high end pricing on Premium which dependant on use can actually work out more expensive than running a SQL Server IaaS virtual machine in Microsoft Azure but that’s the price you pay for design simplicity of DBaaS over SQL Server IaaS.

If we use my blog here at richardjgreen.net as an example where I currently use Web databases, if I moved from Web to Basic under the new tiers, I would see a monthly decrease in cost of about 50%.

What Will Happen to Web and Business

All we know at the moment is that these two legacy tiers will be phased out in twelve months time. There doesn’t seem to be any indication as to how databases would be transitioned from the existing Web and Business tiers over to the new tiers but I would hazard a guess that Web databases will become Basic and Business databases will become Standard.

This above statement is assuming of course that there is compatibility between the current tiers and the new and that the databases will be transitioned seamlessly. I think it would be a bad PR exercise for Microsoft if existing databases were dropped instead of transitioned over to the new tiers as that’s going to put extra work down for customers already consuming these services.

Accessing the Preview Tiers

In order to access the preview tiers, login to your Microsoft Azure Account Portal, the production portal and not the new Preview Portal. You can access this part of the Azure portal at https://account.windowsazure.com if you haven’t accessed it before.

From here, click the Preview Features link in the top navigation.

Azure Portal Preview Features

From the Preview Features page, scroll down until you can see the New Service Tiers for SQL Databases option.

SQL New Tiers Try Now

Click the Try It Now button alongside the preview feature entry.

SQL New Tiers Add Feature

You will be presented with a dialog to select which subscription you wish to enable that feature for. I only have one subscription so I only have a single selection in the drop down. Click the tick button in the bottom right once you have the correct subscription selected. You will be taken back to the previous page once it’s done and you will be sent a welcome email for the preview.

SQL New Tiers Active

The preview features page in the portal will update to also show a caption under the New Service Tiers for SQL Databases button You Are Active to show that you are participating in this preview service.

With this enabled, we can head over to the Management Portal using either the Portal link in the upper right or by navigating to https://manage.windowsazure.com to try out the feature.

SQL New Database Custom Create

From the Management Portal in Microsoft Azure, I have clicked into SQL Databases and selected the New Custom Create option. As you can see in the new database wizard, in addition to the current tiers for Web and Business, we can now select from our three preview tiers, Basic, Standard and Premium also.

SQL Database Features Support

The current crop of SQL Databases support the Automatic Backup and Sync features. I haven’t had a chance to explore the support for these with the new tiers yet but I’ll be back soon with just that information. I will be interested to find this out for myself as transitioning from Web to Basic would save me on my monthly Azure bills but if Sync isn’t available in this tier then I’m probably going to be paying more to use Standard.

Configuring a SQL Azure Sync Group TechNet Guide

Richard Green on

Back in January, I drafted a blog post on how to configure a SQL Azure Sync Group to provide database high availability and geo distribution. I decided that actually there was so much content there, it would have been too long for a blog post so I have published it instead as a .pdf document on TechNet Gallery instead.

The great news is that the guide is now published and you download it for yourself at http://gallery.technet.microsoft.com/Configuring-a-Windows-73847ad3.

Please let me know what you think of it as this is my first publication of this format. If you have any questions, comments or have a topic request for another guide then please get in touch and we’ll see what I can do.

WordPress Database Index with SQL Azure

Richard Green on

As part of a moving my online services between two Windows Azure subscriptions last week, I did some upgrades to the blog including moving the database to Windows Azure SQL (SQL Azure). To facilitate this, I’m using the WP DB Abstraction plugin for WordPress available from http://wordpress.org/plugins/wordpress-database-abstraction/. Using this plugin does take a bit of guts I hasten to add as it hasn’t been updated in over two years and it will prevent some plugins from functioning but for core WordPress it’s great.

After migrating the site to the new subscription I was doing some validation checking in the SQL Azure Management portal. I was querying the database for various things and I noticed that there were no indexes on any of the tables, a byproduct of the WP DB Abstraction plugin translating the native WordPress MySQL syntax into MSSQL I suspect. Luckily for me, WordPress have a great in-depth article on their Codex for the database schema, mappings for all of the primary and foreign keys and most importantly, all of the indexes.

Using the SQL Azure Management Designer, I was able to create the indexes in SQL Azure to match the WordPress MySQL specification. If you are using WP DB Abstraction for your Widows Azure Web Sites WordPress installation with SQL Azure, I strongly recommend you take a look at your own indexes to see if any exist and if not, look at all of the details on the WordPress Codex article at http://codex.wordpress.org/Database_Description for what indexes should exist.

If I get a chance in the coming days, I’ll update this post with a T-SQL snippet which you can dump into SQL Server Management Studio to create the indexes for you.

SQL Single User Mode Logon Failed

Richard Green on

Ran into an issue today with one of our SQL instances where we needed to bring it up in Single User mode to recover the sysadmin role, however every time we tried to login we got the fateful “Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)” error.

Found this great page on MSDN which you should pay close attention to if you are in a clustered environment as we are:

http://msdn.microsoft.com/en-us/library/ms188236.aspx

The important thing to note is that the cluster resource .dll will allows get the first and only connection before you have a hope in hell of getting it. The way around this is to start the MSSQL$INSTANCENAME using the net start command manually on one of the nodes and not using the Failover Cluster Manager.

This will allow you to use sqlcmd to generate the login you need to regain access.

Enjoy 😉