Careers   |   Events   |   Contact   |   402.238.1399   |   contactus@deliveron.com

Deliveron
Connect with us on FacebookConnect with us on LinkedInFollow Us on Twitter

Category List


Tag List

zap (1)
Infrastructure (1)
sharepoint 2007 (2)
AAD (2)
Automated Testing (10)
MFA (1)
github (1)
planning poker (4)
ssas (2)
insiders (1)
visual studio 11 beta (2)
azure resource manager (1)
tfs 2013 (2)
team foundation server 11 beta (2)
azure mobile services (2)
asp.net-mvc-4 (2)
visual studio (8)
release management (7)
action-filters (2)
visual studio team services (1)
lab management 2010 (2)
deliveron agile delivery process (2)
tfs odata (2)
home projects (1)
scrum (8)
Java (1)
swagger (1)
rest assured (1)
pipeline (1)
artifacts (1)
automatedui (1)
performancepoint services (2)
nebraska code camp (2)
Multi-Factor Authentication (1)
test cases (2)
tfs 2010 (2)
nunit (1)
table-valued-parameters (4)
tls 1.2 (1)
tokenization (1)
alm rangers (1)
mvvm (2)
deliveron alm delivery guidance (2)
alm (9)
database publishing wizard (2)
storyboarding (2)
REST (1)
opensource (1)
powershell 2.0 (8)
Azure (14)
netstandard (2)
vsts (22)
business intelligence (2)
bundling (2)
tips (2)
web deploy (1)
web performance tests (2)
ARM Template (1)
angular 2 (3)
Visual Studio Online (6)
Power BI (3)
licensing (2)
service-fabric (1)
requirements (2)
wit (2)
asp.net-mvc-routing (2)
Functions (2)
example (2)
tags (2)
Business (2)
agile (12)
visual studio code (1)
continuous integration (2)
video (2)
load tests (2)
MSBuild (1)
top 5 (1)
Powershell (1)
Build (6)
microsoft alm rangers (2)
gulp (2)
workflow (2)
Quality (2)
reporting (2)
microsoft case study (2)
pipelines (1)
c# (13)
angular (1)
visual studio 2010 (18)
Visual Studio 2017 (4)
burndown (2)
bdd (2)
nintex (4)
microsoft test manager (2)
webparts (2)
outlook (2)
code-first (4)
react (1)
asp.net-mvc (6)
selinium (1)
Cosmos DB (1)
testing (5)
xaml (2)
jquery (4)
entity-framework (6)
performance (3)
tfs 2012 (4)
javascript (3)
data warehousing (2)
whitesource (1)
database projects (4)
razor (4)
visual studio 2012 (10)
windows 8 store app (2)
kanban (1)
wiki (1)
test agent (2)
business insight (2)
sp1 (2)
necc (2)
sql server (2)
DevOps (14)
Azure Functions (1)
Decisions (2)
user profiles (4)
Keith Holt (1)
feedback (2)
faq (2)
asp.net-mvc-3 (4)
windows azure (2)
test automation (1)
serverless (3)
sharepoint 2010 (10)
sql saturday (2)
IntelliSense (1)
msi (2)
team build (2)
Office 365 (1)
coded ui tests (6)
Document (1)
pdf (2)
Node.js (1)
team web access (2)
vsdbcmd (2)
Nuget (1)
TFS 2015 (6)
mstestv2 (1)
2013 (2)
sql server 2008 (2)
ssl (1)
preview (1)
DevSecOps (2)
Collaboration (2)
Mike Douglas (4)
dns (2)
MVP (2)
ssrs (4)
API Tests (1)
AzureAD (1)
association (1)
exchange (2)
selenium (4)
adfs (3)
xunit (1)
deployment (2)
spc14 (4)
bi (2)
my work (2)
fluentvalidation (2)
certificates (2)
Data Analytics (1)
Template (1)
whitelist (1)
Analytics (2)
silverlight (2)
subsites (2)
alerts (2)
CI (3)
cascading-dropdown (2)
fields (2)
Big Data (2)
test controller (2)
dbpro (2)
lab environments (1)
web (1)
team foundation server 2012 (4)
TestArchitect (5)
team foundation 2012 (2)
OAuth2 (1)
load testing (2)
Meetings (1)
gherkin (2)
work item (2)
sharepoint (5)
dependency-injection (2)
team foundation server (6)
application insights (2)
webcast (4)
connect() (1)
event-handling (2)
onenote (4)
lunch and learn (2)
single page applicaiton (1)
lync (2)
web application firewall (1)
site collections (2)
tfs (11)
sharepoint 2013 (6)
PBI (1)
M Query (1)
power tools (2)
HOLs (1)
AzureAD Admins (1)
team deploy (2)
security (1)
json (2)
installation (2)
Web API (1)
ninject (2)
owasp (3)
webpack (1)
webs (2)
tfs 2017 (1)
continuous inegration (2)

Archive

Deploying Azure Cosmos DB stored procedures with Powershell

Nov 02, 2018

How can you deploy stored procedures to an Azure Cosmos database using Azure DevOps Pipelines?

That was the question I was trying to solve. I needed to be able to deploy stored procedures if they didn’t already exist and be able to update them if they already existed. I wanted to be able to check in an individual file to create a new stored procedure or update an existing file to update an existing stored procedure. This would allow for maximum reusability in the future no matter how many stored procedures were needed or how often they needed to change.

Start with the Powershell Commandlets from https://github.com/PlagueHO/CosmosDB. This gave me the ability to create a new stored procedure or update an existing one with the New-CosmosDbStoredProcedure and Set-CosmosDbStoredProcedure commands respectively.

Using the New and Set commands I created my own command that will first create a new stored procedure and then update that stored procedure with the contents of a file. If the stored procedure already existed, it would error, so we catch that error and proceed to update knowing that it there in all cases.

function CreateOrUpdate-Sproc

{

    param([string] $sprocName, [string] $sprocFilePath) 

    echo ("Loading sproc from file: "    $sprocFilePath)

    $sprocValue = Get-Content -Path $sprocFilePath -Raw

 

    try{

        New-CosmosDbStoredProcedure -Context $cosmosDbContext

             -Database "dbName" 

             -CollectionId "collectionName" 

             -Id $sprocName

             -StoredProcedureBody "function overwriteMe() {}" 

             -ErrorAction Stop

    } catch {

        ## Ignore error here

    }

 

    Set-CosmosDbStoredProcedure -Context $cosmosDbContext

            -Database "dbName" 

            -CollectionId "collectionName" 

            -Id $sprocName

            –StoredProcedureBody $sprocValue 

            -ErrorAction Stop

}

 

Now I need to loop over the files within a folder and call this method for each file I find.

# Loop through all sproc.js files in sprocs folder to create or update sprocs

$sprocsFolder = Join-Path (Get-ScriptDirectory) "sprocs"

 

Get-ChildItem $sprocsFolder  -Filter *.sproc.js |

Foreach-Object {

    $fullPath = $_.FullName

    $sprocName = $_.BaseName.Split(".")[0]

 

    CreateOrUpdate-Sproc $sprocName $fullPath

}


Here I am using only looking for files with the extension of sproc.js. I use the rest of the filename as the stored procedure name and pass that name and file path to the CreateOrUpdate-Sproc command.

The only thing remaining for this to work is to make sure that those sproc.js files are packaged with my solution’s build. I need to make sure that these files are marked as Content and Copy Always.

Right-Click your sproc.js file and choose properties, then verify these fields are set to match:

properties.png

 

There are some aspects of this that I do not love. I wish I could find a way around catching an exception to determine if the stored procedure already exists, or better yet have a single command that would create if needed or update if needed. I believe it is likely these concerns will get solved in the future, but for the time being this solves my problem and hopefully yours as well.



Category: PowerShell

Christian Peters


We believe in helping our customers create software solutions in a better way.
We do this by having a project delivery process and technology expertise that ensures we are solving the right problem in the right way and driving the most business value.