Use PowerShell to automate your Azure DB creation

Why create your databases manually if you don’t have to?  If there is one thing that I hate to do it is extra work when it’s not needed.  With the magic of PowerShell and Azure PowerShell modules you can easily automate the creation of your Azure Database.  You can download the scripts I put together from here and below is a little walk through on the scripts.

There are three files contained in the zip folder that will be needed.

  • AzureConfig.xml (Contains the username and encrypted password to login to Azure)
  • PasswordEncrypt.ps1 (utility to set the encrypted password for the AzureConfig.xml)
  • CreateAzureDBv12.ps1 (Script to create your Azure Database)

So why did I create my automation this way?  Well like I said before I hate doing extra work and that includes having to manually login, but I am also security conscious so I wanted to create a way to have a configuration file that would have my username and password and also store that password as an encrypted value.

To use this all you have to do is enter your username into the username tag in the AzureConfig.xml and then launch the PasswordEncrypt.ps1 and enter the password to encrypt when prompted and it will update the AzureConfig.xml file.  There is a default key being used in the PasswordEncrypt.ps1 that you can feel free to change but if you do you must change it in the PasswordEncrypt.ps1 on line 2 and CreateAzureDBv12.ps1 on line 36.

PasswordEncrypt.ps1image

CreateAzureDBv12.ps1image

And yes as I am writing this I realized I should of put the key into the config file as well but I will save that for the next update.

Now you are ready to use the CreateAzureDBv12 script which has the following parameters.

  • SQLDatabaseName (The name for your database)
  • Location (The Azure location you wish to create your database in)
  • SQLServerName (The name of your SQL Server.  This can be an existing or new server.)
  • StartIPAddress (the start IP Address to create the Azure Firewall rule for your server to allow connections)
  • EndIPAddress (The End IP Address to create the Azure Firewall rule for your server to allow connections)

There is also a default rule that is created to allow Azure IP addresses.  You can comment this out if you don’t want to create this rule.  Myself I do this because almost all the databases I create are being used by an Azure Web Application or Power BI.

Hope you enjoy these and love to hear feedback.

SQL Developer Edition is now free!!!

Announced on 3/31/2016 SQL Server Developer Edition is now free and available as a download to all who have signed up for Visual Studio Dev Essentials.  Even if you just call yourself a database guy/gal I highly recommend signing up here.  In the past the only way to get Developer Edition was to have a paid MSDN subscription and the licensing headache in a large development environment was a nightmare.   I’ve personally taken over 20 hours of Microsoft licensing training and I still couldn’t fully explain it to you.

This announcement significantly clears up the licensing model for developers,  remember you can’t use Developer edition for production environments or production data, but for all your development and test environments just sign up for the Visual Studio Dev Essentials and now you’re covered.  Now licensing isn’t the only advantage here because Developer Edition has all the features of Enterprise Edition, so if there are features that you want to explore that aren’t currently in use you now have access to them.   For myself I know I have been in the situation many times where someone was asking, “Why should we upgrade to Enterprise”, now you can build out a POC using those features in a dev environment and pitch the advantages to management.

With all these tools now being free and available it should also expand SQL Community, create more SQL professionals in the long run and lead to more qualified candidates for open positions.  This is something that all existing SQL professionals should be championing and sharing however you can.

SQL Saturday NYC Update #4

SQL Saturday NYC is going full steam ahead and we have finalized our 2 Precons for Friday May 29th.  Before I announce what they are I wanted to first say thank you to everyone who submitted it certainly made making a choice very difficult.

 

Precon #1:  Bradley Ball will be doing SQL Server Performance Tuning check out the full event description here, and hurry we only have 40 available spots.

 

Precon #2: Allan Hirt will be doing The Good, Bad and Ugly of AlwaysON Availability Group you can check out the full description here.  We only have 30 seats for this class and have a great early bird offer of $200 off the regular price at $299 that ends May 1st so don’t delay.

 

On another note we have gained a few new sponsors with New Horizons and Profisee so check out there sites and see what they have to offer.

Table Variables, CTE and Temp Tables. Which one should I use?

There are several really good blogs that already exists that explain the differences between Table Variable, Common Table Expressions (CTE) and Temp Tables and here are a few.

However once you know the differences how do you decide on which ones to use?  Now I know there is the always dreaded it depends which is an answer that I do truly hate so instead I decided to put together some best practices to serve as a guideline on when to use what.  

CTE

In general what I like to say is only use a CTE if you need recursion, and yes I do realize that there is almost no difference between a CTE and a derived table.  However I say only use if you need recursion because there are some cases when joining a CTE multiple times can create a really nasty execution plan. 

Table Variable

Use a Table Variable in the following scenarios:

  1. Small tables
  2. Data is only inserted once
  3. Data has no need to be updated
  4. Known number of rows that doesn’t change

One of the reasons for the above recommendations is that a Table Variable is assumed to have a low number of rows usually 1 for generation of execution plans which can cause a cardinality issue.  If you read Ben Snaidero’s article above you will also see the performance hits for updates to temporary tables.

Temporary Table

Use a Table Variable in the following scenarios:

  1. Large tables
  2. Need for additional DDL, such as an index
  3. Need to access data in child stored procedures
  4. Need statistics
  5. Data needs to be inserted multiple times or updated
  6. Unknown Data size

Temporary tables as you can see have a much broader use and are more of the catch all for everything that doesn’t fall into the CTE or Temp Table bucket.  One thing to remember is that if you have a data set that needs to be generated that needs to be reused through a process of multiple stored procedures is that a Temp Table can be used in child stored procedures in the execution tree.  This is a great way to generate temporary data only once and access it in multiple stored procedures. 

Conclusion

Remember that these are just best practices or in other words a starting point and one that can be generally applied regardless of the skill set of the SQL developer.   As with anything there is always times where the data science will call for an exception to the rule and when it does that is when you go outside the best practices. 

How Functions function

If you are going to use functions first you should understand how functions work as they are often over used in database applications.  Personally I will start out by saying that I only use functions when I absolutely have no other choice.  This is something that I have seen many different opinions on so let just start out by taking a look at the different types of functions and how they work.

In SQL Server there are several different types of functions so lets take a look at what they are:

  • Table Value Functions (These are functions that return a table or dataset)
    • Inline (Contains a single T-SQL Statement)
    • Multi-Statement (Contains multiple T-SQL Statements and other functions such as Cursors and While loops)
  • Scalar-Valued Functions (Returns a single value)

 

There are many different reasons that developers use functions such as repeatable code, generic datasets, common logic etc…  These are all good reasons to use functions when used in the properly.  To understand properly lets start by taking a look at how functions work.

Table Valued Functions

There is a significant difference between the Inline vs. Multi-Statement functions when it comes to performance.  To help show some of these differences I created a few functions as well as a view and stored procedure to help show some differences that you can download here and goes against the Adventure Works database.  Lets first just take a look at what happens when we call the two functions of SALES.GetSalesOrderDetail() and SALES.GetSalesOrderDetail_Multi().   If you SET STATISTICS IO ON and SET STATISTICS TIME ON you will notice that the multi statement takes almost double the CPU Time (hint also run DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE to insure data isn’t cached between tests)  Most of this overhead is contained in the insert to the temporary table which is a needed overhead of multi-statement functions. 

Now before I show comparisons to views and stored procedures lets take a look at one other significant difference between these two functions and that is when we use them in a join statement.  For this test I am going to execute the following two commands

SELECT sod.*
FROM SALES.GetSalesOrderDetail() sod
join sales.SalesOrderHeader soh
on sod.SalesOrderID = soh.SalesOrderID

SELECT sod.*
FROM SALES.GetSalesOrderDetail_Multi() sod
join sales.SalesOrderHeader soh
on sod.SalesOrderID = soh.SalesOrderID

If you take a look at the execution plans you will notice a significant difference with the inline functions as the SQL can be interpreted and joined using a Merge Join operator and also notice the estimated row count is fairly accurate.  However with the multi-statement function you will see that the SQL can not be interpreted and the function is applied via a nested loop and sequence operator as well as the row count is not accurate and set to 100.  If you also take a look at the CPU time on the multi statement used with a join you will see about 3x the cost.  

Now just for a comparison run the similar query this time using the view.

SELECT sod.*
FROM SALES.SalesOrderDetail_View sod
join sales.SalesOrderHeader soh
on sod.SalesOrderID = soh.SalesOrderID

You will notice the same execution plan as the inline functions but on average you should see about 1/2 the cost of the CPU with the view compared to the function. 

Scalar-Valued Functions

Scalar Valued Functions operate very similar to the multi-step table valued functions in that they require significant CPU cycles and typically the migration path from Scalar Functions is to use inline T-SQL.  There are multiple ways to do this but lets take a look at the following example, which you can download the script to create the function here.

This is a simple function that is just concatenating some fields to format an address.  If we call the function vs do the same query inline you will see an almost 10x cost in CPU for the function. See the examples below.

SELECT
    Person.FormatAddress(AddressLine1,AddressLine2,City,StateProvinceID,PostalCode) AS FormatAddress
FROM
    Person.Address;

GO;

SELECT
    COALESCE(AddressLine1,”) + ‘ ‘
    + COALESCE(AddressLine2,”) + ‘ ‘
    + COALESCE(City,”) + ‘ , ‘
    + COALESCE(st.StateProvinceCode,”) + ‘ ‘
    + COALESCE(PostalCode,”)
FROM
    Person.Address    Addr
JOIN
    Person.StateProvince    St
ON
    Addr.StateProvinceID    =    st.StateProvinceID;

 

Wrap-up

So what does all this mean?  In general if you want to ensure that your database isn’t using unnecessary CPU cycles avoid using functions.  In almost all performance tuning engagements that I have worked when there is a CPU utilization issue we were able to find offending Functions as the cause for this.  Functions are for lack of better words CPU hogs and the more operations that are within the function the worse it gets.  So if you already have function try and migrate them to inline T-SQL. In the case of Table Value functions look to see if you can accomplish the same thing via a stored procedure or if you have to use a Function at least make sure that it is an inline Table Value Function.

Business Intelligence and Financial Services

A frequent question for enterprise executives is: why do I need BI?  Occasionally, I have to put elaborate presentations together showing what BI is and how it fits each company. However, when it comes to financial services, and more specifically the banking industry, there are only two lines that are really needed:

  1. Regulatory Reporting    
  2. Risk Management    

Anyone familiar with either of these and the concepts of BI might just stop reading right now.  However, since that would make for a short blog, let me continue to explain why I think it’s so simple.

Let’s start with the basics of BI which, to me, are Reporting (Operational and Ad-hoc), Analytics, Dashboards, Discovery, and then Learn and Repeat.  Each is a separate step and should be treated as an iterative approach.

Business Intelligence and Financial Service Illustration

Regulatory reporting requirements from the FDIC, NCUA, SEC, SOX, HIPPA, etc. all require various types of reporting. Specifically with the FDIC, NCUA and SEC, there are monthly, quarterly and yearly reporting requirements ranging in all areas from Deposits, Securities, Safe Deposit, CD’s, Secured Loans, Unsecured loans, etc.  All these reports could be automated and easily generated through the Operational Reporting piece of BI, not to mention all the other reporting a financial organization needs to be able to handle. Ad-Hoc reporting is great for spontaneous or last-minute reports. Maybe someone asks about the status of delinquent loans 20 minutes before a meeting. Maybe you’re working on 13-month rolling audits, and the auditors ask a question and they need answered by end of business. You can easily use Report Builder or Power View in SQL 2012 in order to produce what they need.

Risk Management encompasses Analytics, Dashboards, and Discovery. It’s important to learn from that discovery, and monitor what you’ve learned. The best way to do this is to go back to the basics and leverage operational reporting.

Let’s take a look at this scenario:  You have a problem where you noticed delinquencies on your auto loans have increased by 50% over the past six months.  First, you open Power View in SQL 2012 that is attached to a Loan Performance Analysis Services Cube.  You start to analyze your loans and you find that 90% of the increase in delinquencies is caused by borrowers that had FICO scores of 650 and below, had the loan for one year and a payment to income (PTI) of greater than 20%.  Now, you have analyzed and discovered the issue.   You decide to make a change in your lending practices to charge higher interest rates for borrowers in this category, which in turn reduces your risk of loss on the loan.

So are we done now?  Of course not.

We need to see if this change in lending practices actually made a difference.  There are a couple of ways we can do this.  First, we could create a report that starts monitoring loans generated after the decision date. Since we learned the borrowers only started becoming delinquent after a year, it should take 12 months to see the results.  Next, we could create a dashboard that monitors both loans before and after the decision and put that dashboard into the CFO and head of loan’s daily view.   We could even create a KPI for goal of delinquency and monitor against that. All of these could easily be created within SharePoint 2010 using Reporting Services and/or PerformancePoint. 

One last quick note: the FDIC requirement for stress testing could also be easily created using an Analysis Services cube and enabling write back for what if scenarios.

Rather than ask how business intelligence can help your organization, take some time to think about the increased power you could have in backing your decisions with the help of BI. The more insight into each decision, the better your company will be positioned for the future. Business intelligence and financial services go hand-in-hand.

PASS Summit 2012

 

So another PASS summit has come and gone. I had a great time and not only was it technically informative I also got to meet some great people. My company had a great presence as well with 7 attendees this year and a speaker for two sessions, so a congratulations goes out to Steve Hughes, www.dataonwheels.com , for his efforts. For those of you who could not attend I challenge you to do all you can to attend next year in Charlotte, NC and I am going to try and point out some of the highlights of this year’s conference.

 

Wednesday morning started off with the first Keynote speech by Ted Kummert where several important announcements were made.

  1. Project “Hekaton” was announced, more to follow below about this. Oracle has already made some responses Oracle Response
  2. SQL 2012 SP1 was launched
  3. PASS Business Analytics conference in Chicago April 10 – 13 in Chicago. http://www.passbaconference.com/
  4. Columnstore Indexes to be updatable and clustered
  5. Next version of SQL PDW will be available in 1st quarter of 2013
  6. PolyBase, a new tool for allowing traditional T-SQL statements to be executed against Hadoop, will be released by June 2013
  7. Powerview is native to Excel 2013 and includes the ability to execute DAX queries against traditional MOLAP cubes.

 

While all these updates are all individually important the most important ones to me are project “Hekaton” and Powerview native to Excel 2013. Hekaton is the next major enhancement to in memory technology on the SQL stack and bringing OLTP into the in memory space. During the demo of Hekaton several different examples where shown. The first was showing the performance increase by just putting the table into memory and a performance increase of about 13x was achieved. They followed that up by putting both the table and the stored procedure in memory and a performance increase of around 60x was achieved. Now while this was a demo and real world scenarios are always more of a true test it was still rather impressive to see this advancement and what it shows for the future of SQL Server and competing with other database platforms.

Not as for Powerview not being native to Excel 2013. This is one that really changes how business can bring analytical reporting to their end users. The requirement for SharePoint in order to use Powerview is no longer there, and in my opinion was a big road block to the adaption of Powerview. However, with it now being native to Excel you can have a robust reporting engine from within a tool that the users are already familiar to using and combine that with the support for traditional Multidimensional cubes and you now have a true reporting tool that I think will be adopted by business very quickly.