Now that we can create and remove an Azure Cosmos DB and databases that we can use for automation purposes, along with obtaining some information about these accounts, we’ll look at making some changes to these accounts for contexts related to performance. It’s possible that our unit and security testing or development with proof-of-concepts may face performance problems where we need to upgrade the settings of our database account. In this tip, we’ll be working with the SQL API database layer in a Cosmos database account by building on our get, create and remove automation to update its performance.

Dependencies

We will continue using the Az module in PowerShell to work with Azure Cosmos DB and begin by connecting to the database before creating a test database for manipulating performance. In the below script, we connect to our Az account, set some variables that we’ll be using to create our SQL API database, and create the database. To prevent any dialogue box in PowerShell, we use the -Force option and this script executes without asking us for confirmation. We’ll notice that we set the throughput in our properties object.

We see our database created in our Azure Cosmos DB with 1000 RUs.

We see that we specified 1000 as our RU throughput in our PowerShell script (the interface in the Azure Portal will show this under Scale and we see that it matches in the above image) and we see the range allowed in the Portal as between 400 and unlimited RUs for our Azure Cosmos DB. An RU is a request unit – this is the aggregated measure of the database requests against it for requests (reads, writes, storage, etc). If we set our RUs low, we can’t expect to run a heavy query load, but we don’t want to set it too arbitrarily high. Also, we’ll see that unlimited is not a specification we can use. As we’ll see later in an error I intentionally generate when updating the RUs, we won’t actually specify unlimited if we wanted unlimited for our performance, but 1000000 RUs. It’s important to note that RUs will affect our performance, so if we want high performance for queries, we’ll need a higher RUs specification and this will come with higher costs. This is where horizontally scaling our SQL API databases, or even scaling our Cosmos database account will help us.

Change RUs for Database

Just like we see in the Azure Portal under Scale with the positive and negative symbols, we can increase or decrease the RUs for our SQL API database in our Azure Cosmos DB. With PowerShell, we’ll use the Set-AzResource function with the appropriate parameters to update the RUs for our SQL API database. For our example, we’ll increase our RUs to 2000. Provided that it successfully passes, we’ll get a return properties object with the throughput measured in RUs that we set.

PowerShell returns the RU throughput we requested.

We confirm the throughput of our database within our Azure Cosmos DB in the Azure Portal.

As we see in the image from the Azure Portal, when we refresh our information for Scale, we see the new throughput of 2000. It should be of note that we cannot set whatever scale we want outside of what is allowed in PowerShell. For an example, let’s try to change the RUs to 750 and notice the error we get, giving us the range of limits that we have for setting the scale in our PowerShell script. The below snippet uses the same above script, but only has changes to the update object – we change the throughput to 750 RUs.

We cannot set a scale of 750 RUs for our SQL API database in our Azure Cosmos DB.

We see that the error indicates that we must use increments of 100 RUs to increase the scale. This means that we must either select 700 or 800 in this case (not 750) for our script to run successfully. We also see that we must use an RU range between 400 and 1000000, as anything below 400 or anything over 1000000 will fail. This shows that we won’t use a specification of “unlimited” in our PowerShell script if we want the highest RU specification. Notice that we get the same error if we try this in the Azure Portal:

We see the same restrictions in the Azure Portal.

Returning to our scale decrease, we’ll drop our throughput to 700 and run our script to decrease our SQL API database’s scale in our Azure Cosmos DB. As we see, the scale drops to 700 successfully since we used an increment of 100.

When to Increase or Decrease Scale Automatically

With some unit or security testing, we may require a higher scale to test – as we may see connection issues if we have a large load, or the test may exceed a time limit (if we or our software sets this limit). We can use these scripts to automate a temporary increase in scale for our SQL API database in our Azure Cosmos DB prior to testing and automate a decrease in scale following our testing. To do this, we would keep the initial RU threshold saved and increase it only when needed (testing or proof-of-concept) before returning it to its starting level.

This technique is especially effective in situations where we don’t create and remove the SQL API database account or Cosmos database account, as we can set the appropriate RUs for performance in our testing for those situations. Consider a scenario where we keep all seldom used SQL API accounts at an RU level of 400, but during intense unit and security testing we increase them to 2000 RUs. Following the test against our Azure Cosmos DB, we can return the RU level to 400 – the 2000 increase is only temporary. We may also change the scale if we want to automate a solution to testing failures, if the testing failures involves a scale issue – in other words, a test fails because of scale, we automatically increase scale, re-test, and return the scale to its normal level.

Azure Portal versus PowerShell’s Az Module

In working with Azure Cosmos DB for the creation, removal and updates to Cosmos database accounts, SQL API databases and RUs, we’ve seen that we can use the Azure Portal or we can use the PowerShell Az module. Both have advantages and depend on our context. Let’s look at some pros and cons of each approach that we’ve seen so far.

• Azure Portal. For one Cosmos database account, SQL API database account or changes, we can quickly create, remove or update settings. In addition, we get a confirmation within the Azure Portal itself and can refresh our screen to confirm our changes. For an example, if we manually updated the RUs in the Azure Portal like we do in this tip, we would see this reflected as soon as we refreshed the screen. For small environments where we have one or two Azure Cosmos DB accounts, the Azure Portal may suffice for the creation, removal and maintenance of Cosmos database accounts and SQL API databases. Also, if we seldom do proof-of-concept creations, manual account creations will suffice as well since we don’t have to worry about frequent changes to libraries
• PowerShell Az Module. For multiple Cosmos database accounts or SQL API database accounts, we may prefer automation scripts, especially when we want to clone and re-use a template, create temporarily for a unit and security test and remove when finished, or build for a proof-of-concept in a development context where we often do many proof-of-concepts. The key here is that scripting allows us to take advantage of re-use and minimize development, as we’ll generally change few parameters when creating, updating and removing resources involving Cosmos database accounts

Microsoft will update PowerShell Azure modules from time to time, so it’s worth mentioning that if we’re creating, removing or updating Azure Cosmos DBs on a small scale or a one-by-one level, it’s possible that we’ll prefer using the Azure Portal. Automation is useful in development when the second point applies to our situation in the above list.

Conclusion

With unit and security testing along with proof-of-concept development, performance is one issue we may face with these. With PowerShell’s Az module (or use of the Azure Portal), we’ve seen that we can update the RUs for the SQL API databases along with the limits of this – such as ensuring that we use the appropriate increment amounts to avoid errors when we make these changes. Along with changing the performance of our Azure Cosmos DB with the other creation, updating and removal techniques we’ve learned, we can avoid a wide range or problems in our testing or development.

Table of contents

• Author
• Recent Posts

Timothy Smith

Tim manages hundreds of SQL Server and MongoDB instances, and focuses primarily on designing the appropriate architecture for the business model.

He has spent a decade working in FinTech, along with a few years in BioTech and Energy Tech.He hosts the West Texas SQL Server Users' Group, as well as teaches courses and writes articles on SQL Server, ETL, and PowerShell.

In his free time, he is a contributor to the decentralized financial industry.

View all posts by Timothy Smith

Latest posts by Timothy Smith (see all)

• Data Masking or Altering Behavioral Information - June 26, 2020
• Security Testing with extreme data volume ranges - June 19, 2020
• SQL Server performance tuning – RESOURCE_SEMAPHORE waits - June 16, 2020