Password<\/strong> \u2013 in Team City you can save a password in a secure string, for testing purposes you can just put the password here, but NEVER save it here.<\/p>\nSo the powershell will look something like this:<\/p>\n
![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/06\/BuildingAzureDB_2.png\")
<\/p>\n
We then need to convert the password to a secure string and set the result as a parameter using the following code:<\/p>\n
![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/06\/BuildingAzureDB_3.png\")
<\/p>\n
Using these details I logged in to the azure portal using the following code:<\/p>\n
![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/06\/BuildingAzureDB_4.png\")
<\/p>\n
This uses the username passed in as the parameter, the password that we\u2019ve converted to a secure string which we have set to be a credential, and then using these credentials we\u2019ve logged in to our Azure account.<\/p>\n
We then need to pass in the detail of which resource group we want to use, which location and pass it a database name.\u00a0 We could parameterise these but in this case I haven\u2019t as my resource group and location wont change, but if I wanted to use this code for a different resource groups or locations I\u2019d definitely parameterise them.<\/p>\n
![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/06\/BuildingAzureDB_5.png\")
<\/p>\n
Then we need to check whether our database exists, we can use an Azure command for this called Get-AzureRMSQLDatabase<\/strong> and we need to pass it the ServerName, ResourceGroupName and DatabaseName.\u00a0 We need to pass this to a variable because really all we want to know is if it exists, if it does we want to delete it because, in my opinion, when you are building a test database from a source controlled environment you want to start with an empty database.\u00a0 In future builds I will be building a pre-production environment in which case we will be comparing and making changes and not starting from empty.<\/p>\nTo remove a database we use the command Remove-AzureRmSqlDatabase <\/strong>and we need to pass in the ResourceGroupName, ServerName and DatabaseName<\/p>\nThe script to check and remove the database is as follows:<\/p>\n
![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/06\/BuildingAzureDB_6.png\")
<\/p>\n
Once the database no longer exists we can create a new one.\u00a0 We have to use the New-AzureRmSqlDatabase command and pass in the ResourceGroupName, ServerName and DatabaseName and the RequestedServiceObjectiveName which is basically what tier of database do we want, I\u2019m using Basic because it\u2019s a test box and I\u2019m only interested in building the cheapest I can without compromising performance.\u00a0 However remember some functionality which works on higher tiers may not work on this as I found out.\u00a0 For example Clustered ColumnStore indexes do not work on the Basic tier so consider your functionality when setting the level you want.<\/p>\n
The code to do this is:<\/p>\n
![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/06\/BuildingAzureDB_7.png\")
<\/p>\n
So the entire script looks like this:<\/p>\n
![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/06\/BuidlingAzureDB_8.png\")
<\/p>\n
Once we\u2019ve tested this script and prove it works we need to plumb it in to TeamCity.\u00a0 However remember to remove the password, we don\u2019t want to save a password in here.\u00a0\u00a0 We could encrypt the password and save it in a file, but TeamCity handles it quite well so I\u2019ll leave it there.<\/p>\n
In TeamCity open your Build Configuration and add a build step.\u00a0<\/p>\n
The window will look like this:<\/p>\n
![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/06\/BuildingAzureDB_8.png\")
<\/p>\n
Going through this step by step:<\/p>\n
\n- Runner type<\/strong> refers to what type of script we are using and in this case it\u2019s Powershell.<\/li>\n
- Step name<\/strong> is up to you, whatever makes sense,<\/li>\n
- Execute Step<\/strong> defaults to \u201cIf all previous steps finished successfully\u201d, but you can look at the options and choose which is most appropriate. I keep with the default because I don\u2019t want it to continue if something hasn\u2019t worked properly.<\/li>\n
- Platform<\/strong> relates to the version you are using,<\/li>\n
- Format stderr output<\/strong> as: defaults to Warning but if I want it to error if there is something that doesn\u2019t work quite right so I often change this to Error.<\/li>\n
- Script<\/strong>, select file as we are running a script from a file.<\/li>\n
- Script File<\/strong> is, you will notice that I\u2019ve used a parameter and then the \\ and the file name, we\u2019ll set the parameters shortly.<\/li>\n
- Then you need to click on Expand<\/strong> and the script arguments will be displayed, you can see here I\u2019ve added in the 3 parameters that we set in the powershell script. We have to put parameter names in % and \u201c, I can\u2019t explain why I just know that we need to!!!<\/li>\n<\/ul>\n
Then we save it.<\/p>\n
Now we need to click on Parameters on the left menu to set our parameters.<\/p>\n
It will list all parameters used and there will be red text in the value box for those parameters with missing values.<\/p>\n
Populate the missing ones with the details you set in the powershell.<\/p>\n
To set the password ones, click on edit and click in here click on \u201cEdit\u201d and in display select Hidden and in Type select Password, that way you enter the password in once and then it\u2019s hidden from sight.<\/p>\n
Once these are all saved you can click on Run and Run the build, hopefully without error.<\/p>\n","protected":false},"excerpt":{"rendered":"
I\u2019m in the process of setting up a new environment using Azure SQL Databases, previously in Azure we\u2019ve just used an Azure VM with SQL Server on it.\u00a0\u00a0I\u2019m also fairly new to setting up a Continuous Integration environment and in-house we use Team City, so in this blog and probably the next few I\u2019m going……<\/p>\n","protected":false},"author":10747,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[57570],"class_list":["post-79391","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/79391","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/10747"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=79391"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/79391\/revisions"}],"predecessor-version":[{"id":79405,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/79391\/revisions\/79405"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=79391"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=79391"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=79391"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=79391"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/06\/BuildingAzureDB_1.png\")
<\/p>\n