A couple months ago, I was asked to add geo-replication for all our Azure SQL Databases to align with our recovery strategy in case of disaster. A few weeks ago, when upper management finally realized the full cost of that replication for all our databases, they requested that we remove replication from anything that isn’t business critical and doesn’t need to be recovered immediately in case of a disaster to reduce the shocking cost of replication.
I mistakenly didn’t do research before doing what I thought was fully removing the replicas I had previously created, which was removing the replica from the primary databases. I only recently realized that those replica databases were still alive and well and charging us money that we thought we were already saving while I was reviewing resources for another task . Keep reading to learn how to do better and fully get rid of the replicas you no longer need.
A replica for an Azure SQL Database is a way to make a secondary copy of your database on a separate logical SQL Server in a different region that you can keep available to failover to in case of a full region outage in Azure. Although this scenario is rare, it has happened in the past, and most companies do not want to be caught without their vital resources for hours while Microsoft troubleshoots their outage. In such a case, having a geo-replica means that you can immediately failover to an exact copy of your database in a different region and keep your business running.
How to Remove Replica Link from Primary
Getting rid of a replica of an Azure SQL Database is a two step process. The first step is to remove the replication link between the primary and secondary databases, which I will cover here, and the second step is to delete the database itself, which I will cover in the section below.
Removing the replication link between primary and secondary is as simple as the click of a button. Navigate to the primary database for which you want to remove the replica, and go to the “Replicas” page under “Data Management” in the menu.
On that page, you will see the primary database listed first, then in the section below that, any and all replica databases.
To remove the replica, you will click on the ellipses menu on the right side of the replica database, then choose “Stop Replication”.
At first I was confused as to why this said that it was going to stop replication because I was assuming that I would be able to delete the replication and delete the replica in one step. But now I better understand that this is a two step process.
After you have chosen to “Stop Replication”, you will get a prompt to have you confirm that you want to remove the replica. It also clearly points out what happens when you choose to do this, but I just didn’t understand what it meant. “This will remove server/MySecondaryDatabase” from replication relationship immediately and make it a stand-alone database.” When I read that, I thought it meant that removing the replication would be reverting the primary database to a standalone database, but now I know that it means what it says: the secondary database will become a standalone database that you will later have to deal with.
Click “Yes” to remove the replication relationship.
You will get a notification that replication is being removed.
After a few minutes, you will be able to refresh the page and see that no replica link exists for the primary database anymore.
However, if you search for the name of the database that you previously had a replica for, you will see that the replica still exists, it’s just no longer linked to the primary through a replication process.
Fully Removing Replica (so that it’s like it never happened)
To get rid of the replica you no longer want so you can stop being charged for it, you will need to navigate to that former-replica database in the portal and then delete it like you would any other database. Before deleting, ensure that this is the database that you really want to get rid of since the deletion cannot be undone.
Once you have deleted the Azure SQL Database resource for the replica, you are finally done with removing your replica.
Summary
If you want to remove a geo-replica database from an Azure SQL Database to save money (or for any other reason), you will need to complete the two step process to do so. First, remove the replication relationship between the primary and the secondary through the “Replicas” page under the primary resource. Once that is complete, navigate to the former-secondary database in the portal and delete the resource. Removing the replica relationship alone won’t delete the database, and you will keep getting charged for that secondary database until you fully delete it.
As part of my ongoing series about working with Bicep and SQL Servers and SQL Databases, I am going to cover how you can turn on auditing for a SQL Server instance using Bicep. The auditing I am referring to is what you will find if you go to an Azure SQL Server (Platform as a Service, fully cloud version), then navigate to “Settings” and then “Auditing”. By default, the auditing option will track a few different things that happen on your server, and then you’re able to customize it further if you would like. This post will specifically cover how to enable auditing for a SQL Server using a Storage Account for the destination of the audit logs and the “Storage Access Keys” option for the Storage Authentication Type.
According to the Azure portal, auditing “tracks database events and writes them to an audit log in your Azure Storage account, Log Analytics workspace or Event Hub.” This allows you to keep track of what is happening on your server and its databases in case you ever need to look back and see what changed or if you need to troubleshoot an error in more detail. Auditing gives you a similar level of server and database traceability as the standard SQL Server Logs on a normal instance, just in a slightly different form since it’s 100% in the cloud.
In my organization, we have a requirement for auditing to be turned on for all our SQL Servers as a safety measure, which is why I wanted to learn how to enable it in the portal and then later how to enable it through a Bicep template now that we’re automating our resource creation more.
Note: This Bicep template applies auditing at the SQL Server level, not the SQL Database level. Both are options that you can choose from. Understand that I am not showing how to enable auditing for a specific database, as the template for that is likely slightly different.
In the portal, this is what the Auditing settings page looks like.
Continue reading and I’ll show you how to convert that portal page into a Bicep template that you can deploy without much effort at all.
Full Bicep Template
This post is discussing a subset of a Bicep template that I’ve also covered in part in other posts. If you would like to see the Bicep template in its entirety, you can find it on my GitHub.
Create a Storage Account Resource
Since I have opted to send my SQL Server audit logs to a Storage Account, we first need to create a storage account to send the logs to. If you already have an existing storage account that you would like to send the files to instead, you can skip this step and do an “existing” type reference in the script.
The definition below creates a very standard storage account. The only thing to mention is that it must be a “V2” type of storage in order to work with auditing.
Once you have your storage account created and ready to use, you can then enable auditing on your SQL Server by creating a auditingSettings type resource like what I have below.
The first notable thing is that I have specified the parent of the resource as the SQL Server that I created earlier in the template (not shown in this post, but is on GitHub), which is important to tell the auditing resource what you want it to be applied/connected to. If you created the SQL Server in a separate template, you would have to reference the server differently to get the template to understand what you’re trying to apply the auditing to.
Specify what you want to audit
The next notable thing about this resource definition is the list of auditActionsAndGroups, which I chose as the default values. If you go to the Auditing settings in the portal and hover above the (i) icon next to where you enable the auditing feature, you will get information about what auditing rules the Azure SQL Server will get by default unless you specify otherwise.
The default policies are:
BATCH_COMPLETED_GROUP
SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
FAILED_DATABASE_AUTHENTICATION_GROUP
In my experience so far, those options have been sufficient for my troubleshoot needs, but you can add others to the list if you would like. To see all the options, review this Microsoft document.
The best part of creating the auditing resource through Bicep is that you are able to specify those additional “action groups”. Otherwise, you would have to use PowerShell to update the list of things you want to be audited, since you aren’t able to make that change in the portal at this time.
Retention length and storage account connection
One of the final things you will need to set in your auditing resource is the number of days you would like to retain the logs, with a maximum value of 100. I chose 90 days, according to my organization’s standards.
Then you need to specify the storage account and the storage account keys to grant access with. I have set the storageEndpoint value using a variable which contains the name of my storage account, which was created earlier in the full template. I then need to provide the “primary” key of the storage account which gives the auditing resource access to save logs to the storage account. To do this, since I had created my storage account in the same template above, I extract the keys using built-in Bicep functionality. storageAcct.listKeys().keys[0].value
The breakdown of that key extraction is <stg acct symbolic name>.<function that will list keys>.<first key in list (primary)>.<value in keys array at location 0>.
Summary
With just two resource definitions in a Bicep template, you are able to specify custom auditing for your Azure SQL Server instance, and do extra detailed auditing beyond the defaults which isn’t available through the portal. Let me know in the comments below if you found this helpful. Thanks for reading!
If you’ve been following along in my series on using Bicep templates to deploy database-related resources in Azure, you may have seen my template for creating Azure SQL Servers and Databases on GitHub which contains a section for defining role assignments. I haven’t yet covered the specifics of that type of resource in a post, so I will be going over that today to simplify the information you need to know to assign particular Role-Based Access Control (RBAC) roles to resources you’re creating in Bicep templates. Because why would you want to manually add roles to a resource that you’re automatically deploying? (You don’t!)
The template I am pulling the role assignment section from creates an Azure SQL Server, Azure SQL Database, and then a Key Vault, and you can learn more about that in my previous post. What I didn’t cover previously though, is that in my actual use case, I need to then give my team admin rights on the Key Vault after it’s created so that we have the permissions needed to add secrets to the Key Vault.
The Full Template
If you would like to see the Bicep template I will be covering in today’s post in full, instead of piecing it together from this post, you can find it on my GitHub.
Assigning RBAC Roles to Resources with Bicep
The process of creating role assignments on Azure resources using Bicep templates is relatively simple. The only data you need to supply to create such role assignments are the Object ID of the group or user you want to assign the role to and the ID for the specific role you want to assign. The hardest part of that is to find the ID of the role assignment in the Azure portal.
Get the Object ID for the user or group
To get to the Object ID value for the user or group to which you want to assign a role, navigate to Entra ID in the Azure Portal, then search for and select the user or group from the dropdown list. When you open the user/group, you will find the Object ID value to copy into your template.
Finding the ID for the Role Assignment
The easiest way to figure out the ID value of the role assignment you want to give to a user or group is to go to the type of resource that will get the assignment, in this case a Key Vault, go to the IAM page of the resource in the portal, and then view role assignments and view the JSON for the needed assignment. If you know of a better way, please let me know in the comments below!
First, navigate to an existing Key Vault in your Azure environment and go to the “Access control (IAM)” page.
Screenshot showing the Access control page for a Key Vault resource in the Azure Portal
Then, click on the “Roles” tab. On that page, search for “key vault”, or whatever keyword will help you find the role you’re looking for. Once you’ve found the role you want to add through your Bicep template, click on the “View” link for the role under the “Details” column.
Screenshot showing how you can find a specific role from the Roles tab of the Access control page for a resource
When you click on “View”, it will open another pane with details about the role. In that pane, select the “JSON” tab and then copy out the GUID value from the end of the string for “id” in the JSON description.
Screenshot of the Azure Portal showing where you locate the ID of a role assignment from the JSON details of the role
That is the value you will need to put into your template, so keep that handy.
Creating the roleAssignment resource
The short definition to assign a role to a user or group via Bicep is like the following:
var roleDefIDKVAdmin = '00482a5a-887f-4fb3-b363-3b7fe8e74483' /* The role ID for "Key Vault Administrator" */
var admin_entra_group = '0000-0000-0000-0000' /* Object ID for our Entra group */
var roleAssignmentName = guid(admin_entra_group, roleDefIDKVAdmin, resourceGroup().id)
resource roleAssignment 'Microsoft.Authorization/roleAssignments@2022-04-01'= {
name: roleAssignmentName
properties: {
principalId: admin_entra_group
roleDefinitionId:resourceId('Microsoft.Authorization/roleDefinitions', roleDefIDKVAdmin)
}
}
Variables
I have 3 variables in my template that simplify the process of specifying the settings for the roleAssignment resource.
roleDefIDKVAdmin: The ID value I retrieved from the Azure Portal for the role I want to assign
admin_entra_group: The Object ID for the Entra group I am assigning the role to
roleAssignmentName: A value I generate to give to the roleAssignment resource based on the other two variables. Important to note: the part “resourceGroup().id” is what ties the role assignment to the key vault, because I am granting the role assignment at the resource group level. If you want to apply the role assignment to a single resource, you would do symbolicName.id instead.
Resource definition
Once you have the 2 values retrieved as I specified above, you simply need to pass the variables into the correct locations of the resource definition. The name of the resource is given the name from variable roleAssignmentName. The principalID is the user or group the role should be given to, which is provided through variable admin_entra_group. And finally, the roleDefinitionID which we provide by using the resourceId function in Bicep, which retrieves the actual ID value for a role given the ID value we retrieved manually from the portal. We are required by Bicep formatting to use this function instead of simply passing in that value ourselves.
Summary
Once you retrieve the ID of the user or group you want to give a role assignment to, get the ID for the specific role assignment you want to give, then tie those together with the Bicep template, it’s very simple to set up the resource definition to assign that role to that group when the template deploys.
This is the fifth in my series of posts about working with Bicep, the declarative scripting language created by Microsoft as the modern facade for Azure Resource Manager templates. Today’s post is focusing on a small bit of information that may be important for some Azure environments.
For those of us who work in Azure and have Azure SQL Servers and Databases, we often want those resources to be available to other resources, such as applications, within the Azure environment. To grant that access through the portal, there is a simple checkbox on the Networking page of the Azure SQL Server that will allow that. But if you’re trying to declaratively create your Azure infrastructure with Bicep like I am, how are you meant to accomplish checking that same box? That is what this post will answer.
For those of you who may not be as familiar with what I was referencing above, what I am referring to is this checkbox that you find on the Networking page of an Azure SQL Server instance in the Azure Portal.
Screenshot showing the checkbox I am talking about, which allows other Azure resources to access the SQL Server resource
The purpose of this checkbox, as you can learn by hovering over the information icon at the end of the line of text for the checkbox, is to configure the firewall settings to allow inbound access from Azure resources. By default, this box is left unchecked.
Screenshot showing the information about the checkbox in the Portal screen
However, if you are in a situation where you have a separate application that is going to be accessing the server and database (which is fairly common), you will also likely need to ensure this networking rule is checked when creating new Azure SQL Servers.
How to check that box in a Bicep template
Understanding what the checkbox does
The heart of what that checkbox is doing in the background when it is checked is creating a firewall rule which indicates to Azure services if they are allowed to connect to the server and database or not. You can see what it’s doing by querying the system table which contains the list of server firewall rules, which I wrote about in a previous post.
When the box is checked, you can see a firewall rule for the server in the table sys.firewall_rules (run on the master database), called “AllowAllWindowsAzureIps” with start and end IP addresses both set to 0.0.0.0.
Screenshot of SSMS showing query that lists all server firewall rules
Conversely, if that box is not checked on the portal, you will not see a firewall rule in the table with that name or IP address range. Once you understand that the checkbox is setting a specific firewall rule for you in the background, it becomes simple to figure out how you can make that box get checked through a Bicep template.
Bicep setup to check the box
Since the checkbox in the portal is creating a simple firewall rule with a specific name and IP address, we can recreate that easily within a Bicep template by creating a firewallRule resource within the template and tying that to your Azure SQL Server with the parent attribute.
Create the sqlServer resource in the template
First, create the SQL Server resource so that we can tie the firewall rule resource to it. If you would like to see how to define such a resource to then create a firewall rule for, see last week’s post.
Create the firewallRules resource in the template
After you’ve create the Azure SQL Server resource in your script, the next step is to specify a second resource of the type firewallRules which we will use to check that box on the server’s networking page.
The important parts to note from that resource definition are the following:
name: 'AllowAllWindowsAzureIps': This specifies the name of the firewall rule that is going to be created. In this instance, the rule needs to have this specific name in order to check the settings box in the portal and allow the access
parent: sqlServer: This line is what tells the template that this firewall rule needs to be applied to the server we created in the same template previously, with the symbolic name of sqlServer.
endIpAddress: '0.0.0.0': This, along with the startIpAddress, must be set to all 0s, which is what Azure looks for when trying to see if another Azure resource has access to the server. This “range” is special, reserved for this situation.
startIpAddress: '0.0.0.0': This, along with the endIpAddress, must be set to all 0s, which is what Azure looks for when trying to see if another Azure resource has access to the server. This “range” is special, reserved for this situation.
Then you can also specify any and all Azure SQL Databases you want to be on your server in the template if you would like and then deploy the template. After the template has been deployed, navigate to your new server in the Azure Portal and review the networking settings to see that the “Allow Azure services and resources to access this server” box is successfully checked.
Summary
If you’ve been wondering how you can get the one box on the Azure SQL Server Networking page in the portal checked by using a Bicep template, it’s as simple as adding a firewall rule for the server with a start and end IP address of all 0s. This special firewall rule is used to indicate to Azure that a given resource is available to access through other Azure resources.
Let me know in the comments below if this tip helped you or if you have any questions regarding setting up firewall rules for an Azure SQL Server using Bicep.
Continuing on in my series about doing Infrastructure as Code (IaC) using Bicep templates in the Azure environment, today’s post will cover how to create an Azure SQL Server and Azure SQL Database with a deployable Bicep template. If you haven’t seen it yet, also check out my previous posts about creating an Infrastructure as a Service (IaaS) virtual machine with a Bicep template and using a Custom Script Extension with that type of template.
If you would like to see the Bicep template I will be covering in today’s post in full, instead of piecing it together from this post, you can find it on my GitHub.
What the template does
This template creates 3 different resources, which are the ones my team wants to spin up any time we have a new project of a certain type. The resources it creates are: Azure SQL Server, Azure SQL Database, and a Key Vault.
The Azure SQL Server and Azure SQL Database are the fully cloud, Platform as a Service (PaaS) resources for SQL Server. These resources make it very easy to create a new server and any number of databases on that server without having to manage the infrastructure yourself and to only pay for what you need to use.
The Key Vault is a resource type that allows you to store any secrets, certificates, or keys in a secure location which is accessible to other privileged resources in your subscription (like a pipeline executing code). This resource would be optional for others who are only looking to create the server and database, it’s just something I’ve included because I have it in my production template for my team.
In addition to those 3 resources, there are a few others created in my Bicep template that I will be covering in-depth in future posts to keep things shorter here, so you’ll only see those in my GitHub repo for now.
How to know what configurations to choose
When I first started writing this template, I had no idea what configuration values to choose. When looking at the Microsoft documentation for the resource type in Bicep, it seemed like there were endless possibilities for what I could choose to configure the resources. That was overwhelming to me at first. I then had the idea that I would compare the list of possible values in the documentation for the resource type to the settings of an existing resource with settings similar to what I wanted for my new machine.
That effort originally started with me looking at the normal portal view of the resource, but I didn’t stick with that for long. I quickly realized that the portal view of settings doesn’t show a lot of values that I was looking for. But I figured out that you can view all the setup information for a given resource in the portal in a JSON format, which is very similar to the Bicep formatting I was needing. I believe this JSON document is likely what would be used by Azure Resource Manager (ARM) to create the same resource, which is why it’s available for every resource in the portal.
To view the JSON version of the resource settings, navigate to the resource in the portal, then near the top right corner of the Overview page, you will have a link to “JSON View”.
Screenshot showing where you can find the JSON view of a resource definition in the Azure Portal
When you open that pane, you will see something like the following, with all pertinent details of the resource, which you can then use to help you create your Bicep template.
Screenshot showing the JSON definition for an Azure SQL Server resource
Creating the template
Parameters
When creating a new Bicep template, the first thing you’ll need to decide (apart from what specific resources you need to create) are what parameters you will need to input into the template, which will usually be fed in from a pipeline which deploys the template. For my template, I created the following parameters:
serverName (string): The name you want to give to the Azure SQL Server that the template is going to deploy. You may want to add a minLength and maxLength setting to this parameter is you are worried your template deployers are not going to choose names that are within the length requirements Azure requires.
databaseName (string): The name you want to give to the Azure SQL Database that the template is going to deploy. You may want to add a minLength and maxLength setting to this parameter is you are worried your template deployers are not going to choose names that are within the length requirements Azure requires.
keyVaultName (string): The name you want to give to the Azure Key Vault that the template is going to deploy.
location (string): The region you want the resources deployed in. If you are always going to want to use the same region, you can change this to a variable instead. I have set a default for this of “westus2”.
saLoginName (string): The login name that you want to have created for the system admin (SA) user for the SQL Server. When you create an Azure SQL Server resource, you are required to provide an SA, so I am setting that value by passing it in as a parameter to the template at runtime.
saLoginPassword (secure string): The password you want to give to the SA login the server will be created with.
Variables
For the three resources I’m discussing the creation of in this post, there are no variables needed.
Resource: Azure SQL Server
Once you have your parameters and variables defined for the template, the next step is to start defining the main resources you want to create. Depending on the settings and configurations you need for your own SQL Server, you may need to define this resource differently. In my template, I have the following definition for the Azure SQL Server resource:
For the name of the server, I pass in the parameter “serverName”. Then, under the properties object, I define the required elements of the resource, which are:
administratorLogin: set to the value of the parameter saLoginName
administratorLoginPassword: set to the value of the parameter saLoginPassword
publicNetworkAccess: I have set this to “enabled” so that we’re able to access this resource through the public internet, which is required for normal usage and adminstration of the server and databases after they’re created.
adminstrators: This is a more complicated setting that is its own object. This “administrator” setting is the Microsoft Entra Admin for the server, which is essentially an Entra user or group that becomes the super user of the server with complete control to do whatever they want on the server and databases on it. I set this to an Entra group my team uses for ourselves by setting the principalType to “Group” and then giving the name of the group for login, and then the ID value and tenant ID for our group. You can find the “sid” value as the Object ID value for the group or user in Entra in the Azure Portal. And you can also find your Tenant ID through the portal as well.
restrictOutboundNetworkAccess: I have set this to “disabled” because we want the server and databases on it to be able to send data outside of themselves so that we can use them like normal.
Resource: Azure SQL Database
Depending on the settings and configurations you need for your own SQL Database, you may need to define this resource differently. In my template, I have the following definition for the Azure SQL Database resource:
For a SQL Database resource definition in Bicep, it’s really important to specify the parent value so that Bicep and Azure Resource Manager (ARM) know what server the database should be deployed onto and that the server resource should be created before the database resource. For my template, I made sure to specify the “symbolic name” of the Azure SQL Server resource I created previously in the template, which is “sqlServer”. After specifying the parent, you will also need to specify the name of the resource, which I used a parameter for, and then the region the resource should be deployed in, which I also used a parameter for.
The next thing you’ll need to specify is the sku of the database, which is the type settings for the database which dictate price, performance, and storage. In this case, I have chosen to make a database in the General Purpose tier, which is a little cheaper, and then to give it 2 CPUs. I think the easiest way to find the specifications for this section is to review an existing resource in your environment and see what values it is set to, or refer to Microsoft documentation.
Once you’ve set up the sku information, you’ll then need to specify the general properties of the SQL Database through the properties object in the resource definition. For mine, I set the following:
collation: This is the standard collation type you want your database to be setup with. I have chosen the default for North America/US, which will use English and then be case-insensitive and accent-sensitive.
maxSizeBytes: This is the number of bytes you want to be able to store in your database. That number I have set equates to 34 GB approximately, which I believe is the default.
catalogCollation: Same as the normal collation listed above
zoneRedundant: I’ve chosen not to make my database zone redundant, because I want it to be as cheap as possible and won’t be severely impacted if the entire Availability Zone goes down.
licenseType: I chose “LicenseIncluded” which means we will pay for the license and database as we use it, we don’t have an external license key to provide
readScale: I elected to not have the database setup for scaling for reads
requestedBackupStorageRedundancy: I selected “Geo”, which means we will have some redundancy, but not much. This is the standard my team set for this type of scenario.
isLedgerOn: I do not want this to be a ledger database so I’ve set this value to “false”.
availabilityZone: I’ve set as “NoPreference”
Resource: Key Vault
While most of you probably aren’t here for learning how to create a Key Vault with a Bicep template, I thought I would include it just because I have it in my production template. As with every other type of resource, you may want to change the below definition to match your own needs.
Just like with the other resources defined above, I set the name of the resource and the region it should be deployed in using parameters. And similarly to the definition of the SQL Database resource, for the Key Vault definition I also needed to define a sku, which is the pricing model for the resource. I chose Standard, and I really don’t know what other options there may be since this is such a simple type of resource.
The tenantId value is the ID for my tenant resource, so the template knows where to deploy the Key Vault. For networkAcls, I chose to not setup any bypass and to “Allow” traffic by default. Then the rest of the values are ones that seem self-explanatory to me and I believe are set to the defaults for all Key Vaults.
Summary
Creating an Azure SQL Server and an Azure SQL Database through Bicep is a fairly quick and simple process, much simpler than defining an IaaS VM like in my previous posts. That is due to the fact that the SQL Server and SQL Database are both fully-cloud resources, they’re not something you are able to access the infrastructure for. Since Azure manages the infrastructure, it is much easier for them to create these resources from a template.
After a previous in-depth post about creating a Bicep template to deploy an Azure Infrastructure as a Service (IaaS) virtual machine, which included the use of a Custom Script Extension (CSE), I want to go more into depth about some of the trials I went through while trying to get the Custom Script Extension to work. Troubleshooting it wasn’t the easiest thing in the world, since Azure doesn’t give much feedback at all from the failure to deploy that resource from a Bicep template, so I am hoping I can give anyone else struggling to implement the Custom Script Extension resource to run a PowerShell script after deploying an Azure resource some assistance with their process.
This post only has the code for CSE itself, and not the rest of the template I use it in. If you would like to see the full template, please view it on my GitHub.
In the Bicep world of infrastructure as code templates in Azure, there is a resource type that is called a “Custom Script Extension” which isn’t so much a “resource” as you would normally expect in a Bicep template. Normally, a “resource” is what it sounds like: an Azure resource you want to create, like a server, network security group, database, and even things like role assignments. But for the Custom Script Extension, it’s a method of running a PowerShell script on a Virtual Machine (VM) resource after it has been deployed, to install software or do other necessary setup on the machine.
My use case for a Custom Script Extension
The reason why I use a Custom Script Extension in my Bicep template when I create an Azure IaaS VM is so that I can initialize the disks related to the VM so they can actually be seen and used like you would expect. For some reason, when you create an Azure VM with a Bicep template, it does not automatically join the created disks to the machine. Due to this, when you first log in to the machine after it has been created, you won’t see the disks in the File Explorer like you would expect. Thankfully, PowerShell and the Custom Script Extension allow me to initialize those disks and name them what we normally want them to be named without having to login to the server and do it manually myself.
I originally had my PowerShell (PS) script set to also join the machine to our corporate domain after it was created, but I recently removed that part because it would not work with the protectedSettings section of the extension description, which you’ll see below. If you want more details about why I couldn’t get this to work and thus had to remove the most problematic sections from my PS script, keep reading.
My setup for a Custom Script Extension
The following is the Bicep resource script I used as part of my wider IaaS VM creation script to setup the VM after it is deployed.
A quick explanation of that definition is that it’s creating a resource of an extension type for VMs, and it’s dependent upon the VM which I specify further up in the full Bicep template. The script extension is set to execute a PowerShell command so that I can run a file called SetUpWindowsVM.ps1, which is downloaded by the script runner from the storage account location specified in a variable called initializeDiskScript. There are two different sections of “settings that you can specify: a normal settings section whose values will be output to the log after deployment, and then a section of “protected settings”, whose values do not get output to the log after deployment.
How the Custom Script Extension Works
After the Bicep template has created the VM, it will then set about running the specified script file I designated in my CSE resource definition. The first step to do that is to download the file from the specified fileUris location, which for me is an Azure Storage Account. The extension is able to connect to that Storage Account, since I provided the name and access key in the protectedSettings, and then download the file from there onto the local machine. The general location it’s downloaded to is: “C:\Packages\Plugins\Microsoft.Compute.CustomScriptExtension\1.*\Downloads\<n>”, where “1.*” is the version of Bicep you’re using and the “<n>” is a seemingly random integer value that the extension picks. For me, that was always “0”. After the file is downloaded, the CSE handler tries to execute the commandToExecute that you specified in your Bicep template. Since the PowerShell file will be downloaded locally to the area that CSE expects to use it from, you do not need to specify the full path to the file location, you can use the relative path formatting.
If you’re having issues with the CSE, like I was, you can get insight into what happened when the CSE ran by viewing the logs in this location: “C:\WindowsAzure\Logs\Plugins\Microsoft.Compute.CustomScriptExtension”. For more on that, see section below.
Issues with the Custom Script Extension
As of the time this was posted, I’ve been going back and forth with Microsoft support for several weeks to figure out how I could possibly use the commandToExecute specification in the protectedSettings object of the resource definition, and I have not yet resolved the error while working with them. The issue I am having is that the PowerShell script that I actually want to run includes the usage of a parameter containing a password, so I should use the protectedSettings to pass in the command with the password parameter so that the password is not output to the deployment log in plain text after the template is deployed. However, if I put the commandToExecute into the protected settings, nothing seems to happen and the script is not executed. If I put the same exact command into the normal settings, the script completes successfully yet my password it unsecurely output to the log, which I do not want.
Since I haven’t been able to resolve this problem, even with the help of Microsoft support, I have updated my PowerShell script to remove the section that joins the machine to the domain, which removes the need for me to pass through a password, so I can use the normal settings section to successfully call the commandToExecute. I am going to continue working with Microsoft support to see if we can finally come to a resolution, but I didn’t want to keep waiting on this post. If I ever fix the problem, I will update here.
Troubleshooting help
As I mentioned in the section above, you may run into random issues with the Custom Script Extension of Bicep (CSE) if you include it in your templates. Overall, I think it is still worth using this resource type, but you do need to be armed with some knowledge to help yourself as much as possible. These are the things I found useful when troubleshooting different issues with the CSE.
You will need to delete your VM and redeploy it so many times while troubleshooting issues, so be ready for that. Thankfully, deleting a VM and all its associated resources through the Azure Portal has gotten a little easier recently, so that will save you a little time.
If you are unsure whether the problem is with your PowerShell script or your Bicep template, create a test version of your VM as it would be created by your template (or run the template to create the VM then log on to that) and manually run the same PowerShell script on the machine. If it runs when you execute it manually, then the issue is not the PowerShell script but with the CSE.
Do not create the CSE nested under the resource creation specification for the VM, list it as its own separate resource definition, like I have in my template. It’s much harder to understand what’s happening if you put it as a nested resource, and then you can’t troubleshoot it on its own because the entire VM creation will fail if the CSE fails.
Make sure you specify the dependsOn property in the CSE resource definition, or else it will likely get deployed out of order. Bicep is supposed to be smart enough to know that some things should be deployed before others, but it doesn’t seem to understand order of operations for the CSE.
To view the logs of the CSE after it’s been deployed to get a sense of what was happening, you can see that by going to the Deployments section of the resource group you deployed the template into. Open the specific deployment you created for your template, then click on the link for the CSE resource deployment.
Screenshot showing the deployment overview where you can find more details on the CSE resource deployment
Check to make sure the file downloaded onto the local machine by the CSE handler is the correct file. It would be in this general location on the machine: “C:\Packages\Plugins\Microsoft.Compute.CustomScriptExtension\1.*\Downloads\”. I never saw it be wrong, but it doesn’t hurt to double-check.
Check the execution logs of the CSE after it runs on the machine. You can find all logs in this location on the machine after it’s been created and the CSE executed: “C:\WindowsAzure\Logs\Plugins\Microsoft.Compute.CustomScriptExtension”. The most useful log to me was the one called “CustomScriptHandler”, since that shows the exact steps the CSE handler ran when it executed your script (or tried to execute it).
Set your PowerShell script to generate a log when it runs, because that will either tell you what happened and went wrong in PowerShell, or it will not be created at all like in my situation, showing that the CSE is the problem.
Review this Microsoft document for other troubleshooting information about the CSE.
Mostly, you will need a lot of time and patience to properly troubleshoot issues when the Custom Script Extension goes wrong.
Alternatives to this resource type
While I haven’t used this yet, there is an alternative resource type availabe to use instead of the Custom Script Extension (CSE), and it’s called RunCommand. If you would like to read more about this resource type to see if it would be a better fit for you, there is a great Microsoft blog about it.
Summary
While I may have gotten very frustrated using the Custom Script Extension resource type in Bicep recently, I still think it’s a very useful feature that could save you a lot of manual work in setting up a virtual machine, if you can get it to run the way you need. If you’ve also run into issues with this resource type, I would love to hear about them in the comments below! Otherwise, I will keep you all updated if I find a resolution to my specific issue after posting this.
I am starting a multi-week series of posts covering the topic of Bicep, Microsoft’s Infrastructure as Code scripting language, since I have been working with this tool heavily for the past several months. You may wonder why a database developer is using Infrastructure as Code, since DevOps engineers typically do that. However, I decided to learn Bicep as a proof of concept for my team since we repeatedly find ourselves creating the same types of resources for our projects. We would like to reduce the amount of time and effort we put into creating our infrastructure so we can spend more time developing databases and pipelines to get our users the things they care about.
Stay tuned in the coming weeks for more details on how I have started integrating Bicep templates into my database development lifecycle.
Bicep is a declarative scripting language created by Microsoft as a successor to and addition on top of their existing Azure Resource Manager (ARM) templates. (Yes, the new templating script language is called Bicep because the original was called ARM.) Bicep is a scripting language that allows you to easily and quickly write declarative scripts to define how you want Azure resources to be created. These scripts, called templates, are used for Infrastructure as Code, which means you are generating your infrastructure of cloud objects using code instead of provisioning them manually through a portal. The main competitor for Azure Bicep is Terraform, which is a multi-platform tool that essentially does the same thing. But Bicep is developed by Microsoft to work only with the Azure cloud, so it is better suited for deploying Azure resources than Terraform is.
After you have created your Bicep script defining the resources you want to deploy to your Azure cloud environment, you deploy that template either manually or through a pipeline, and Azure gets to work creating the specified resources. In the background, as the deployment begins, the first thing Azure Resource Manager does is convert the Bicep script into the traditional JSON script used by ARM templates, so that it can understand and process your requests, since Bicep is essentially a nicer skin built for ARM. Once the template has been converted to JSON, Azure Resource Manager starts parsing all resource requests and sending those to the correct ARM APIs to create those resources.
If your template is set up correctly, all your requested resources will deploy to your cloud environment, and the time it takes to deploy depends on the resources you are creating.
Use cases for Bicep
Bicep templates are great for any Azure cloud users who find themselves creating the same types of resources over and over again, or for anyone who wants to make their environment re-deployable. For example, if you need to recreate your development environment from production after each deployment cycle, you could write Bicep templates to do that work for you. Essentially, if you are doing resource deployment on a regular interval and you would like to stop doing all that work manually, to reduce human error or to make the process more efficient, Bicep may be the tool for you.
What I use Bicep for
I started using Bicep as a proof of concept for my team to determine if it would be suitable for our needs for creating Azure Infrastructure as a Service (IaaS) Windows servers which we install SQL Server on. As of the time I am writing this, I still haven’t figured out 100% how to generate those servers exactly as we need them through Bicep, but I’ve gotten a good way through writing a template to provision what we need. I have proven that Bicep will work for that, but there are some final technical needs, such as using a custom virtual machine (VM) image with SQL Server already installed with our required settings, which I haven’t quite figured out yet.
Another use case that I have completed a proof of concept for is to generate all required minimum resources for our Azure-based AI chatbots with one template, which has already been immensely useful for me and my team. Creating fully cloud-based resources with Bicep has proven to be much easier to accomplish than creating the IaaS servers I started with, because all specifications and settings for those resources are managed by Azure, so Bicep handles them easily. It took me only a single afternoon to write my AI chatbot backend Bicep template, whereas it took me over a week to get my IaaS Bicep template partially working.
This new AI backend template creates an Azure SQL Server, Azure SQL Database, firewall rules, key vault, key vault role assignment, storage account, auditing on the SQL Server, and a Search Service for managing the document indexes used by the bots we’re developing for our business users. When I was doing this all manually, it would take me about an hour to work through the portal and deploy each of these resources. But now that I’ve turned it all into a Bicep template, it takes only a few minutes to deploy it all.
My thoughts on Bicep
Created using icon from Freepik on FlatIcon.com
Overall, I think Bicep is a cool and useful tool to use to create your Azure resources using a single script, which can be deployed over and over again. It really speeds up the creation of resources and reduces the chance of manual error while clicking through the portal setup of the same objects. There are several flaws with how it operates, such as not always understanding the hierarchy and order of deployment required for related resources, like it claims to do easily, but that isn’t an insurmountable challenge.
As with most scripting or programming languages, I wish there was more and better documentation from Microsoft to help us newbies figure things out faster, but it’s not impossible to learn. I would recommend that anyone who is tired of manually deploying the same or similar Azure resources repeatedly look into using Bicep to speed up their infrastructure work.
Best resources for learning Bicep
I learned most of what I know about Bicep from a Microsoft Learn workshop that is free online. The first learning path I worked through was the Fundamentals of Bicep, which I would recommend for any developer who is new to Bicep. After I completed that one to get a general understanding of writing Bicep, I then completed a few separate modules from the intermediate and advanced Bicep learning paths. Then there were many things I just had to learn on my own through other resources outside of Microsoft or through my own trial and error.
Summary
Bicep is a great tool that all types of developers working in the Azure cloud can likely use to speed up their development lifecycles. If you’re interested in learning more about Bicep, stay tuned for the next few weeks as I take a deeper dive into how Bicep works using detailed real-world examples.
One of the easiest ways to duplicate SQL Server database objects from one database to another–such as in a situation where you want to copy the structure of a production database down to a development database to help with testing and development–is to create a file called a DACPAC from the database you want to copy. With SQL Server, there are two ways to copy a database without creating and restoring a full database backup: 1) Create a full copy of the database structure and data by exporting to a BACPAC file, or 2) Create a copy of only the database structure by exporting to a DACPAC file.
This post will cover how you can import a previously generated DACPAC file onto an Azure SQL Database to duplicate the structure of another database with little effort on your part. It specifically covers copying an existing Azure SQL Database to another Azure SQL Database. This process may work with on-prem or managed instances of SQL Server, but I haven’t tested that yet.
Have an Azure SQL Server or Database to load the DACPAC to
Note: The source and target databases can be on the same Azure SQL Server or on different ones.
What are DACPACs and BACPACS?
There have already been many blog posts written on the subject of the difference between DACPACs and BACPACs, so I will refrain from trying to duplicate that information. Essentially, a DACPAC is an export of the schema/structure of a database, including the database users and logins. A BACPAC is similar to the DACPAC except that it contains the data from the database as well. If you would like more detailed information about both of these types of files, I recommend reading this post from SQLShack.
Then if you’re like me and are now wondering what the difference is between a BACPAC file and a full database backup (BAK file), I found this resource on SQL Server Central that explained the difference well.
Why use a DACPAC?
As I mentioned in the introduction above, the primary use case for making and importing a DACPAC file of your database is to quickly copy all database objects–including tables, procedures, functions, and even users–to a new version of the database, such as when trying to create a new dev version of an existing production database. That was my exact use case and what triggered me to write this post. I got tired of manually copying tables, procedures, and functions from one database to another whenever we needed a new version of a database.
How to Create a DACPAC File
When you have a source database that you would like to copy over to a new database, without the data, you should create a DACPAC from SQL Server using a “Task” on the database. To do this, right-click on the database name in the Object Explorer, select “Tasks” then select “Extract Data-Tier Application”. This will open a wizard for you to create a DACPAC file of the selected database.
Choose to “Extract Data-tier Application” from the context menu of the Database in the Object Explorer
If instead, you click on “Export Data-Tier Application”, you will be brought to a wizard for creating a BACPAC file, which is not what we intend to do during this process, so make sure you choose the option to Extract a data-tier application.
After selecting that option, you will see a screen like the following. Review the introduction page and make sure it refers to creating a DAC package (see screenshot below, underlined in red) and not a BAC package.
The introduction page of the “Extract Data-tier Application” wizard, confirm that it is going to extract to a DAC package, not a BACPAC.
Step through the wizard, provide a name for the “Application” (database name), and choose where you want the file to be downloaded to on your local machine.
The DACPAC extraction properties page of the “Extract Data-tier Application” wizard
Once you have reviewed the settings for your file, click “Next” until the creation process starts. Once that has been completed, you will have successfully created a DACPAC file which is stored in the location that you chose.
Using a DACPAC to Format an Azure SQL Database
There are two different ways that you can deploy a DACPAC to an Azure SQL Server. The first way is to deploy the DACPAC to create a database, the second is to update an existing database with the DACPAC.
Overview of the Steps
Choose whether you want to create a new database with the DACPAC or update/format an existing database
If choosing to create a new database:
Right-click on the “Databases” folder in the Object Explorer of SSMS, choose “Deploy Data-tier Application”
Specify the DACPAC you want to use and the name of the database you want to create
Run the wizard which will create a database with default settings
If choosing to use an existing database, whether blank or already in use:
Right-click on the database, select “Tasks” then “Upgrade Data-tier application”
Specify the DACPAC file you want to use
Review the changes the wizard identifies between the DACPAC and the target database
Run the wizard which will update the target database with all changes necessary from the DACPAC
Deploying a New Azure SQL Database using a DACPAC
If you want to make your new Azure SQL Database on an existing Azure SQL Server and don’t want to do that through the portal, you can create a new database on the server by “deploying” a DACPAC to create a new database.
The main downside to this method of using a DACPAC is that the database will be created with the default setup, which for me is a General Purpose standard database with 2 vCores. That level of database may be pricy for some people, or it could be underperforming for others, depending on your needs. Due to that downside, that is not the method I chose to use my DACPAC with, but I still researched it so I could understand both ways. If you want to know how I used a DACPAC, skip down to the section for “upgrading” with a DACPAC below.
Deploying a Data-Tier Application
To create a new database from a DACPAC, you first need to connect to your target server through SSMS in the Object Explorer. Once you have connected, right-click on the “Databases” folder and choose “Deploy Data-Tier Application” from the list.
Choose to “Deploy Data-tier Application” from the context menu of the Databases folder in the Object Explorer of SSMS
This will open a wizard that is very similar to the one for “upgrading” a data-tier application (discussed below). On the overview page of the wizard, you can confirm that you are going to deploy a DACPAC and not a BACPAC.
The introduction page of the “Deploy Data-tier Application” wizard
The first thing you will need to do in this wizard is to choose the DACPAC file you previously created.
The page of the “Deploy Data-tier Application” wizard where you choose the DACPAC file you want to deploy from
Click Next to move to the following page, where you will choose the name of the database you will deploy.
The page of the “Deploy Data-tier Application” wizard where you choose the name of the new database you want to deploy from the DACPAC
Click Next to move to the Summary page and review the process that the wizard is about to execute to ensure everything is set the way you want. Click Next again to deploy the DACPAC and create a new database from that file.
Once that process completes, you can close the wizard and then check on your database through the Object Explorer to see that you now have a new database with the same objects as the source database. At this point, you will also be able to see the newly created database in the Azure Portal under that SQL Server, where you can make any changes you would like to the default setup chosen by the deployment.
Updating or Formatting an Existing Azure SQL Database with a DACPAC
This method of “updating” an existing Azure SQL Database is helpful if you already have a database that you want to align with another, or if you have already created a blank database meeting your standards that you want to quickly deploy database objects to. In the previous section, the method of “Deploy a Data-Tier Application” creates a new database from the specified DACPAC, but that database is created with the default Azure SQL Database settings, which is “General Purpose: Standard-series (Gen5), 2 vCores”. That database setup is estimated to cost (in our setup) $373 per month.
If that default setup for a database doesn’t work for you or you don’t want to fix that after the database has been created, you can use this second method of “upgrading” your existing database with the DACPAC. Before you can complete this method, you first need to create a blank database on your Azure SQL Server, through whichever method you prefer. Or use any other existing Azure SQL Database that you are fine with overwriting with the DACPAC.
Upgrading a Data-Tier Application
Connect to your target database in SSMS through the Object Explorer. When you are ready to import the source database objects onto this new database, right-click on the empty database then select “Tasks” then “Upgrade Data-tier Application”.
Choose to “Upgrade Data-tier Application” from the context menu of the existing database you want to deploy the DACPAC to
This will open another wizard that will walk you through the process of “upgrading” your chosen database with the DAC package of another database.
The introduction page of the “Upgrade Data-tier Application” wizard
On the second page of the wizard, you will be prompted to select the DAC package you would like to use for the upgrade. Browse to the DACPAC file you created earlier, then click Next.
The page of the “Upgrade Data-tier Application” wizard where you choose the DACPAC file you want to deploy to the database
After you click Next, the system will start detecting changes between the target database and the DACPAC you selected, which may take a few minutes.
The page of the “Upgrade Data-tier Application” wizard where the wizard checks for differences between the DACPAC and the target database
If you have already imported the DACPAC before or have anything on your target database that is different from the source, you will receive a warning like this:
The page of the “Upgrade Data-tier Application” wizard where you confirm that you are okay with proceeding despite the risk of data loss from deploying the DACPAC
I got that warning because I had already imported the DACPAC so I no longer had a blank database. In this situation, you are required to check the box that you’re okay with overwriting the existing structure by continuing. If it’s your first time attempting to import the DACPAC onto a blank database, you should instead receive a green check box on this page.
On the next page, choose whether or not you want to roll back in the event of a failure during the “upgrade”. I chose to not have it roll back since this was going to be a new dev database that I could tweak, fix, or simply recreate if something went wrong. You may want to choose differently.
The page of the “Upgrade Data-tier Application” wizard where you choose the rollback option you want the wizard to use when deploying the DACPAC
The final step will be for the system and you to review the “upgrade” plan. You will see a list of all changes that will be applied to the database on the “Review Upgrade Plan” page. If you’re happy with the proposed list of changes, click Next until the upgrade process starts and the DACPAC gets applied. Once that process completes, you can close the wizard and then check on your database through the Object Explorer to see that you now have database objects deployed to the database that you didn’t before.
The page of the “Upgrade Data-tier Application” wizard where you review all the changes that will be applied based on the DACPAC
Can’t I just import the DACPAC from the Azure Portal?
This was the first thing I tried when trying to import my DACPAC into an Azure SQL Server. I uploaded my DACPAC file to a storage account, then clicked “Import” on the SQL Server page in the Portal. This started a new deployment where the SQL Database was created but then the deployment of the DACPAC to the database failed so I was left with an empty database on my server. The error I received when the deployment failed was related to not specifying a BACPAC file for the import process, so it expects that file type instead of a DACPAC.
If you can figure out how to import a new database to a SQL Server using the Portal, I would love to hear about it in the comments below.
Summary
If you, like me, were confused about how you could import a DACPAC file into a database on an Azure SQL Server (because the Microsoft documentation for this isn’t great), I hope these instructions help you with your import. It is as simple as creating the DACPAC file and then using a wizard to “upgrade” or “deploy” your database using the DACPAC.
I have done more database creation in the past couple weeks than I had done in the previous year combined, all because my team is working on making several different AI chat bots for our business users and we are being very agile about our development process. At first we had separate Azure SQL Databases for each chat bot to use for its backend, but then I decided it would be more prudent, cost effective, and easier to manage if we combined the three related bots into a single database. This meant that I needed to move data from three disparate databases into one single database, and I wanted to do it easily without any extra tools.
How I decided to move the data between the databases was to quickly export to a flat file then import that flat file onto the new database–since I only had a few hundred rows to move from each source database. As far as I am aware, this should work with all modern types of SQL Server (on-prem, IaaS, Azure SQL Database). Read on to learn how to follow the same process I did.
On your source database in SSMS, run a SELECT query to retrieve all the data you want to copy over to your target database. Once you have your result set, select all records, right-click on them, then choose “Save Results As…” from the menu.
You can export your query results using “Save Results As…” from the right-click dialog
That will open the normal “Save As” dialog, and you can choose where to save your results. Save the file to your computer.
Once the file has been saved, I recommend opening the CSV using Notepad++ or similar tool to review the formatting of the data. There were a few times when I went through this process that the columns names did not get exported to the flat file, and instead the columns were generically named “Column1”, “Column2”, and so on (see final screenshot below for what this would look like when trying to import). Chances are that you won’t run into this issue, but it doesn’t hurt to check. If for some reason your file doesn’t have the correct column names that you expect, simply export the query results to a file again and it should fix the issue.
Importing the data to the target
To import the data onto your target database, connect to it in SSMS as well, right-click on the database name in the Object Explorer, then choose “Tasks” > “Import Flat File”. This will open a wizard to help you import the data.
Import a flat file using the Task “Import Flat File”, which will open a wizard tool for you
In the dialog that opens, browse to the file that you previously exported from the other database. Once you select that file, a table name is generated that the data will be imported to. You can change the name of that table if you wish. Also, choose the schema that table will go into. This process creates a new table from the imported data, so make sure you don’t use an existing table name.
Specify the CSV file you created during the export process and the name of the new table you want to load the data into
Click “Next” to move to the next page, where you can preview the data that will be imported. If the data looks good to you, click “Next” to move to the “Modify Columns” page where you can change some settings on the table columns if you would like. I chose to “Allow Nulls” for all columns, just to make things easier for myself in this import process. I plan on deleting this table after I copy the data from it into the final target table, so it doesn’t matter to me if every column allows nulls.
This screenshot shows you what can happen if the export process goes a little wonky–the column names from the source may not be put into the CSV file and thus only show as generic names in the Import wizard. From this menu, though, you can modify the properties of the columns in the new table that is going to be created for the import.
Keep clicking “Next” to move through the rest of the pages, review the process before you opt to import, then import the data. Once the import wizard has completed its work, you will now have a new table in your database, essentially a staging table.
To get the data from your staging table into the final target table in your database, you can use an INSERT INTO tablename SELECT... query. This step also allows you to modify the data before moving it into the target table if you need to. In my case, I needed to add an extra column to the data that the source table didn’t have but the target requires, so I hard-coded the value for that column into my SELECT statement so it would be added to the final table.
Cleanup
After you have moved your data into its final destination, make sure to clean up the temporary new table that you created during the import wizard process, to keep things as neat as possible in your database.
Summary
Using this method to copy data between two Azure SQL Databases may be considered a bit lazy, but I truly think it’s the most efficient way to get data from point A to point B. Sure, I could have made a pipeline or used some other system to move the data around, but I only had a few hundred records to copy over so this method worked great for me. Let me know if this method saved you time as well!
Have you ever wondered what the normal work tasks of a database developer/integration engineer looks like? If you have, then this is the post for you. This is a new series of posts where I simply give an overview of what I accomplished each week, giving insight into what life as a database developer looks like for those who might be curious. I also want to do these reviews for my own records and edification, because it’s always good to keep track of the things you accomplish at your job. This post is going to review the week of September 23 – September 27, 2024.
One part of my job is interacting with various software vendors that we buy products from. I’m currently on a longer-term project where we are doing a piece-by-piece upgrades to one application that our legal department uses, so this week I met with the vendor’s development team to get an overview of the next step of this upgrade process.
One of the joys of working with vendors is sometimes you go into these meetings thinking the purpose is completely different than what it ends up being, then you have to rethink everything you planned for the meeting on the fly. That was how this meeting went. I wasn’t expecting that I would be the one taking lead on driving the topic by asking questions we needed answers to on our side before we could move forward with the next step of the upgrade. I thought the meeting was going to be the vendor giving an overview of the product for my own learning, not me driving the conversation. As someone newer to the company, this change was a little scary, but I handled it well and got good information for our team from the vendor.
Updating Security Certificates
This part of technology is something I’m still not very familiar with but had to dive into this week. My team had an ETL that broke because it could no longer communicate with our customer’s software due to an outdated security certificate. To fix this broken ETL, I had to locate the correct updated certificate and put that on our ETL server and remove the old one. After making such a small change, things were back to working as they normally do. This problem was a good learning opportunity and not nearly as difficult as I was expecting it would be.
Emergency Database Refresh
I got to experience my first emergency refresh request in our Oracle database environment this week because something went wrong with one of our production databases and the app development team wanted to refresh the database into the lowers as quickly as possible to start troubleshooting. I think fate decided we should have a good day, because our Oracle cloning process into our test environments went off without any issues and finished in about an hour, which is almost a record low. We’ve ran into numerous issues in recent months with the pluggable database cloning process in Oracle, so we are very thankful that one of those problems didn’t arise when we needed this emergency refresh.
Completing Database Permissions Requests
Another piece of Oracle work I got to experience for the first time this week was updating user permissions. We received a request from one of our application developers that they needed a service account to be granted the same level of permissions that his own account had (in test) so that he could complete a task. Although the SQL needed to complete a permissions change in Oracle looks a little different from what I’m used to in SQL Server, it overall was very similar to making permissions changes with T-SQL so was easy to complete.
Attending A Women’s Leadership Conference
My favorite part of this week was getting to spend Tuesday and Wednesday at the Women & Leadership Conference by the Andrus Center at Boise State. This was my first time attending this conference, and I was able to go with the handful of other women in the IT department.
As would be expected for this type of conference, the sessions focused on building soft skills in women instead of hard technical skills like previous work conferences I’ve attended. I listened to a range of women speakers, all of whom are in various leadership roles in different fields and states across the country. I wouldn’t say I loved every session I attended, but most of them were interesting and I learned a lot of tips for managing in the work environment as a woman that I hadn’t thought of before. In the near future, I will be writing a recap with more of my learnings from the conference, if you’re interested in hearing more.
Troubleshooting a Virtual Machine (VM)/SQL Server Connection Issue
This issue was not something I had ever considered before, because I’ve never had to think about the networking setup for VMs that I’ve worked with. Normally, someone else sets up the VM and makes sure it has all the networking and firewall rules needed before we get access to the VM. However, one of my project teammates and I found out that it’s not always the case.
I am working on a different application upgrade project from the one I mentioned above, and I was in charge of setting up those SQL Servers last week. This week, the main application developer started his portion of setting up the VMs we put the SQL Servers on, and he found that he was unable to access the SQL Server from his local computer version of SQL Server Management Studio (SSMS). When he messaged me about this connection issue, I had no idea where to start troubleshooting it since I had never had to think about how SSMS connects to the SQL Server on the VM before.
I had to work with our networking team to figure out the issue, which ended up being that the local firewall on the VM was not setup to allow ingress from the two ports that SSMS/SQL Server requires for connections. The networking team wrote two commands to allow that ingress, ran the commands on all 3 servers we setup last week, and then we were able to easily connect to the new SQL Server instances from our local SSMS apps instead of having to login to the VMs directly.
Finishing and Presenting a Python Data Analysis Script
One of the things I was most pleased and excited about this week was finally getting to demo a complicated Python script I wrote to optimize customer orders based on their previous order history, given a list of input parameters from the customer. I have spent months working on this script, going back and forth with the business users about what should be included, adding new features when requested, and even totally reworking the algorithm when their requests got more advanced. My demo of the script had an audience of the business users as well as members of my own team, including my manager.
The demo itself went really well, the script worked exactly as I wanted it to, running in under 5 seconds to optimize the customer data and provide a recommendation for what should be stocked to fulfill those orders, which is a massive improvement from the current process that takes a week to calculate the best possible solutions. What did not go as I expected was hearing from the business users that they are unsure if the solution provided is accurate enough, so they’re afraid to move forward with my script unless I did several things that would likely lead to several more months of rework. At the end of the meeting, we had decided as a team that instead of implementing this solution I worked hard on for months, that isntead we would put their current process onto a newer and beefier server in hopes that it would run faster than the current week.
No developer wants to spend months working on something just to be told that it won’t be used due to reasons that are out of the developer’s control, but that seems to be the situation I am in now. I am staying positive about it though, because it was a great Python development learning opportunity for me when I came into the company, and I have faith that with time, the business users will come around to using the faster and more modern solution when they see that the results I produced are very close to what they already get with the current solution. I might need to do a little more tweaking to get my algorithm’s results into an acceptable range compared to the current process, but I am hoping it won’t require a full rework to do so.
Summary
Sometimes I have weeks at work where I feel like I haven’t accomplished all that much. This week was one of those. But now that I have typed out everything I did like this, I am seeing that I do a LOT of work while I’m at work, and I am proud of everything I learned and accomplished this week, even if every single work item did not go as planned.
Being a database developer or data integration engineer comes with a lot of variation in work, which you can probably see by looking at this week’s and last week’s summaries. There is always something new to learn and work on, so I’m excited I’ve had another interesting week of work and I look forward to next week being interesting as well. (Although I’m technically on vacation next week, so really I mean the following week.)
Do you have any questions about what a database developer does day-to-day that I haven’t answered yet? Let me know in the comments below!
