Month: April 2019

Azure Metrics Reporting

A few days ago, I did a post on DTUs of an Azure SQL Database.  In the post, I mentioned a report that you can use to see how many DTUs your subscription has used.  This is an important number to know and measure over time so you don’t get slapped with an unexpectedly high bill or have performance issues because you have exceeded your DTU limit.

Azure has quiet a bit of reporting that can me used to get the current state of your Azure subscription. Although many of the metrics are targeted more for subscription rather than database performance counters, there are few DB related such as Deadlocks. Below is a small list of the items that can be viewed, of course there are more than included in the list:

      • Azure SQL Database
        • Data space allocated
        • Data space used
        • Deadlocks
        • In-Memory OTLP usage
        • DTU used
          • What is a DTU?  Stands for Database Transaction Unit. Here is a nice article by Andy Mallon the does a nice job explaining what it is. Click Here
      • Storage Accounts
        • Used Capacity
        • Egress
        • Ingress
        • Availability

How do I Get to the reporting tool?

Getting to the reporting tool is just a matter completing a few clicks. Of course with most products there are several ways to get to a specific place.

On the left side of the Azure Portal, click “All Resources”.  You could also click on Resource Groups if you like.

Just a little side note on how to add a resource. As expected there are many resources in the Azure world.  Here are just a few: Azure SQL Database, Azure VM, Storage accounts, resource groups, Cosmos DB as well as many other items.  Not all resources are from Microsoft.  There are a number of resources that are made available in Azure.  After you click the “Add” button, you will be taken to a page that looks something similar to what is below.

Above is a screenshot of some of the available resources.  The list on the left is just the list of resource categories.  On the right are some of the resources in the chosen category.  You can either pick a category or click the “See All” link towards the top.

Once you click the “All Resources” link you will be taken to something that looks like this.

The above image lists a number of important items.  Obviously the resource name is included and the Resource Group that resource is part of.  You can also see the resource type and the Location.  The location is really important, especially when it comes to cost.  Since this blog post is about Metrics reporting, it is important to note that not all the resource types have this type of reporting.  Above you will see that I have three resources, a storage account, a SQL Sever and an Azure SQL database.  Of these three, the SQL Server is the only one that doesn’t have this metrics reporting functionality.

Once you click the resource you would like to get the reports for, you will be taken to a page similar to this.

By chosing a resource on the left, the panel on the right will update with the information for that resource.  In the above situation, I chose the storage account.  Notice two green boxes, both of these will take you to a reporting tool. You want to click the one on the top. The bottom one, Metrics(Classic) is going to be retired on June 30, 2019.  This is according to Microsoft.

Creating the Chart

Now you simple click on the Metrics link and you will be taken to a page that looks similar to what is below.

Now confirm that you are on the proper resource by viewing the resource identified in the box highlighted in the green box.  If you are not on the proper resource, simply click the resource that is there and you will have the option to change to the proper resource.

Staying in the same box as the resource, you will see a few other items.

Metric Namespace:  This is the group of metrics that you are looking for.  The items in the drop down box changes depending on the type of resource selected. Since I am working with a storage account, you see the account option.  If you don’t see this for some reason, click the “Add Metric” link that can be found just under the Chart Tile.

Metric: This is the counter that we are looking to review.

Aggregation:  This is what it says it is.  There are usually three options, Avg, Min and Max.  However, some only have Avg as the only choice.

Once you have chosen all the desired options, you will see this.

As you can see, this chart contains more than one metric.  This is easy to add, just click the “Add Metric” link in the upper left hand corner. When you do, you will be given the same options as before.  The additional metrics do not have to be from the same resource, as you can see.  The metric on the left is from the storage account, while the one on the right is from the Azure SQL Database.

Although this example is a line chart, you can change the chart type easily.  Just click the arrow to the right of where it says “Line chart” in the upper right corner.Just like in Excel, there are a number of options for chart types, just not as many options.  Only 5 options here.  Just like in any other reporting tool, not all charts work well with all data sets.

Also in the upper right corner you can change to time range of the chart.

If you click the time, the options window for the chart will open up. As you can see there are a number of options, including the time range, the Time granularity(time interval) and the time(GMT or local time).

The options for Time granularity are in intervals that would be expected.  The options are listed below.

      • 1 minute
      • 5 minute
      • 15 minutes
      • 30 minutes
      • 1 hour
      • 6 hours
      • 12 hours
      • 1 day
      • 1 week
      • 1 month

You also have another option, Automatic.  The interval for automatic is dependent on the Time Range chosen. Below are the time ranges with the automatic interval.

      • Last 30 min – 1 minute
      • Last hour – 1 minute
      • Last 4 hours – 1 minute
      • Last 12 hours – 5 minutes
      • Last 24 hours – 5 minutes
      • Last 48 hours – 15 minutes
      • Last 3 days – 15 minutes
      • Last 7 days – 15 minutes
      • Last 30 days – 30 minutes

Another area that could be very helpful is in the lower left corner.  There will be one number for each metric that has been added to the chart.

In the green box above there are a number of data points.  It contains the resource name of the color of the line in the chart.  It also contains the name of the metric, aggregation used as well at the value.  In this case the number represents the aggregation listed for the data range of the chart.  If you float your cursor over the chart and vertical line like below will appear.  When you do this, you can move it to any time on the chart and the numbers in the lower left corner will no longer represent an aggregate, but the numbers of the point in time on the chart.

Now that I have created my chart, but the numbers on the Y axis don’t meet my needs.  This can easily be changed.  In the upper right hand corner you will see three dots.  If you click it a menu opens up and you can the click “Chart settings”.

Once you click the menu item, the settings window opens.  Here you can change only Y value.  Remember the X values are determined by the date range mentioned earlier.

In addition to the Y value, you can also change the Chart Title and chart type.  Notice that the unit of measure is bytes, but many of the metrics are in MB.  If you change to a time granularity to something other than automatic, the warning goes away.

Notice the warning:

“Setting a min or max value is not recommended when the time granularity is set to Automatic”

Pin to Dashboard

Once you are in the Azure portal, you can have one or more dashboards.  Once  you have your chart looking they way you want it to look, you can then pin it to your dashboard.  Once you do that it, will now appear as below.  In this case I have two charts pinned.

 

To pin your chart to the current dashboard, click the “Pin to dashboard” button in the upper right corner.

When you do, you will have an option to pin to current dashboard.  However, when it is pinned, your dashboard may not look as you like it.  If this is the case, you can click the “Edit” option on the dashboard.

Once in edit mode, you can move the charts as you see fit.

One nice feature of pinned charts is that the vertical line that appears when you float your cursor over the chart still works.  There is one additional feature that is very nice if you have more than one chart.

 

When you have more than one chart, the line appears on all charts that are pinned to your dashboard.

Hopefully this will help you with creating charts in the Azure portal.  Thanks for stopping by my blog and I hope you learned something.

 

 

 

 

 

Azure SQL Database and DTU

As you know, when you are using an Azure SQL Database you can use SQL Server Management Studio on your computer to execute queries.  You also have an option to use the Azure Query Editor. Here is my post on the query editor.

As I was writing the post I wondered more and more about whether or not there might be a difference in how data usage is billed.  My thought was that since I am going from an Azure SQL database source to another Azure tool, Query Editor, could this potentially reduce my costs.

There are a number of Azure resources that have Egress as a metric that can be used to measure the amount of data that is being pulled from the Azure data center.  These resources include the Azure Storage Account. However, an Azure SQL Database does not need to use a storage account.

Before we move on, let’s define what egress is.

Here is a definition of Egress from Microsoft:

“….data going out of Azure data centers” – Microsoft

I think the most important point the two definitions are trying to make is that egress is data going out of a data center, including if it is going from a data center in one region to a data center in another region.  According to Microsoft, if there is a data pull and the database and the querying tool are both in the same region, you will not be billed.  However, you will need to confirm this.

I am not planning on getting into the pricing models for Azure in this post, however you can get that from Microsoft.  https://azure.microsoft.com

Although some resources have Egress to measure data, an Azure SQL Database does not.  It does however have another option, DTU.  DTU stands for Database Transaction Unit and it is a unit of measure that is used, in part, to calculate your bill and to help you chose the proper pricing tier for your database.

A DTU is a “blended measure of CPU, memory, and data I/O and transaction log I/O”, per Microsoft.  The DTU is a measurement for the Azure SQL database only, not for your entire Azure environment or any other database.  An important thing to keep in mind is that when your workload exceeds the DTU maximum for your pricing tier, Azure will then throttle your workload.  My subscription currently has a max DTU of 10. As long as my workload stays below that I should be fine.  Because of the potential of being throttled, it is important to monitor your work load.   This is a nice article on the DTU by Andy Mallon.

If you are not sure what your workload might be in Azure, there are a number of DTU calculators available that can help.  Here is a link to one, DTU Calculator.

As for the how many DTUs you can have, that will depend on the tier that you have chosen.  Below is a sample of a chart from Microsoft that outline the limits of two of the tiers.  Notice that there are a number of options for Max DTUs. Source of the chart is here. As you can see, the Max DTUs goes from 5 to 100.  It even goes higher if you have are using the Premium tier.

Per Microsoft, you have up to the Max DTU for your tier before you will be charged.

The DTU price for a single database includes a certain amount of storage at no additional cost. —Microsoft

Of course this might change at any moment, so I have to make a disclaimer.  Always consult your MS rep or Licensing expert to get the exact pricing and rules.

As I stated earlier, it is important to monitor your work load to see if you are at a position in which you might be throttled.  Azure is a pretty good set of monitoring reports that you can create.  You can use the metrics reports to accomplish this.  Here is a link to my post on how to create a metrics report.

So what should you monitor?  Well since we are talking about an Azure SQL database, you have three options for measure DTUs.


DTU Limit – this is what you think it might be, the limit for your tier.

DTU Percentage – this is just that, the percentage of the DTU max that is being used.

DTU Used – this is the current DTUs that are being your by your workload for the database.

This post is less about DTU calculation and pricing tiers and more about does it make a difference if I use SSMS or the Azure Query Editor on the use of DTUs.

From the tests that I have completed that answer is no.  Using Azure Query Editor showed little or no improvement in the use to DTUs.

Before we get into the results of the test, let’s cover a few basics of what I did.  I used the AdventureworksLT sample database that you can get from the Azure portal when creating a new sample database.

This is the query I ran this query using both tools.

SELECT TOP 100000 *
FROM saleslt.salesorderdetail a
CROSS JOIN saleslt.salesorderdetail b

In the chart below the green boxes represent the test using SQL Server Management Studio, while the blue boxes are the executions in the Azure Query Editor.  Notice that there is very little difference in the DTU usage.

Although there really isn’t much difference in DTU consumption, I did notice something I didn’t really didn’t expect.

When I ran the query in SSMS is usually took less the 3 seconds to run.  However, when I ran the same query using the Query Editor, it usually took 45 to 60 seconds.  That is a huge difference.  Also note that in the above chart, the Max DTU never reached 10, which is my limit.  Because if this, I know that I was not throttled by Azure.

You might be wondering with the red box is.  This is the test when I ran this query.

SELECT *
FROM saleslt.salesorderdetail a
CROSS JOIN saleslt.salesorderdetail b

The only difference it that it doesn’t have the TOP keyword.  What is interesting about this is that when I ran the query in SSMS, it took about 45 seconds.  However, when I ran in the Query Editor, it was pushing 5 minutes before I cancelled it.

How can I get information about the MAX DTU for the Azure SQL Database?  There are a few DMVs that you can use.  The first on is the dm_db_resource_stats.  This can be used to the Max DTU, as well a number of other bits of useful information.

If you run this query in your Azure SQL Database you can get the information.  The column you want to look for to get the Max DTU limit is DTU_limit.  Here is a link to get more information on this DMV.

SELECT * FROM sys.dm_db_resource_stats;

However, if you try to run it in the Master database, you will get this error:

Msg 262, Level 14, State 1, Line 1
VIEW DATABASE STATE permission denied in database ‘master’.
Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.

You can also use he sys.resource_stats dmv to get the DTU limit.  This DMV has the same column name as above, dtu_limit.  This DMV must run in the Master database.  Here is a link to get more information on this DMV.

SELECT * FROM sys.resource_stats

If you try to run it in another database, you will get this error.

Msg 208, Level 16, State 1, Line 3
Invalid object name ‘sys.resource_stats’

How do I get to the metrics report. This is pretty straight forward.  Navigate to your Azure SQL Database in the portal.

 

As you can see, in the menu on the side you will see the Metrics items.  If you click it, You will then be taken to an Azure Reporting tool.

You will then create your report.  This my post on the Metrics Report.

Thank you for stopping by and I hope you learned something.

Disabled Database User?

In my many years of working as a DBA, I have encountered many disabled logins.  However, I have never really encountered what looks to be a disabled database user account.  I didn’t even think it was possible to disable a user account in a SQL Server database.  I checked the user account properties just to makes sure I was correct.  Sure enough, no option to disable a user account. This finally turned out to be a simple case of looks can be deceiving.

When we first received the call that a user was having an issue, we asked what was the error.  This is what we were sent:

For the purpose of this blog, we will be using a SQL login named, testuser.

My first thought was maybe a permissions issue.  I very quickly realized that it was something else.

The first thing I did was look at the properties of the login.  This clearly showed that the login had access to a few databases, except the one related to the reported error, 1GBDB.  As you can see below, it looks like the login doesn’t have access into the database.

 

However, if you look at the users in the database, you can see that the account does indeed exist in the database.  As you can see below.

Of course the red “x” on the user account was a bit suspicious, we will get into that in a bit.  One of the items I checked was if we have an orphaned account.  So I check the SIDs for the login and the user account using these queries.

SELECT sid, name
FROM sys.syslogins
WHERE name = ‘testuser’

SELECT sid, name
FROM sysusers
WHERE name = ‘testuser’

Here are the results.

As you can see, the SIDs for both the user account and the login are the same. So that rules out an orphaned account.

I still couldn’t figure out why they user couldn’t get into the database.  That red ‘x” was still bothering me.  After some checking, I discovered there was something different about the user account in this database, 1GBDB, when compared to the same user accounts in different databases.

Database Permission properties for the 1GBDB, which the user cannot connect to.

This the a screenshot of the database permissions for the AdventureWorks2014 database, which the user can get to.

There is one important difference, the Connect permissions.  If you remove the “Connect” permissions from a user account in a database that has a corresponding login, you will see this.

Once you grant the Connect permissions for the user account, the red “x” goes away and all should be good.  Unless of course there are other issues.

Thanks for stopping by and I hope you learned something!

Azure SQL DB Query Editor

We have all been using SQL Server Management Studio to query and manipulate data, even for an Azure SQL database.  There is also an option to do this same thing built into the SQL Azure database interface in the Azure portal.  Although there have been a number of posts related to this topic dating back a few years, this feature is still marked as “preview” in the Azure portal.

How to get to the Query Editor

Once you are in the SQL Database list in the portal, find the database you would like to work with and click it.

When you do, you will be taken to the administration page for the database.  On the left side you will see the “Query editor” link.  You will also notice that this is a feature that is still considered to be in preview.

When I did a Google search on the Query editor in the Azure portal, I found posts from as far back as 2017.  Despite that, I still wanted to complete a blog post on it.  Once you click it, you will be taken to a login page similar to below.

Notice on the right side there is the ability to utilize the Active Directory single sign on functionality, which I do not have.  On the left you will enter the appropriate credentials to login to the database.

Once at the Query Editor

Once you are in the Query editor, you will notice many of the same features as SSMS.

These features include the following

      • Tabs for new queries
      • Open query
      • Results pane
      • Messages pain
      • A condensed object explorer
      • as well as many other features of SSMS

Below you will find a screenshot of the Query editor interface.

Menu Bar

Starting with the menu bar across the top, you will find the expected buttons.  In addition to the Open query, new query and save query buttons, there is also an Edit Data(Preview) button.  This does as you expect it might, allows you to edit the data.  Just like the Query editor, this is also a preview feature. There is also a Login button if you wish to use a different user.

Object Explorer

 

The Object explorer is very similar as that in SSMS, however there are some very glaring differences, mostly that there are fewer object types displays in Query editor.  As you can see you can browse tables, views and stored procedures.  As for the tables, you can see the column names and data types as well. For stored procedures, you are able to see the parameters just as the image below indicates.

In SSMS a user can right click on a stored procedure and execute it.  In the Azure query editor, when you right click on an object you get nothing.  The context menu does not appear, therefore not allowing you to execute the procedure.  Of course you can still use T-SQL to execute a stored procedure.

Query Window

In many aspects the query window is very similar to that of SSMS.  There are tabs across the top, the T-SQL pane, a results pane and a messages pane.

Starting with the T-SQL pane, there is color coding of the SQL, however it is slightly different than that of SSMS.  First big difference is that there is not an option to change the size or color of the font.

If SSMS if you execute two SELECT statements, like the ones below, the results are much different in Azure Query editor when compared to the same two queries in SSMS.

SELECT TOP 10 p.name
, p.productnumber
FROM saleslt.product p

SELECT TOP 10 *
FROM saleslt.address

Here are the results when you run the code in SSMS.  Notice that there are two data sets

Now look what happens when I run the same queries as the Query editor, I only get one data set vs the two when executed in SSMS.

In SSMS, if you right click on a table you will see an option to Edit X number of rows.  That options is very similar in Azure Query Editor. The way this is done in the portal is to click on a table and then click the “Edit Data (Preview)” button just to the right of the Login button.

This will in turn, return the data in a grid that allows for editing data. You can just double click in a cell in the results set.  This will change the cell to an editable version.  The edit menu has just 5 buttons on it.

The are:

        • Create New Row
        • Save
        • Refresh
        • Discard
        • Delete row

When you click the new row button, the new row will appear in a different location that if you attempted to enter a new record use the grid results set in SSMS.  The big difference here is that the new row will appear at the top of the grid.

One option that is important to me is the ability to change the font size.  I have NOT found out how to do this in Azure Query editor.

When you execute a statement and an error is returned.  In SSMS this error is red, but default. In the Query editor it is black.

Once you have your query as you like, all you have to do it click the Run button

Limitations

There are a number of limitations in the Query editor at this time.  Since it is in preview, my hope is the Microsoft will address some of these deficiencies as the Query editor moves closer to having the preview label removed.

      • Does not allow for the viewing of object definitions
      • Limited objects in object explorer
      • Tabs in the query window does not have the SPiD on it
      • If more than one SELECT statement is executed in a batch, you will only see the results of the last one
      • Cannot right click on a stored procedure to execute it
      • No options such as display Actual Execution Plan
      • Cannot drag and drop object names into the query

Despite all of the limitations, I still like it.  Once you get used to the limitations it really isn’t that difficult to use.  Since the name of the tool is Query Editor, it does make sense that it doesn’t have all the same functionality as SQL Server Management Studio. However, it would be nice to have some the items that are currently not included.

Thanks for stopping by and I hope you learned something small.

How to Move an Existing Database to Azure SQL DB

When moving the enterprise to a cloud based solution such as Azure SQL Database, we will need to find a way to migrate existing database to the cloud.  I already completed a post on how to create an Azure SQL database.  This post will focus more on how to migrate an existing on-prem databases to an Azure SQL Database.  There a several options that you can use, use the “Deploy Database to Microsoft Azure Database…” option in SQL Server Management Studio, Data Migration Assistant tool or use the Azure Database Migration Service.  During this post I will cover all three options.

Deploy Database to Microsoft Azure Database…

After opening SSMS, move to the database you wish to move to Azure SQL Database and right click on it.  When you do, you will see this:

After clicking the first next in the wizard,  you will then be asked to provide some detailed information about what you are attempting to migrate to Azure SQL Database.

This information includes the following:

  • Server Connection – this is the Azure location you are attempting to migrate to
  • New Database Name – this will default to the name of the database you right clicked on, but can be changed in Azure SQL Database if you have a need to change it
  • Azure SQL Database Settings – these will not be active until you identify a Server Connection
      • Edition of SQL Server
      • Maximum Database size
      • Service Objective
  • Temporary File Name – This is the name and location of the .bacpac file.

What is a .bacpac file?  Since you may not have worked with this before, I thought I would take a few minutes to go over exactly what a .bacpac file is. It is considered a Data Tier Application that can be used for deploying databases from one location to another.  This file will contain both schema, object definitions and data.  There is also a .dacpac file type.  The only difference between the .bacpac and .dacpac is that the .bacpac is the only file type that also contains the data.

This is what the screen will look like.

When you click “Next”, you will have an opportunity to review all the settings before clicking “Finish”.  When you do, you will see the progress of the migration, step by step.

As you can see above, there are number of steps.  You can also see that there is a step named “Processing Table”.  This step will show each table as it is processed and migrated to Azure SQL Database.  Once the migration is completed, you will now see the database in both locations, the original SQL Server and in Azure SQL Database.

Data Migration Assistant

This is a relatively easy tool to use.  It really has two purposes.  One to assess an on-prem database to identify what challenges you might encounter when migrating at database to Azure and the other to actually complete the migration.

This is a tool the must be downloaded and installed before using.  To download it you can go here.  Download DMA.  Once downloaded and installed you will find it list as “Microsoft Data Migration Assistant” in the Start menu, not under the SQL Server items.

Below you will see the opening screen in the DMA.  As you can see there are two options, completed an assessment or do a migration.  By clicking the “+” on the left side you will have an option to chose what you would like to do.  Notice that there are also options to restart an assessment or open an assessment.  These are exactly what you might think they are.  When you complete an assessment you will be able to save the results or save the configurations of the assessment that you can restart if you like.

When you click the “+” sign you will then see a few options that you can chose from.  Here you will be able to chose to complete an assessment or a migration.


Both of which will require a project name, a source and target server type.

You will have several options for a target as you can see in the image below.  Since this blog post is about the migration of a database, we will focus on that target.

Once you pick you source and target, you will then be able to click the “Create” button.  Once you did, you will see this.  You will see three options, although only two are available at this time.  The two options that are available are Check Database Compatibility and Check Feature Parity.

Once you click “Next” you will then be asked to identify a source.  Since this post is about migrating an on-prem SQL Server database to Azure SQL Database, the source will be a local instance of SQL Server.  You will have to provide an authentication method and then the database(s) you would like to complete the assessment on.  Once you have provided all the information you can click the “Start Assessment” button.

Below is an example of the results of the assessment.  There were a few things that were expected, especially the Agent jobs not being supported in Azure SQL Database.

Fortunately Microsoft has also included links to each of the topics that will allow us to gather more information easily.

Make sure you look at both groups of items, SQL Server feature parity and the Compatibility issues.  In this case not compatibility issues were found.

Now that we have completed an assessment and addressed all the blocking issues, we can now use the same tool to perform the migration.  As expected you will also have to provide a project name, a source and a destination type.  This will also include the connection information for the target.

The migration scope has three options.

      • Schema and data
      • Schema only
      • Data only

You just need to decide which is best for what you attempting to accomplish.  In this case, we will pick Schema and Data.

Once you provide all the information, you will be able to click the “Create” button. As part of the migration, there are six steps.  These are list in the image below.

Selecting a source if the obvious starting point for a migration.  Once you do, you will then have an opportunity to pick which database you would like to migrate.

It this is a new database in Azure SQL Database, make sure to click the “Create a new Azure SQL Database…” link.

When you do, make sure you click the Connect button so the list of databases refreshes.

The next step will allow you to identify which objects you would like to migrate.

In this case there are still a few issues that will need to be address, most due to encryption, so I will not migrate those objects.  The next step is to generate the scripts.  Once the scripts are generated, you can save it or copy it.  Note below you the DMA tool will also identify items with assessment issues.

At this point the database has been created in Azure SQL Database and all the objects have been created.  However, we have not reached the point of migrating the data yet.

In order to migrate that data, all we have to do is click the “Migrate Data” button.

We will now have the option to decide which tables we would like to have the data migrated as well.  As you can see below, we can see the tables listed.  This list include the number of rows and whether or not the table is ready to move or not.

Once you have confirmed the information, click the “Start Data Migration” button.  What I like about this part of the tool is that it tells me the progress of each table and the status of each migration of data.

Now were are done. Not only have the objects been migrated, but the data as well.

Both of these migration options take place on your computer, one with SSMS and the other the downloadable Data Migration Assistance.  In my opinion, I think they are both pretty easy to work with.  The third option is done in the Azure portal.  There is a server called Azure Data Migration Service.  There are a number of required steps to utilize this service.

Azure Data Migration Services

As the name implies, this is a service in the Azure Portal.  When looking at all the services, the Azure Database Migration Services can be found either by doing a search or looking in the Databases group.

Once you click the Azure Database Migration Service, you will see what is below.  However, if you already have one of this service type running, you will not have to go through this step again.

For this method, you will need to provide a service name, a location(region) as well as a Virtual Network.  Yes, you will need to create a virtual network. This will include the creation of a virtual NIC.

If you already have the service created you can go to the All Resources list in the portal and click that service. Once you do, you will see this.

The next step is to click the “New Migration Project” button.  At this point you will pick a data source and a target.

The options for Type of Activity are:

    • Schema Only migration
    • Offline data Migration – this is to be used for source databases that will NOT be changed during the migration.
    • Online data migration – For systems that will be modified during the migration
    • Create project only

If you chose the Online Data Migration type, Azure with provide you with a list of rules that must be followed in order for the migration to be successful.

Also, if you chose Online Data Migration, it will require a higher level of SKU.  You must have Premium, which I currently do not have.  I received this warning what I tried to use Online Data Migration.

Given this I changed the type of activity to Offline Data Migration.  You will have to complete several steps to be successful in the migration. These steps are fairly basic and expected.  Identify a source, a target, map to target database, configure migration options and a summary.

There are a number of network and security steps that must be completed prior to utilizing this service.  This is a good description of what is required, click here.

As expected the source and the target require the usual information. What is a bit different is the third step, mapping target databases.  Below is a screenshot of the mapping step that is on the above Microsoft site. As you can see below, you simple map the source databases that you would like to migrate to a destination database in your Azure SQL database portal.

The settings step is simple telling the service what tables you would like to migrate.  Below is also from Microsoft’s site.

Finally, once you have provided all the required information and you can then begin the migration.  Once you do, Azure will provide progress status that will look like this, also from Microsoft’s site.

It is always good to have options.  In this case we have three different options.  Two you can run from your computer and one that must be done in the Azure portal.  Personally, I like the options of running the migration through an on-prem computer.  They appear to be less involved and I don’t need to involve other teams, such as the network or security team.

When migrating databases to Azure SQL Database, it is important to complete an assessment on the database first using the Data Migration Assistant.  This will allow you to identify what your obstacles might be.  You can then address them before starting the migration.

Thank for stopping by my blog and I hope you learned something.

Creating an Azure SQL Database

As the industry moves more and more activity to the cloud, I thought it would be a good idea to put together a post on how to create an Azure SQL database.  There are a ton of advantages to using an Azure SQL database.  One of the biggest is that I do not have to purchase a SQL Server license for a single database.  This is my first post on any Azure product.  I am hoping to have more in the future.

First of all, lets go over what Azure is.  Azure is a collection of many different cloud based technologies that Microsoft offers.  To see the services that are being offered, once in your Azure portal just click the “All Services” option on the left side.

This will bring up many, if not all the services that you can utilize.  Just remember to be careful to not do more than needed on a production server.  It is very easy to take advantage of many of the great features of Azure. If you don’t monitor the cost, you could potentially have a very large bill at the end of the month.

Once you click on the services option you will see something similar to what you see below.

The red rectangle identifies where the SEARCH options can be found in Azure.  The green box is where the groups of services can be found, while the blue box represents the service.  There are many services to choose from.  You just need to identify what you need to pick the appropriate services.

When searching the services you can enter a value into the Search box and Azure will search as you type. If you select a group on the left and then do a search, it will only search that group. However, when you do searching in a group and the query returns no results and there a items in other categories you will see this message.  This is simply telling you that there is nothing in the current category that matches your search criteria, how ever there are other services that do.

Since this post is about Azure SQL Database, we will focus on that service.  There are several ways to get to the point of creating a database, this is one of them.  The blue box below is the group of services and the green box is the service, in this case, SQL databases.

Microsoft defines “SQL Database” as “Azure SQL Database is a general-purpose relational database-as-a-service (DBaaS) based on the latest stable version of Microsoft SQL Server Database Engine“.  Notice that the definition states that it is based on the latest stable version of SQL Server.  This means no SQL Server upgrades or patches, Microsoft takes care of those.

Once you click on “SQL Databases” Azure will take you to this page.  If there already SQL Databases, you will see them listed here.

Before we move on, I would also like to point out that there are several methods to reach this page.  Most notably is by using the menu item on the left.  Then you will need to create the database, you can click the “SQL Databases” option.  This will take you to the part of the portal that will allow you to create a database, as you see in the image above.

Let’s take a tour of what we are seeing.  Your interface should look like below.

This first thing I would like to point out is the Subscription.  Below you can see that the name of the subscription I am using is Pay-As-You-Go.  This is the default name you will see when you first create the subscription.  You can rename it if you like.  Be cautious when adding items to Azure, if you are not careful, you could end up with an unexpectedly large bill.  Just be aware of the potential cost before creating items in Azure.

You also have the ability to change the columns that you see in the list.  You simple click “Edit Columns”.  You can also click “Refresh” to see new items if they do not appear.

Once you click “Edit Column” you will see the form below.  This is very similar to most products that have the ability to change the columns in a list.  As you can see below you can move columns back and forth.

The list will identify all the SQL databases you currently have in the subscription.  As you can see, I currently do not have any databases.

Regions

Before going into how to create an Azure SQL Database, I thought I would take a moment to talk about what a Region is.  A region is nothing more than a geographic location of where you want you resources to reside.  It is also important to note that not all services are available in all regions.  Here is the link to Microsoft’s page on Regions.  Ideally you would like to have the resources in the closest region.  As part of the database creation process, you will need to place a few resources in regions.

Here is a map and a link of the current regions:

Creating the Database

Now that we have gone over how to get to the point where we can create a database, let’s create one.

When you first click the “Create Database” button you will see this.

The first bit of information you will need is which subscription you will be adding the database to.  In my case, I just have the one so Azure has defaulted to that one.

The next item is the Resource Group.  Let’s take a moment to get to know exactly what a resource group is.

Microsoft defines as Resource Group as:

“A container that holds related resources for an Azure solution. The resource group includes those resources that you want to manage as a group. You decide how to allocate resources to resource groups based on what makes the most sense for your organization”

Here a few rules around Resource Groups:

  • A resource can only exist in one Resource Group.
  • A resource can be moved to another Resource Group at any time
  • A resource in one group can interact with a resource in another group
  • A Resource Group can contain resources from different regions
  • Ideally, the Resource Group and the resources should be placed in the same region

The link above to Microsoft has the rules listed.

Now that we have gone over regions and Resource Groups, it is time to create the database.  The first thing we need to do it give the database a name.  Just as in an On-Prem SQL Server, there are rules around how you name your database.  What is nice is that Azure will pop up and tell you if you are violating one of the rules.  Just as with an On-Prem instance, the rules are pretty straight forward.

If you violate one of the rules, Azure will tell you. Just as seen below.

Now that we have provided a name for the database, the next step is to assign it to a server.

If you have not created a server yet, you can simply click the “Create New” link.  A server for Azure SQL Database is a logical container for your SQL Databases and provides a centralized point for administration.  Here is a link to Microsoft’s documentation on Server.

When creating a server, you will also need to create an account that will be used as the administrator account.  You will also need to provide a password and a region.  When providing a name, the name cannot be the same as the database. Notice that under the server name, you will see, “.database.windows.net”.  This will be important information you will need when connecting to your database with SQL Server Management Studio.

Once you have click the “Select” button and filled out all the information, it may look like you are done, but you are not.  We need to provide additional information.  By clicking on the “Additional Setting” item towards the bottom we will see the what else we need to provide.

The first item we need to address is the Data Source.  Here you have three options.

  • Blank
  • Restore from Backup
  • Sample

There is only one option at this point for the sample database, AdventureWorksLT.  If you chose None, this is simply a blank database and you will need to review the Collation to see if it appropriate for your needs.

The next item you will look to look at are the tags.  Tags are not required, but can be useful when grouping your databases.  For example, if you have a system that uses more than one database, you can take them with the name of the system.  Just a note, keep in mind cross database queries are not allowed in Azure SQL Database.

Now that we have provided all the required information it is time to review before creating the database. You will want to pay close attention to the Product Details section.  Notice that it has an estimated cost per month.

The rest of the options are pretty straight forward, however should be carefully be reviewed before creating the database.

Once you click “Create” you will see this.  If you look in the upper right corner of the image, you will see something that looks kind of like a bell.  This is the notifications and you will see the blue line under it move from the left to the right while the deployment is in progress.

Once the deployment is complete, Azure will tell you as seen below.

Now that we have created the database, we can confirm it by returning to the list we talked about earlier.  Just click the SQL Database menu item on the left and you will be returned here.

Notice that our new database is listed.

I would also like to point out the Tags. You will see this list along the top.

If you click the drop down arrow you will see this and the tags that was created. By clicking the box next to Adventwork, the list will only show items that have a tag of Adventwork.

Connecting to the Database

Although this post was mostly about how to create an Azure SQL Database, I would also like to go over how to connect to your newly created database. If you click the database name in the list, you will be taken to a page that will list all the important information about your database.  This includes the subscription name, database name, status, location as well as a number of other usefully bits of information.

However, in order to connect there is one thing we need to copy, that is the Server Name.  In my case, blogdbsvr.database.windows.net.  If you move your mouse just to the right of it, you will be able to copy it to the clipboard.

Once on the clipboard, it is time to open SSMS. You will connect just as if you were connecting to an On-Prem SQL Server using SQL Authentication.

Once you enter a user name and password, you click Connect.  This is where you will begin to see a few differences.  You will be asked to login into your Azure account as well as a few screens about your IP address.

Once connected you will see a few significant differences from an On-Prem instance of SQL Server. Notice that the SQL Server agent is no longer present. As a DBA, this is a huge difference because so much of our on going maintenance is dependent on the Agent.  No worries there, Microsoft has provide a number of ways for us to schedule maintenance jobs.

Below you will see many more differences.

Listing all the differences is outside the purpose of the post, however there is a lot of really good information available.  I may cover some of these differences in future blog posts.  Once you are in SSMS, you can do many of the same things you have done with an on-prem instance.  Including creating objects, writing queries and working with security.  Please keep in mind that there are some differences on how security is set up, however that is for another time.

Thanks for reading the long post on how to create an Azure SQL Database.  This is the longest blog post I have done so far.  Hopefully you found it useful.

Anatomy of a SELECT statement part 6 – the WHERE clause

After a brief break, ok..maybe not so brief, this post continues the series of the Anatomy of a Select statement.   This post is about the WHERE clause.  The WHERE clause will provide a filter to limit the rows returned to only the rows that are needed.  As we should always do, it is important to only pull the data that is needed.  The WHERE clause is a key part of that effort.  We use the SELECT to limit the columns, while the WHERE is one of the statements that can limit the number of rows.  The sample code will be at the end of this blog post.

The first thing we should look at is exactly where does the WHERE go?  As indicated below, it follows immediately after the FROM.  Since the FROM is the first part processed, the WHERE is the second clause processed.

SELECT …..

FROM ……

WHERE….

The Expression

When utilizing the WHERE clause, you will also need to create an expression that will be used as a comparison for limiting the rows returned.  How this expression looks will depend on the data type you are comparing.  Although there are many data types, you will see a few fare more frequently then others. These will include the string based data types like VARCHAR and CHAR,  the DateTime related data types and numeric data types.

This expression can be a number of things.  First of all it can be a simple value you are attempting to compare. In the statement below you will see after the WHERE clause, there is a column name, a comparison operator and a comparison value.

Comparing to a Value

In the statement below we are comparing the SalesOrderID column values to the number 4.  The expression must evaluate to TRUE or FALSE. If it evaluates to TRUE, those records are returned. Of course the means that if the expression evaluates to FLASE that row will not be included in the result set.

FROM …..

WHERE SalesorderID = 4

When comparing a character based data type such as VARCHAR, you of course must include the single quotes. We will talk about wild card searches later in this post.

FROM ……

WHERE LastName = ‘Smith’

In this case the column name is LastName, the comparison operator is the equal sign(=) and the value to be compared is ‘Smith’.

Comparison Operator Options

We have a number of options when deciding on what comparison operator to use. This list includes, but not limited to, the list below.

Equal sign ( = )

Minus sign ( – )

Plus sign ( + )

Not Equal ( <> )

Greater Than ( > )

Less Than ( < )

IN

NOT IN

More than one Comparison

Many times we will have to complete more than one comparison to capture the rows we are looking for.  When you look below you will see that this WHERE clause is looking for anyone that has the LastName of Smith and the StateOfBirth is WI.

WHERE LastName = ‘Smith’

AND StateOfBirth = ‘WI’

You can also use the keyword OR rather than AND. In the statement below, All the records with a last name of Smith will be returned.  In addition, all records that have the StateOfBirth of WI.

SELECT ….

FROM …..

WHERE LastName = ‘Smith’

OR StateOfBirth = ‘WI’

An example would be if I have a last name of Smith, but I was born in New York, I would be returned in the results because I have the last name of Smith, even if the StateOfBirth is Ohio.

Using a List

The WHERE clause also supports the use of the IN keyword.  This can be used if there a multiple values in the date set that I would like to see.

SELECT …..

FROM …..

WHERE LastName IN (‘Smith’, ‘Jackson’, ‘Denault’)

In this case, all records that have the last name of Smith, Jackson or Denault will be returned in the result set. As you can see, this also works with numeric data types.

SELECT …..

FROM …..

WHERE EmployeeID IN (1, 2, 3, 4)

The above statement can also be written using Less Than or Equal to comparison operator.

SELECT …..

FROM …..

WHERE EmployeeID <= 4

Using the NOT Keyword or <>

Just as you can search for values that are equal to something, you can also search for something that is not equal to.  How you do this depends on if you are excluding a single value or a list of values.

If you are comparing a single value, you simply use this, <>.

SELECT …..

FROM …..

WHERE EmployeeID <> 4

This also works for character based data types as well.  The statement below will return all records that have a LastName of something other than Smith.

SELECT ….

FROM …..

WHERE LastName <> ‘Smith’

What Else Can be Used the WHERE

As mentioned before there are a number of ways to pull data from a table and limit the number of rows using the WHERE clause.  There is another option I would like to mention.  Although it may not be the best option, using a sub-query is indeed an option.

A sub-query is nothing more that a query in the SELECT statement.

SELECT *
FROM Sales.SalesOrderDetail
WHERE ProductID IN (SELECT ProductID
FROM Production.Product
WHERE MakeFlag = 1)

If you look at the above statement, the sub-query is in green.  This will work just fine.  If the sub-query returns more than on row, it will still work because of the use of the IN keyword.

A sub-query is nothing more that a query in the SELECT statement.

SELECT *
FROM Sales.SalesOrderDetail
WHERE ProductID IN (SELECT ProductID
FROM Production.Product
WHERE MakeFlag = 1)

However, if I were to change the IN keyword to an equal sign, it will fail. You will see this error.

Msg 512, Level 16, State 1, Line 45

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The reason it is failing is because the equal sign expects just a single value, while we passed in a number of values.

Wildcard Searches

Sometimes when we are searching for something, we don’t know enough details about the value we are looking for.  This is where the wildcard searches come in to play.

There are a number of options where using a wildcard search, we will just focus on the use of the % character.  This the same as searching with an * to search for a file, like *.docx.

The statement below will return any state that starts with the word New.

WHERE State LIKE ‘New%’

This statement will return anything that starts with New and ends with the letter K.

WHERE State LIKE ‘New%k’

The code below will return any state that has the work new in it.  Notice that the percent sign is also at the front of the value.  While all wildcard searches may not be the best performing, when placing the % at the beginning of the value, this will force a scan rather than a seek.  This is a potential performance problem and should be used with caution.

WHERE State LIKE ‘%New%’

Notice that these statements use the LIKE keyword.  If you use a wildcard search with the %, you must use the key word LIKE.  If you do not, SQL Server will process the string of ‘%new%’ as a literal and look for the word new and having the % both at the beginning and at the end.  It will not be processed as a wildcard.

Examples Below

These examples will work with the AdventureWorks2014 sample database.  Although it could work on a newer version of the database, I just haven’t tested it.

SELECT *
FROM Production.Product
WHERE Productid = 4

SELECT *
FROM Production.PRODUCT
WHERE Name = ‘Blade’

SELECT *
FROM Production.Product
WHERE MakeFlag = 1
AND FinishedGoodsFlag = 1

SELECT *
FROM Production.Product
WHERE MakeFlag = 1
OR FinishedGoodsFlag = 0

SELECT *
FROM Production.Product
WHERE Name IN (‘HL Road Frame – Black, 58’, ‘Headset Ball Bearings’)

SELECT *
FROM Production.Product
WHERE ProductID IN (1, 2, 3, 4)

SELECT *
FROM Production.Product
WHERE ProductID <= 4

SELECT *
FROM Production.Product
WHERE ProductID <> 4

SELECT *
FROM Sales.SalesOrderDetail
WHERE ProductID IN (SELECT ProductID
FROM Production.Product
WHERE MakeFlag = 1)

SELECT *
FROM Sales.SalesOrderDetail
WHERE ProductID = (SELECT ProductID
FROM Production.Product
WHERE MakeFlag = 1)

–This well return all records have a Name starting with Blade.

SELECT *
FROM Production.PRODUCT
WHERE Name LIKE ‘Blade%’

–This well return all records have the word name in the product name somewhere.

SELECT *
FROM Production.PRODUCT
WHERE Name LIKE ‘%Blade%’

–This well return all records have the a name that starts with the letter A and ends with the letter r.

SELECT *
FROM Production.PRODUCT
WHERE Name LIKE ‘A%r’

This may conclude my series on the Anatomy of a Select statement.  During this series of posts, I covered the processing order, SELECT, FROM, WHERE, GROUP BY\HAVING and ORDER BY.

Thanks for visiting my blog..I really hope you learned something as you read it!

 

 

 

 

 

 

 

 

 

 

Where are the events for Extended Events stored

Today I received an email from someone who attended my Extended Event session on Boston last fall.  He was asking what table stores information about what Extended Events exist on the server.  The answer is sys.dm_xe_objects data management view.

In this DMV, obviously there are a number of columns.  This post will focus mostly on the object_type column.  This column has a number of potential values, including Event, Target and Action.  When setting up a SQL Server Extended Event session these are a few of the key items that need to be filled out.

Let’s just take a few moments to identify what these are.

Target – This is where the data captured by the Extended Event session will be stored.  Many times you will find the target to be the event_file target, although from time to time you will find the data going to other targets.  The data will be stored in an XML format. See the image below.

 

Actions – These are also referred to as Global Fields.  These are the fields that many of the events have in common.  You can decide what data you would like to collect.  These differ from the Event Fields in the sense that are shared with all  the events.  Event Fields are data points that are specific to that event.  Sometimes you will find a particular data point in both the Actions and Event Fields.

Events – This is the focus of this post.  Events are things that happen on the server and you would like to capture the what, when and other information about the event.  As each release of SQL Server comes out, there are more and more events included. The image below is an example of some of the events you might want to use.  In this case they are related to Deadlocks.

Now back to the purpose of this post, where is this information found in the system tables.  As stated above, the information can be found in the sys.dm_xe_objects DMV.

If you run this query you will see all the events for that instance.  When I run this on my SQL Server 2017 server I get 1,505 events.  Although, your results might be slightly different depending on version.

SELECT Name
, Description
FROM sys.dm_xe_objects
WHERE object_type = ‘event’

Here what the data set looks like.

There are really two columns that are important for this post, the Name and Description columns.

Thank you for stopping by and a big thank you to the attendee that emailed me the question.