Renewed my Azure Administrator Associate Certification!

 

I'm happy to say that I passed the renewal assessment for my Microsoft Certified: Azure Administrator Associate. 

In December 2020, Microsoft changed the renewal process to help learners stay current - those with an active certification which was within 6 months of expiring could take a free online assessment to renew their certification. The assessment can be retaken multiple times free, but you have to wait at least 24 hours before retaking it. On the renewal page for the certification, Microsoft provides links to Microsoft Learn training directly related to the assessment content, so you will know exactly what topics to study. 

It's definitely a less stressful way to take the exam: you can take it using own machine in the comfort of your home - no driving to a test center and using an antiquated "testing computer" covered in other people's schmutz. And you can't beat the price: free. 

Be sure to renew your certifications when they are close to expiration.

Moving Azure SQL Database from One Subscription to Another

Multiple Environments

Many companies like to separate Production from Development / UAT environments. I have frequently seen Dev / UAT deployed to one subscription, and Production to a different subscription. This works well for the Finance team because the billing for each environment is clearly separated. However, having the environments in different subscriptions inserts a speed bump in a common practice among DBAs - restoring a copy of the Production database in the UAT and Dev environments to ensure that code is synchronized across all environments. 

Azure SQL Database Restore Database 

In Azure SQL Database, when you select "Restore", your options are limited to restoring the same server. Notice that the options for changing the subscription, resource group and server are greyed out. Only the database name can be changed. 

If you need to restore a copy of your Azure SQL database on the same server so that you can copy an accidentally deleted table back into your database, then this works fine. 

The restore Azure SQL database operation will not allow restoring the database over the existing database, however, you can change the name of the database at any time. 

Rename Azure SQL Database

In SQL Server management Studio run the following command on your Azure SQL Server: 

ALTER DATABASE OriginalDatabaseName MODIFY NAME = NewDatabaseName;

Azure SQL Database Copy Database

The next option would be to COPY the database. This option looks promising because it allows the database to be copied to another server, and if a different Azure SQL server does not exist, a new one may be created without having to leave the Copy dialog and create one separately. 

Although the New Server option looks promising, it does not provide the desired options: changing the subscription and resource group. 

All is not lost, a solution exists: since an Azure SQL Server can move from one Subscription to another, the database can be copied to a Migration server, moved to the other Subscription then copied onto the existing server to refresh the existing database. 

It's Easier than It Sounds.

You must have at least Contributor rights in both subscriptions to successfully perform this operation.

• Copy the Azure SQL Database to a new migration Azure SQL server using the "Copy database" blade as shown above. This server will exist only for the duration of the database transfer and can be deleted immediately following the completion of the process. 

• Go to the Migration Azure SQL Server's Overview page. 

 

• Click on "Move" and select "Move to another subscription" in the drop list

In this above example, the user does not have access to move resources to the selected resource group. 

• With appropriate permissions, the next step is validation of resources to move. 

 

(The above image is linked to the image in Microsoft Documents "Move resources to a new subscription" article because my account doesn't have rights to perform this action so I cannot get a good screenshot. The validation dialog should show only the Migration Azure SQL server in this step.) 

• Upon completion of Validation, the Review screen pops up.

• Confirm Source and Target subscriptions, resource groups, and number of resources to move then check the box and click "Move". 

• Azure will pop up a notification when the move has completed.

• Go to the subscription and resource group where the Migration server has moved.

• Go to the overview page of your Azure SQL Database on the Migration server.

• Click on "Copy"

• Select your UAT or Dev Azure SQL Server and set the database name. 

• Click on "Review + Create"

• Click on "Create" to complete the process

Upon successful completion of the deployment, the migration server may be deleted. 

Your Synapse Moment: Adding Synapse Workspace User

 Just a quick script for Azure Synapse. Let's say your synapse workspace is named "dev-synapse-workspace", and you've already set up the Managed Identity

To allow synapse to actually interact with your SQL pool user databases, in each database you should run the following script (put your synapse workspace name in place of the example name)

CREATE USER [dev-synapse-workspace] FROM EXTERNAL PROVIDER;

exec sp_addrolemember 'db_owner', 'dev-synapse-workspace';

After that, your pipelines should be able to easily access your Synapse SQL Pools. 

This same code works in an Azure SQL database if you need to access one from Azure Synapse. 

Happy coding!

My NEW Crafting Blog

 I have decided to completely separate my technical blog (SQL server / Azure posts) from my crafting (sewing / crafting) blog because it makes more sense to have them separate rather than intertwined. 

Crafting blog: https://craftingwithandie.blogspot.com/

This blog will remain as the technical blog. If you're interested in reading about my future adventures in crafting and sewing, please visit my crafting blog. Otherwise, stay tuned here for more interesting Azure and SQL server articles.

First Heat Transfer Vinyl Project: Awesome Aunt Shirt

 I'm fairly new to using a cutting machine - I bought my Silhouette Cameo in April 2021 - and since my niece was planning to visit me soon, I thought it would be neat to have a shirt labeling me as an awesome aunt. Awesome aunts do crafting with their nieces, of course, and I planned to help her make some things using the Cameo. 

For the shirt, I decided to use one of my old but rarely worn t-shirts just in case my first use of heat transfer vinyl (HTV) was a disaster and it had to be discarded. After searching around, I found a "This is what an awesome aunt looks like" design on Creative Fabrica in their print templates category. 

I was happy to see that it was not only a free design, but it also came with a commercial license. Most other design houses mark their free designs as "personal use only", which means you can't use those designs for items you sell. All of Creative Fabrica's free designs come with a commercial license, which is fantastic and extremely generous. They also offer more free designs than most sites - over 25,000 free designs including fonts, SVGs, graphics and machine embroidery files are available in their freebies section. Creative Fabrica also has "daily gifts" which are selected premium fonts, crafts, and graphics designs available free for one day only.

The "This is what an awesome aunt looks like" design came in 6 formats: AI, SVG, DXF, EPS, PNG, and PDF. I have Silhouette Studio Designer edition so I chose to use the SVG instead of the DXF because I read that SVG is supposed to be the best non-.studio3 file format available. I made one small edit to the file, removing the extra space between the k & s in "looks". I also resized the design so it would fit on my 12" x 7.5" piece of HTV.  I then connected my machine and prepared to send the file.

My Cameo came bundled with a collection of vinyl including 9 sheets of HTV. I selected the silvery gray because it looked like it would stand out well on the dark green shirt. After rechecking the documentation on determining which side of the HTV to cut (dull side up because shiny side is the carrier sheet), I placed the HTV on the mat and loaded it in the machine. 

When I hit "send", Silhouette Studio asked me if I remembered to mirror the design? 

I looked back at the design and realized I had forgotten to reverse the design. I mentally thanked the creator of that error message - I'm sure many have forgotten to flip designs and ruined numerous sheets of expensive HTV in the process. I canceled the job, returned to editing mode, mirrored the design, and switched back to the send screen to send it to the machine. The design cut out smoothly and relatively quickly.  

After weeding the design, I stuck it to the shirt and checked the design placement by pinning the shirt's shoulders to my dressform. 
I decided to move the design up closer to the neckline because my initial placement put the hands pointing right at my bust (not a good look). The higher placement looked much better, so I put the shirt on my ironing board and ironed the HTV. I found I had to press certain sections again because the HTV was pulling up when I tried to remove the backing. I think that would happen with any HTV design when using a regular household iron. I have a heat press on my wish list so I can do more HTV designs with less struggle. 

I wore the shirt the day my niece visited. She noticed it after we had been talking for about an hour. 

I have washed the shirt once and noticed that couple of letters are starting to peel up on the edges. The good news is that was easily fixed by ironing the shirt inside out. Of course, if I had that heat press, it would have stayed on better. 

I think I'm going to have a lot of fun decorating other garments with HTV. I have already ordered more HTV in a variety of colors.

HAPPY CRAFTING!

Where did my table valued functions go in Azure Synapse?

I recently discovered that you can create table functions in Azure Synapse,  but they are NOT listed in SSMS.  Also, even if you know the function's name, you can't use sp_helptext to find out what it does.

So how can we find them or find out their definitions if we know someone created some functions? 

Here's the query you need to pull from system tables:

SELECT s.name
    ,o.name
    ,DEFINITION
    ,type
FROM sys.sql_modules AS m
JOIN sys.objects AS o
   ON m.object_id = o.object_id
JOIN sys.schemas AS s
   ON o.schema_id = s.schema_id
WHERE o.type IN ('IF','FN');
GO

Have fun with Azure Synapse!

Preparing for the New Azure Certification Exams

Now that I have a few Microsoft certification exams under my belt, I decided that it's time to write a post with tips on preparing for the new Azure exams. Microsoft recently updated most of the Azure exams - and they're updating the rest over the next several months - so the exam experience isn't what it was even a year ago.

Your first stop should be to review the exam overview page, especially the skills measured section, as that is the official list of topics the exam covers.

Next, watch Azure videos and online classes related to the exam topics. You can find free Azure classes on Microsoft Learn and Pluralsight. Pluralsight and Udemy have many in-depth paid Azure training classes as well.

You will also want to get experience in Azure itself. Be sure to check out the Microsoft hands-on labs which allow you to get practical Azure experience without incurring cost from your own paid Azure subscriptions.

Finally, try a few practice tests. I recommend Microsoft’s official practice exam vendor MeasureUp, because they have the most up-to-date practice exams which include all the current type of questions.

In addition to questions, there are now practical tasks (or labs) on the exams. The new labs require you to log into a live environment and complete a number of tasks in Azure. The labs are not  simulation nor stripped down environments, but rather examples of real-world scenarios to truly test your skills and abilities.

The exams will always begin with a series of conventional questions to provide time for the labs to be provisioned and loaded into the environment. If you answer these questions too quickly, you will have to wait for the labs to finish loading. Carefully manage your time in the exam - at the end of each section, you can review your answers, but as soon as you leave that section, you cannot return to it. 

In the labs, you do NOT have to wait for tasks to complete before moving onto the next one. Starting a task by hitting "create" is sufficient for scoring since the provisioning of that resource will complete while you are finishing up other parts of the exam. Microsoft does NOT require you to use a specific method to perform tasks in the labs. If you are more comfortable using PowerShell or CLI, instead of a GUI, then use it. The exam is scored on the end result not on how the tasks were executed.

Each Role-based certification expires in two years. That may not sound like a good thing, but given the rate at which Azure changes, it makes sense - the current certification aligns more closely with the current state of Azure. Microsoft hasn't yet announced what the recertification requirements will be.

Azure SQL Managed Instance

Azure SQL managed instance is a relatively new Azure offering. It has nearly 100% feature compatibility with the latest on-premises SQL Server Enterprise Edition Database Engine and allows relatively easy lift and shift of on-premises applications to the cloud with minimal application and database changes. At the same time, Platform as a service capabilities such as automatic patching, version updates, backups, and high-availability drastically reduce required management overhead and total cost of ownership.

For customers running older versions of SQL Server, I'm happy to report that the Azure SQL managed instance supports backward compatibility to SQL 2008 databases. It supports direct migration from SQL 2005 database servers, however migrated SQL 2005 databases will have their compatibility level updated to SQL 2008.

A great benefit of choosing the managed instance deployment option is that it will always be up-to-date because patches and upgrades are automatically applied. Also, you don't have to purchase, manage or secure physical hardware.

Managed instance has High-availability built in and pre-configured using a technology similar to Always On Availability Groups, providing 99.99% uptime SLA. It also has automated backups and point in time restore.

With the managed instance you will spend less time on administrative tasks because the service either performs these tasks automatically for you or greatly simplifies them.

It supports Azure AD authentication as cloud alternative to Windows authentication, automatically manages XTP filegroup and files for databases containing In-Memory OLTP objects and supports SQL Server Integration Services (SSIS) and can host the SSIS catalog (SSISDB) that stores the packages. SSIS packages must be executed on a managed Azure-SSIS Integration Runtime (IR) in Azure Data Factory (ADF). Unlike Azure SQL Database, it allows cross-database queries.

There are two service tiers for Managed Instance: General Purpose and Business Critical.

General purpose: Designed for applications with typical performance and IO latency requirements.
Business critical: Designed for applications with low IO latency requirements and minimal impact of underlying maintenance operations on the workload.

Server sizes range from 8 to 80 vCores depending upon service tier. Memory is 7 GB per vCore.
Maximum storage is 8 TB for general purpose and up to 4 TB for Business Critical

NEW Stuff in Azure SQL Managed Instance!

As of 11 April 2019, managed instance now has a public endpoint. (YAY!)
Here's a link to the official Microsoft article on it - with full instructions for implementing it via Powershell

Azure Data Studio - now with Notebooks

Azure Data Studio is a light-weight cross-platform database tool based upon Visual Studio Code. It runs on Windows, Mac, and Linux. It includes a SQL editor with IntelliSense, keyword completion, code snippets, code navigation and Git source control integration. Although SQL Server Management Studio still remains the champion for database administration tasks, I believe that for developers and Mac and Linux users, Azure Data Studio is the best choice. I also like the fact that Azure Data Studio uses far fewer resources on my machine than SSMS.

As of March 2019, Azure Data Studio connects to:

• SQL server 2014+
• Azure SQL database
• Azure SQL Data Warehouse
• Azure SQL Managed Instance
• PostgreSQL servers 

One of the newest and I think more notable features that has been added to Azure Data is its ability to create a SQL notebook.

Notebooks are type of document that combine formatted text with live code elements, images, and query results. You may have heard of them as "Python Jupyter Notebooks". Well, now they can be used with SQL in addition to other programming languages such as Python, Scala, and R.

To create a notebook, simply open Azure Data Studio, then select New Notebook from the file menu. Add a code type cell (or section), check that the Kernel is "SQL", set the connection to your favorite database, enter some SQL code and execute it.

The IntelliSense in Azure Data Studio simplifies code entry, and the right-click menu offers some excellent options, including formatting the document, changing all occurrences of the selected word, and taking a "Peek" at the definition of the selected SQL object (table or stored procedure name).

Just think of all the possibilities!

These notebooks will be great for technical presentations. I can prepare formatted text to describe the concepts presented in my session, and include executable code within the context of the document which the attendees can run at home on their own systems after they download the demos.

Another great scenario would be for troubleshooting or baselining a server. Because Notebooks allow you to save the query results inside the file, you can run diagnostic queries and save the results all in one neat package. Then, later on when you wish to compare to the original baseline, make a copy of the notebook, clear the results and re-run all the queries to see the differences.

For problem solving,  you could create a Notebook containing instructions and a series of queries, ask your client to run the queries and return it to you with the results populated.

I think these new features really make Azure Data Studio outshine old SSMS. I may not remove SSMS from my machine just yet, but I'm pretty certain it's not going to see very much use in the future.

I'm Speaking at Phoenix SQL Saturday!

Just a quick post to let you know that I'll be speaking at the Phoenix SQL Saturday on May 4, 2019.

SQLSaturday is a free training event for Microsoft Data Platform professionals and those wanting to learn about SQL Server, Business Intelligence and Analytics. This event will be held on May 04 2019 at Chandler-Gilbert Community College Pecos Campus, 2626 East Pecos Road, Chandler, Arizona, 85225, United States

Hope to see you there!!

---

Shoe lovers: I'm a DSW ambassador, so please use this link when going to DSW Shoes

---

Meet Azure Key Vault

What is Azure Key Vault? Azure Key Vault is a cloud hosted service offering secure storage and access for certificates, encryption keys, connection strings and other secrets. It streamlines the key management process and provides full control of keys for accessing and encrypting your data. Administrators can grant and revoke access to keys as needed. Key vaults also control the access to anything stored within them.

Applications and Azure resources authenticate to Key Vault to retrieve the secrets. The best authentication method for Azure services is to use a managed identity because it allows Azure services to authenticate to Key Vault or any service that supports Azure AD authentication, without having to include credentials in your code. With Azure Key Vault, applications never have direct access to keys.

Administrators can monitor and audit key use with Azure logging.

Key vault allows the separation of security maintenance from application development. For example, Key Vault secrets can be used to store connection strings for various resources accessed by ADF. Those connection strings can be updated by Administrators without affecting the ADF pipelines or having to send the new passwords out to developers.

It only takes a minute or two to create a Key Vault. In the Azure Portal, click on the Create a Resource, search for Key Vault, then click create.

Next, provide a name for the Key Vault, select subscription, resource group and location. Finally, select pricing tier.  Refer to https://azure.microsoft.com/en-us/pricing/details/key-vault/ for details on current pricing. Most users will select standard tier unless their security policy requires the premium features.

Once the key vault has been created keys, secrets, and certificates may be imported or manually created.

To create a secret, click on "Secrets", then Generate/Import.

This will open the "create a secret" dialog. Choose a name for the secret, such as dev-Adventureworks-Connection for the connection string for Adventureworks database in Development environment. Enter the full connection string in the Value field, write a brief description of the secret in the content type box and click "Create" to complete the creation process.

Once you have created your key vault and some secrets or keys, they may be used in any Azure resource which has been granted access to the Azure Key Vault. That will be covered in another post.

Power BI: M Query Basics (Part 2)

This is the second part of my series on Power BI M Query Basics. In Part 1, I defined M Query and talked about the structure of how an M Query query is written. If you haven't read the first part, go ahead and read it now....I'll wait.

OK, on to

M Query - the Second Part!

 

M Query Editor

Open up a Power BI report in Power BI desktop. On the Home ribbon, click on the Edit Queries > Edit Queries to open up the Query Editor.

In Query Editor, you can get to the M Query behind one of your queries by clicking on Advanced Editor either on the Home tab or on the View tab of the editor. (it's available in both locations)

Once you have Advanced Editor open, you can edit the M Query associated to the selected query.

The first thing that you will notice about the built-in M Query editor is that it doesn't have any context highlighting, matching brackets are not highlighted, and syntax errors are extremely difficult to track down. Editing M query in here is not for the faint of heart - most of us are very thankful that the Query Editor user interface can auto-generate about 90% of all of the M Query code that will be needed.

Thankfully, it's not the only option. 

M Extension for Notepad++

 

First up is the M Extension for Notepad++. I'm a big fan of Notepad++. It's lightweight, loads fast, and has context highlighting available for most programming languages. The official plug-in page for Notepad++ doesn't yet have a link for a fully completed extension, but you can visit Lars Schreiber's blog for instructions to set up your own M language highlighting (you only have to do it once, then it's there forever as a language choice in Notepad++).

The above screenshot shows the same "May Earthquakes" M Query except within Notepad++. Already you can see that pasting your M Query into Notepad++ makes it so much easier to understand and edit. 

M Extension for Visual Studio Code

 

A second editor to try for working with M Query is Visual Studio Code. This is a free application available from the Visual Studio Code site. Once you've installed Visual Studio Code, click on the Extensions button and perform a search for Power (full name is Power Query M Language).

Hit install and it will be ready for use. What's really lovely about Visual Studio Code is that the M Query Extension provides intellisense - when you start to type it pops up a context sensitive list of possible M Query functions which could apply to what you are typing.

This is handy for medium-weight development where you are typing in a substantial amount of code or making major changes and prefer to have some assistance with syntax. You will have to paste the code back in Power BI Advanced Editor to test it.

Power Query SDK for Visual Studio

 

For serious M Query writers, Visual Studio now has a Power Query SDK. This add-in for Visual Studio 2015 and Visual Studio 2017 allows developers to create Visual Studio projects using M Query (aka "Power Query). There are two types of projects available:

The Data Connector Project is intended for creating additional Power Query extensions, and the PQ file is for writing M Query queries like we've been working with.

Create a new PQ file project and paste in the M Query from your Power BI Advanced Editor. The PQ file projects can only handle one PQ file per project, so don't try to add a second PQ file. If the M Query contains a syntax error, Visual Studio highlights the error with the same red underlining as it does with any other programming language. It also will provide intellisense and bracket highlighting to make coding easier.

But wait...there's more! 

Visual studio also allows execution of the M query. To run click on the "Start" button (the usual debugging button). 

The first time you click on "Start", a dialog will pop up requesting credentials for the data source. Once credentials are set, close the Error dialog and click "Start" once more. The window will pop up again, but this time data should load. 

This will certainly save time since it won't be necessary to copy the M Query back into Power BI Advanced Editor until all modifications have been completed.

 That concludes Part Two of the M Query blog series. Join me in Part Three when I walk through a demonstration of writing M Query without using the Power BI Query Editor User Interface to auto-generate the M Query.