Creating and Removing Databases with PowerShell In Azure Cosmos DB
Creating and Removing Databases with PowerShell In Azure Cosmos DB
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
SQLShack
SQL Server training EspañolCreating and Removing Databases with PowerShell In Azure Cosmos DB
May 27, 2019 by Timothy Smith Our testing or development may call for dynamic creation on the database level for Azure Cosmos DB rather than the account level. As we’ve seen with dynamically working with a Cosmos database account using PowerShell, we can create, remove, and obtain properties of the account. Identically, we can do this on the database level as well and we may use this in testing if we need the same Cosmos database account for other testing purposes. Development situations may also involve use cases where we want to test a concept and dynamically create a database within our Cosmos database account. In this tip, we’ll look at working with our Azure Cosmos database account on the database object level where we do nothing to manipulate the account itself, only add databases to the account once it’s been setup.Dependencies
In addition to using PowerShell’s Az module and connecting to Azure using the Az module, we will use an existing Cosmos database account for running tests of creating and removing a database. In the below script, we first connect – which will be required for our later scripts – and we also get a list of all our Azure Cosmos DBs. We can use this check for logic when we need to first validate the existence of a Cosmos database account, such as checking if the name of scosdb exists (or the name you use). 12345 Connect-AzAccount Out-Null $resource = "Microsoft.DocumentDb/databaseAccounts" (Get-AzResource -ResourceType $resource).Name In NoSQL contexts such as MongoDB, we can use a “use database” statement or a connection to a database with a database that doesn’t exist and create a collection that doesn’t exist from an insert without explicit creation. For NoSQL databases, this is one way to create a database and collection on the fly, but if our use case requires removing it, we may want to use the explicit creation and drop, like we’ll be doing in this tip with Azure Cosmos DB. To demonstrate this on-the-flow creation process, I run the below commands in order in a MongoDB shell session – first I check the existing databases; second, I use a database that doesn’t exist and create a collection in the database using an insert; third I check the collections and databases, and we see the collection and database now exists. 12345 show dbsuse OurNewestDatabasedb.OurNewestCollection.insert({“_id”:1})show collectionsshow dbs In some contexts, we may create collections and databases without explicit creations in NoSQL databases like Azure Cosmos DB or MongoDB This development style may be an option in some NoSQL contexts, but it may not be the option we choose in testing if we want to re-use the parameters for the explicit creation and removal of the database we create. Our approach will differ in this tip since we’ll focus on the explicit creation and removal of a database.Getting Properties of a Database
As we noticed in our check, we can call the Get-AzResource to discover if a resource exists – we searched for all Azure Cosmos DB accounts (Microsoft.DocumentDB). With this same function, we can obtain some of the properties of our existing Cosmos DB account – in this case the scosdb account. Calling the function with the parameter of ResourceType and Name will return some specific information about this account – we’ll save some of these values to variables, such as kind, location and rGroup. 12345 $resource = "Microsoft.DocumentDb/databaseAccounts"$cosmosdb = "scosdb"$rGroup = (Get-AzResource -ResourceType $resource -Name $cosmosdb).ResourceGroupName$kind = (Get-AzResource -ResourceType $resource -Name $cosmosdb).Kind$location = (Get-AzResource -ResourceType $resource -Name $cosmosdb).Location These properties can be useful if we want to create another Cosmos database account that has similar properties – such as creating one that has the same location and resource group name. We’ll also look at using properties for creating a database within the Cosmos database account.Creating a Database
If we consider the use case of a unit and security test where we want to run a test on a newly created database and remove it when finished where our Azure Cosmos DB has other unit tests running on other databases, removing the Cosmos account would cause disruptions. For this use case, we want to dynamically create a SQL API database within our Cosmos database account for our testing. The creation will carry some similarities to the account creation and with our properties that we saved in the above script from the existing account, we’ll be able to use the variables to create our database. In the below code, we create our database account with our Azure Cosmos DB and we see output returned from this call. We use the -Force parameter here to avoid the dialog that returns asking us to input yes or no, which is more appropriate for automated development contexts. If you prefer the dialog box, you can remove this parameter. 1234567891011121314 $resource = "Microsoft.DocumentDb/databaseAccounts"$type = "Microsoft.DocumentDb/databaseAccounts/apis/databases"$cosmosdb = "scosdb"$testdb = "TestDB"$api = "2015-04-08" $rGroup = (Get-AzResource -ResourceType $resource -Name $cosmosdb).ResourceGroupName $properties = @{}$properties.Add("resource", @{"id" = "$testdb"})$properties.Add("options", @{"throughput" = 1000}) New-AzResource -ResourceType $type -ApiVersion $api -ResourceGroupName $rGroup -Name "$cosmosdb/sql/$testdb" -PropertyObject $properties -Force Part of the returned output when we create our SQL API database in our Azure Cosmos DB As we see in the Azure portal, we’ve created our SQL API database with a throughput of 1000 RUs within our Cosmos database account. The Azure Portal view of our SQL API database in our Azure Cosmos DB Like our unit and security testing scenario, we can use this model to horizontally scale SQL API databases within our Cosmos database account, test proof-of-concepts that we don’t intend to keep after we prove out our concept, or other development purposes that allow us to re-use our creation scripts saving us redevelopment efforts.Removing a Database
In testing situations, we may only create a database to perform a unit or security test without keeping the database that we created. The same pattern may also be true if we have a quick proof-of-concept test where we build an Azure Cosmos DB and remove it when we’re done with our test (retaining the results of our findings). The purpose of removing the database doesn’t only eliminate possible costs by keeping something unused in our environment, it prevents other developers from using it or misunderstanding the intent. This latter point becomes an issue in organizations where developers move on and newer developers wonder what was the function of architecture and feel reluctant to remove it since the history is questionable. We never want resources we’re not using for this reason. Finally, removing a resource that we’re not using completes the cycle of our intent – if our intent is testing, the final cycle may be the removal of what we created. If we create databases for unit and security testing or proof-of-concept reasons in our Azure Cosmos DB and don’t need the Cosmos database account, we may remove it on this level. Recall that the creation and removal of the Cosmos database requires some time, so we may want to keep the account and remove individual databases within the account. This is also true if we use the Cosmos database account as a holder for databases used in testing and development. 123456789 $resource = "Microsoft.DocumentDb/databaseAccounts"$type = "Microsoft.DocumentDb/databaseAccounts/apis/databases"$cosmosdb = "scosdb"$testdb = "TestDB"$api = "2015-04-08" $rGroup = (Get-AzResource -ResourceType $resource -Name $cosmosdb).ResourceGroupName Remove-AzResource -ResourceType $type -ApiVersion $api -ResourceGroupName $rGroup -Name "$cosmosdb/sql/$testdb" -Force We confirm that our database within our Azure Cosmos DB is removed in the portal When removing our SQL API database, we don’t need to include the properties and we can see the number of parameters we’re able to re-use from our creation process. Like with the creation process, we include the -Force parameter to avoid the dialog box – a useful technique when we want an automated process building and removing our SQL API database.Conclusion
As we see with PowerShell and the Az module, we can get information about our Azure Cosmos DB account, and dynamically create and remove databases within our Cosmos database account. This can be useful in development for new concepts we want to test and validate – such as expansions to horizontal scaling – or it can be useful for unit and security tests where we create, test and remove the database following the test. Unlike creating the Cosmos database account, creating a database within the Cosmos database account in PowerShell is faster and is more appropriate if we want to retain the account while letting the database go. In addition to the dynamic creation, we can get properties we need for the account to update configuration information if required. Finally, if our situation calls for removal, we can remove the database within the Azure Cosmos DB.Table of contents
Creating and Removing Azure Cosmos DBs with PowerShell Getting and Updating Connection Information for Azure Cosmos DB Creating and Removing Databases with PowerShell In Azure Cosmos DB Increasing or Decreasing Scale for Azure Cosmos DB Creating Containers with PowerShell For Azure Cosmos DB Author Recent Posts Timothy SmithTim 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