Monday, September 25, 2017

Automatically scaling up and down between pricing tiers in Azure SQL databases

We want a certain level of performance for our Azure databases, but sometimes we don't need that performance 24/7. And if we don't need it, we don't want to pay for it!

So, I put together a script that can be run regularly (perhaps hourly) and has a set of specific business hours during which we want to be scaled up, along with the tiers that we want to be scaled to. The business hours are set up to be 7 AM to 10 PM. Then during non-business hours, we scale down to the appropriate level. It takes into consideration weekends/weekdays as well.

This is currently running with SQLCMD on a VM, but we'll probably switch to Azure Automation eventually.

The heart of the code is the @PricingTierTarget table (done as a table variable in the actual code below). It has all the DB names, and the scale up/scale down information.

Note that you use this code at your own risk! I would particularly be aware of switching between editions (for instance, switching from Standard to Basic). This will affect the point in time to which you can restore the database (among other features that differ between the editions).

Switch Azure databases between pricing tiers depending on whether the current time is business hours or not

If it's during business hours, then mode is ScaleUp.
If it's not during business hours, then mode is ScaleDown.

Our main database (I'm calling it MainDB database here) is scaled between Standard S2 to Standard S4.
All other databases are scaled down to Basic Basic and must be manually scaled up

Note - this code will run and complete, but the databases take longer to switch tiers. That 
may take a minute or two (depending on size).
To see when it actually finishes, you can check the table sys.dm_operation_status.


Set nocount on

    @Now datetime = GetDate()
    ,@SQL nvarchar(3000) = ''
    ,@BusinessHoursStartTime tinyint = 7
    ,@BusinessHoursEndTime tinyint = 20
    ,@ScalingMode varchar(20) = 'ScaleDown'
    ,@SingleQuote  nchar(1)    = char(39)
    ,@NewLine char(2) = char(13) + char(10)
    ,@Message nvarchar(3000)

    @PricingTierTarget table (
        DBName sysname
        ,ScaleUpEdition varchar(20)
        ,ScaleUpServiceObjective varchar(20)
        ,ScaleDownEdition varchar(20)
        ,ScaleDownServiceObjective varchar(20)
        ,CurrentEdition varchar(20)
        ,CurrentServiceObjective varchar(20)
        ,TargetEdition      varchar(20)
        ,TargetServiceObjective varchar(20)
        ,NeedAlterDatabase  bit default 0

-- Convert to Central Standard Time    
Select @Now = @Now AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time'

-- Determine if we should scale up or scale down
    -- Is it a business day?
    ((Datename(dw,@Now) in ('Monday', 'Tuesday','Wednesday','Thursday','Friday')) 
    -- Are we in business hours?
    (Datepart(hour, @Now) between @BusinessHoursStartTime and @BusinessHoursEndTime))
    Set @ScalingMode = 'ScaleUp'
-- Populate @PricingTierTarget table    
Insert into @PricingTierTarget (DBName,ScaleUpEdition,ScaleUpServiceObjective,ScaleDownEdition,ScaleDownServiceObjective)
-- DBName                   ScaleUpEdition  ScaleUpServiceObjective   ScaleDownEdition    ScaleDownServiceObjective
('MainDB'              , 'Standard'    , 'S4'                    , 'Standard'        , 'S2')
,('MainDBDev'          , 'Basic'       , 'Basic'                 , 'Basic'           , 'Basic')
,('MainDBStaging_Map'  , 'Basic'       , 'Basic'                 , 'Basic'           , 'Basic')
,('MainDB_WebDev'      , 'Basic'       , 'Basic'                 , 'Basic'           , 'Basic')

-- If there are NEW databases, set them up as basic/basic
Insert into @PricingTierTarget (DBName,ScaleUpEdition,ScaleUpServiceObjective,ScaleDownEdition,ScaleDownServiceObjective)
Select Name, 'Basic'       , 'Basic'                 , 'Basic'           , 'Basic'
From sys.databases 
    Name not in (Select DBName from @PricingTierTarget)
    and Name <> 'Master'

-- Get what the databases are currently set to - the current Edition and Service Objective
Update PricingTierTarget
    CurrentEdition = slo.Edition
    ,CurrentServiceObjective = slo.Service_Objective
from @PricingTierTarget PricingTierTarget
    Join sys.databases db
        on db.Name = PricingTierTarget.DBName
    Join sys.database_service_objectives slo    
        on db.database_id = slo.database_id 
-- Loop through the databases, finding those databases that need to be altered because they are not
-- at the appropriate pricing tier 
If @ScalingMode = 'ScaleUp' Begin
    Update @PricingTierTarget
        TargetEdition = ScaleUpEdition
        ,TargetServiceObjective = ScaleUpServiceObjective
        ,NeedAlterDatabase = 1
        CurrentEdition <> ScaleUpEdition
        CurrentServiceObjective <> ScaleUpServiceObjective

else if @ScalingMode = 'ScaleDown' Begin
    Update @PricingTierTarget
        TargetEdition = ScaleDownEdition
        ,TargetServiceObjective = ScaleDownServiceObjective
        ,NeedAlterDatabase = 1
        CurrentEdition <> ScaleDownEdition
        CurrentServiceObjective <> ScaleDownServiceObjective

-- Start setting up @Message
Select @Message = @NewLine + @NewLine + 'Current time in Central Standard time zone: ' + convert(varchar(20), @Now) + @NewLine 

-- Generate the Alter Database script where NeedAlterDatabase is True
Select @SQL = @SQL + 
    'Alter database ' + DBName + ' modify (Edition = ' 
        + @SingleQuote + TargetEdition + @SingleQuote 
        + ',SERVICE_OBJECTIVE = ' 
        + @SingleQuote +  TargetServiceObjective + @SingleQuote + ')'
        + @NewLine
From @PricingTierTarget
    NeedAlterDatabase = 1

If @@Rowcount > 0 Begin    
    -- If databases exist that need their pricing tier changed, print a message and run the alter database script
    Select @Message = @Message + 'Running SQL: ' + @NewLine + @SQL
    Print @Message
    Exec sp_executesql @SQL

Else Begin
    -- No databases need to be changed - just print message
    Select @Message = @Message + 'No databases need to have their pricing tier changed'
    Print @Message

Friday, February 3, 2017

Why most databases have so many useless and confusing objects in them

Recently I was working with a database system, trying to validate some output from an OLAP cube. I ran into discrepancies, and (with a lot of work and troubleshooting) narrowed it down to the fact that in one query, two tables were joined with the Product_ID field, and in another query, the two tables were joined via the dim_Product_ID field. These two fields, Product_ID and dim_Product_ID were identical most of the time, but not always! They were different enough to cause major problems trying to match numbers.

Here's another example of useless and confusing objects - the database system I'm working with now is littered with the remains of what look like three abortive attempts to create a data warehouse, all of them currently unused. But the fact that there's all these separate tables and databases out there, replicating very similar data in different formats - it's enormously confusing to anyone trying to do new work on the system, and trying to figure out what's being used and what's not.

Why is it? Why do databases become cluttered with unused objects, that cause massive headaches for anyone trying to use the data?

Here's what I think. It's a heck of a lot of work to try to figure out what's used and what's not, and archive off/delete the unused objects. Not only is it a a lot of work, but it's unrewarding and unappreciated work. You need to check with lots of people, try to figure out what might actually be used, compare lots of code and data.

And most managers don't really have a solid understanding of how much more work basic database tasks can be if the database contains a lot of leftover tables and other database objects. So the task of cleanup is never prioritized. Even though, over the long run, a clean, well-ordered database makes life so much easier for everyone that uses it.

The work that gets the glory and the praise is building new things - a brand new shiny data warehouse, for instance - even though it may just be adding to the general confusion in the database, especially (as happens so often) if it ends up not being used.

Wednesday, June 8, 2016

For readable code, avoid code you need to translate in your head

I'm a big advocate of readable code. These include things like good aliasing for tables when necessary, separating code out into logical chunks, and CTE (common table expressions) instead of derived tables.

One thing I've always tried to do is avoiding code that requires translation. What do I mean by that? I try to avoid code such as this:

-- Call Answer Time After Hours
when IsBusinessHours = 'False' and SLARuleID = 2  
    then Sum([AnswrIn30Seconds]+[AnswrIn45Seconds])

It's okay when there's just one chunk of code like this, but if there's a whole set of code like this, you're more likely to make mistakes, because you need to internally translate, "So, if it's after hours, than the IsBusinessHours must be False." This caused me a hard-to-troubleshoot bug recently.

What I'd rather read is something like this:

-- Call Answer Time After Hours
when IsAfterHours = 'True' and SLARuleID = 2  
    then Sum([AnswrIn30Seconds]+[AnswrIn45Seconds])
It's much easier to understand. So what I do now in these situations is to set up a field that's the opposite of the original, when it would help readability. I use something like this

        ,IsAfterHours   = 
                    When IsBusinessHours = 1 Then 0
                    When IsBusinessHours = 0 Then 1
                    Else Null 

Monday, February 8, 2016

Flexible update trigger - take the column value when provided, otherwise have the trigger provide a value

We have a small, departmental database with some tables that will sometimes be updated via a batch process, and sometimes via a people inputting the data directly in a datasheet. Here's a neat trick to allow us to have auditing fields (UpdateDate , LastUpdatedBy) that will either reflect the value provided by the update statement, OR provide a default value, if no value is provided in the update statement.  The trigger does this using the Update() function, which has apparently been around for a while, but I'd never used it before.

Here's the trigger:

 IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID('dbo.trg_Update_SLAMetric'))  
 DROP TRIGGER dbo.trg_Update_SLAMetric  
 CREATE TRIGGER trg_Update_SLAMetric  
 ON dbo.SLAMetric  
 -- Update UpdateDate , LastUpdatedBy  
   UpdateDate = GETDATE()  
   ,LastUpdatedBy =   
       When UPDATE(LastUpdatedBy) then Inserted.LastUpdatedBy  
       else SUSER_NAME()  
 from dbo.SLAMetric SLAMetric  
   join Inserted  
     on Inserted.SLAMetricID = SLAMetric.SLAMetricID  

It correctly updates the LastUpdatedBy field in both situations - updating the table and including the LastUpdatedBy field, and also just updating other fields (in which case it's just set to SUSER_NAME().

 update slametric set MetricNumerator = 0  
 select * from slametric  
 update slametric set LastUpdatedBy = 'test'  
 select * from slametric  

Tuesday, March 10, 2015

Microsoft Azure error - "Cannot open database "master" requested by the login"

I've been busy setting up a database for a TSQL programming class that I'll be teaching in a few weeks. I've run into a few things that made me scratch my head. Here's one of them.

I created a sample database, a new login, and a user to go with the login. Then I gave the user read only access to the database, via sp_addrolemember.

When I used that login to connect via SSMS (File, Connect Object Explorer, enter the new login, also click on the Options button to specify the correct database), it appears to connect fine, and I see the connection.

However, when I expand the databases tab, and try to right-click the sample database to get the New Query option, I get the message "Cannot open database "master" requested by the login. The login failed.". So even though I was trying to open a query in the sample database, it tried accessing the master database.

This is a strange one, but the workaround is to right click on the connection object, and not the database. This will allow you to open up a new query, and will automatically put you in the database you specified in the connection options.

Doing this involves a bit of rethinking, because most developers who have worked extensively with SSMS are accustomed to right clicking on a database and selecting New Query to open a connection. But this won't work with Azure, unless you're also a user in the Master database.

Hopefully this write-up will save a few people some time when they go searching for details on this issue. Feel free to comment with more information.

Monday, March 9, 2015

Error "The database entered is not valid" when trying to set a default database, connecting to SQL Server Azure

Are you getting the error "The database entered is not valid" when trying to set a default database in the ODBC Data Source Administrator, DSN configuration? I did too, and it took me a while to figure out the root cause.

It turns out I was using the SQL Server driver (version 6.01.7601.17514) , when I should have been using the SQL Server Native Client 11.0 driver (2011.110.3000.00)

There are apparently a few other causes of this error as well, but switching to the more recent driver took care of the issue for me.

Monday, March 2, 2015

Review - Microstrategy Analytics Express

I'm doing a review of Microstrategy Analytics Express, and thought it might be interesting to jot down a few things I run into as I'm working my way through it. I'm connecting with a SQL Server Azure 11 database, onto which I've loaded the AdventureWorks 2012 sample database.

A few notes, in no particular order:

- I'm getting the below error a lot when I try to view the available tables:
TypeError - Cannot read property 'n' of undefined
I've narrowed it down, and it appears to happen whenever the table I'm trying to view has a column with a data type of HierarchyID, or any user-defined data type. Which, unfortunately, is about 90 percent of the tables in the AdventureWorks sample database. This is quite problematic. So far I haven't found a workaround.

- In the manual, there's a distinction made between the "Quick, easy-to-use dashboard", and the "Customizable, detailed dashboard". These phrases are repeated over and over again. For the sake of clarity, they really should have figured out a name for these 2 types of dashboards - for instance, Lite Dashboard and Superuser Dashboard. It's a little confusing to read these phrases again and again when two types of objects like this would be given different names.

- It looks like for text files, only comma delimited files are supported. That's a little surprising - most products have supported multiple delimiters for decades.

- It's not possible to edit a the underlying source of a dataset in Analytics Express. A dataset is an imported (not linked) set of data. Once you've imported, it you can't edit the underlying SQL in any way. You can, however, do things like put an expression on one of the fields. You also can't name a dataset. It's automatically given a name which is the name of the underlying table or tables with the date/time appended to it.

- When creating a new dataset, the field names are interpreted for you - Microstrategy tries to guess whether it's a metric, or an attribute. Frequently, however, it guesses wrong, and I haven't found a way to correct it. For instance, a column name of BusinessEntityId is determined to be a metric, and there's no way to reclassify it.

- When creating a new dataset, once you've chosen your fields, you'd expect them to actually show up when you click "Continue". But it doesn't. The fields that show up are the fields from the first dataset you've ever created. I was scratching my head over this, wondering what incorrect button I clicked on, or option I picked incorrectly. But I've redone it a few times now, and this is really what happens. If you actually want to see the resultset from the dataset you just created, you have to close down the grid that shows up by default, and open up the one you just created. It's confusing.

I'm looking forward to trying out the Desktop Developer option, which I'm assuming doesn't have some of the limitations of the online Analytics Express.