Date: Fri, 29 Mar 2024 07:10:46 +0000 (GMT) Message-ID: <1340499627.587.1711696246356@ip-172-31-38-4.eu-west-1.compute.internal> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_586_1688643664.1711696246352" ------=_Part_586_1688643664.1711696246352 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
This is a minor release that improves SQL Server 2012 support, provides = some new options for script generation and addresses some issues introduced= by version 1.4.3.
Today=E2=80=99s release brings some improvements to the way you build yo= ur ReadyRoll database projects.
These changes make it easier to do Continuous Integration with ReadyRoll= , whether you=E2=80=99re building your projects on self-hosted hardware (eg= . with TeamCity or TFS Build), or in the cloud (e= g. with AppHarbor or Bamboo OnDemand= ).
Previously you need to manually install a whole bunch of software on you= r build agent to deploy and test your database projects. This included Visu= al Studio 2012/Visual Studio 2010 with SP1, SQL Server Data Tools, ReadyRol= l itself plus a handful of client tools.
Now, with the power of Chocolatey and NuGet, this process has been made = a whole lot simpler. This coupled with the fact that ReadyRoll build a= gent licenses are free (as in beer). This gives you the flexibility to scal= e your Continuous Integration/Delivery environment without incurring additi= onal cost. It also means no messy software activation steps to run on your = build agents!
Before getting started, ReadyRoll=E2=80=99s most basic requirement is th= at the host system is running one of the following operating systems:
Once you=E2=80=99ve got an operating system up-and-running, you=E2=80=99= ll need to get the supporting software installed. This includes:
SQL Server Data Tools&nbs=
p;
Provides build-time support for SQL parsing and .NET CLR c=
ompilation.
Now if you have some time to burn you could go ahead and download these = and manually click-through the dozens of dialog boxes needed to get them in= stalled. However if you=E2=80=99d rather have a coffee break so you can thi= nk about how awesome it will be when your database builds are automated (as= some of us do!), then Chocolatey could be your saviour toda= y.
To install the pre-requisites above, including Chocolatey, = open a command prompt in administrator mode and run the following:
@powershe= ll -NoProfile -ExecutionPolicy unrestricted -Command "iex ((new-object net.= webclient).DownloadString ('http://chocolatey.org/install.ps1'))" && SET PATH=3D%PATH%;%syste= mdrive%\chocolatey\bin=20 CALL cinst SSDT11=20 CALL cinst MsSqlServer2012Express @ECHO Adding [NT AUTHORITY\Authenticated = Users] to sysadmin role on local SQL instance=20 "%ProgramFiles%\Microsoft SQL Server\110\Tools\Binn\sqlcmd.exe" -S . -E -Q = "ALTER SERVER ROLE [sysadmin] ADD MEMBER [BUILTIN\Users];"
That=E2=80=99s it! Well, it may take a little while to= install but you won=E2=80=99t be prompted during the installation (your ac= ceptance of the Microsoft EULA=E2=80=99s is implied; see more legal stuff here).
Oh and that last command (ALTER SERVER ROLE...
) just ensure=
s that your build agent can access the newly-installed SQLEXPRESS instance =
(which becomes the default SQL Server instance on the machine). You might c=
hoose to make this access more restrictive if you know which user your buil=
d agent will run-as.
In an ideal world, all the software that is needed to produce a build wo= uld be checked into source control so we wouldn=E2=80=99t need to install a= ny software on the build server; a key tenet of Continuous Delivery is that= builds should be reproducible, and changes in the software environment can= often result in inconsistent build artifacts being produced.
Though you can=E2=80=99t yet bootstrap an instance of SQL Server Express= or even the Data Tools to your project code, ReadyRoll 1.4.3 does at least= allow you to commit its build-time dependencies to source control using th= e power of NuGet. This means that you can rely on the outcome of you= r database builds to be consistent between deployment environments (ie. TES= T/QA/PROD). It also means that there=E2=80=99s nothing extra to install on = your build agents to get your ReadyRoll builds to work.
To integrate the build-time dependencies into your project,= open the Package Manager Console tool-window and install the package from = the NuGet gallery:
This will install the package into the current solution, rather than int= o a specific Database Project within the solution. This is a current limitation in NuGet, so to work around this we=E2=80= =99ll need to link the page to the database project manually.
To do this, firstly select the ReadyRoll Database Project in the Solutio= n Explorer, then click Project=E2=80=A6 Unload Project.
Then, right-click the project in the Solution Explorer and click
Find the <ReadyRollTargetsPath>
element and=
replace the contents as follows:
1 2 3 |
|
Save the project file, then right-click the project in the Solution Expl= orer and select Reload Project
Confirm that the build-time dependencies have been successfully linked t= o the project by building the solution:
Now commit the solution/project changes along with the package files to = source control.
Congratulations, your build agents are now ready to roll your database p= rojects!
The theme of today=E2=80=99s release can be summed up in one word: varia= bles, variables and more variables!
If in your organisation you need handle s= ubtle differences in the setup of your environments (eg. Test/Staging/Produ= ction), or if you are deploying to customer sites with variations that you = don=E2=80=99t want to hardcode into your project scripts, it can be useful = to provide certain values from an external source. ReadyRoll 1.4 allows you= to do this by tapping into the SQLCMD variable support built-in to Visual = Studio, providing you with a way to write scripts that use different values= depending on the context.
When working within Visual Studio, you ca= n use sandbox or project-level, and outside the IDE you can provide environ= ment-level values from an external source, like a Continuous Integration/Au= tomated Deployment server (eg. Octopus).
Start by adding a variable to the SQLCMD = Variables tab in the Project Property pages. The value you provide in the D= efault column will be stored in the project file (and therefore shared with= other team members) however the Local value is specific to your machine (s= tored in the non-source controlled .user file). If you l= eave the Local column blank, the Default will be used when deploying inside= Visual Studio.
Using the $(VariableName)&n= bsp;notation, reference the variable you just created in a new Deploy-Once = script.
When you build the project, the variable = will be substituted with the Default value (or Local value, if it was provi= ded).
When deploying your database outside of V= isual Studio, you can also provide a set of values for the variables define= d in your project.To do this, first enable SQLCMD package scripts for outpu= t (or enable .nupkg output if you are using Octopus). Bu= ild your project to produce the package deployment script, eg. MyD= atabase_Package.sql. Notice in the header of the file that the full li= st of project variables are included, but commented out along with their de= fault values. You will need to provide values for each of the variables lis= ted, as part of the next step to deploy your database (Note that $= (DatabaseName) is a built-in SQLCMD variable that is required for= all database projects). To deploy your database, open the command prompt a= nd execute the following command:
SQLCMD.E= XE -E -S "(localdb)\Projects"=20 -i "AdventureWorks_Package.sql"=20 -v DatabaseName=3DAdventureWorks_STAGING=20 -v Environment=3DSTAGING
Note: There is currentl= y no straightforward way of passing SQLCMD va= riables into MSBuild; at present the Package deployment method is the best = way to use SQLCMD variables at this time. If you=E2=80=99d prefer to use th= e Patch deployment script method instead (which provides a del= ta file of pending migrations and is only available via MSBuild), please&nb= sp;get in touch.
Next we=E2=80=99ll look at a variation of= this approach, which involves using the built-in Octopus support to provid= e SQLCMD variable values you can use in your database deployments.
ReadyRoll 1.3 introduced support for Octopus, making i= t easy for database deployments to be coordinated alongside other applicati= on components using the NuGet-based package format.
ReadyRoll 1.4 builds on this support by p= roviding automated mapping of Octopus variables to SQLCMD variables. This m= akes it incredibly easy to consume values from your existing pool of enviro= nment-specific variables, or to make use of the project and package variabl= es provided by Octopus itself.=
For example, say you wanted to store the = version number of the deployment package in the target database. To do this= , firstly add the $(OctopusPackageVersion) variable to t= he ReadyRoll project and give it a Default value, eg. 1.0.0.0.
Then include a reference to the variable in a Deploy-Once script. W= hen you deploy locally it will output the Default value, however when you d= eploy via Octopus the value will be substituted with the deployment package= version:
To get a full list of built-in variables,= check out the Octopus Variables d= ocumentation.
Mapping isn=E2=80=99t just limited to bui= lt-in Octopus variables; simply add your variables to your ReadyRoll projec= t to map your custom variables as well.
Note: If you=E2=80=99re alre= ady deploying your database projects using Octopus, please note there is a = code-breaking change in this release: previously, the $(DatabaseNa= me) SQLCMD variable would to hardcoded to the name specified in y= our ReadyRoll project. From ReadyRoll 1.4 onwards, you will need to provide= a value for the database name within the Octopus project variables.
As ReadyRoll projects are based within th= e Visual Studio IDE, you=E2=80=99ve always had the ability to develop and d= eploy multiple databases. However up until now, it has been difficult to ma= nage the deployment of databases with interdependent object references.
For example, say you have two projects:&n= bsp;DatabaseA and DatabaseB. If DatabaseB conta= ined a script that referred to an object within DatabaseA, you would first = need to build and deploy DatabaseA before you could even build DatabaseB. T= his is because of the way that ReadyRoll uses a separate copy of your datab= ase called the Shadow that ensures your project scripts = are parsed & validated before deploying to your Sandbox&n= bsp;database. This process is called gated deployment, and it requires that= all database references be dynamic (which is why you need to use the = $(DatabaseName) variable whenever you reference your da= tabase).
To make it easier to work with interdepen= dent databases, ReadyRoll 1.4 introduces the ability to create dynamic data= base references within your projects.
Start by adding a Database Reference to y= our project (from the Solution Explorer context menu).
Don=E2=80=99t worry about the Da= tabase name field; this will be automatically substituted at buil= d time.
To refer to an object within the database= , you can then use a three-part object name within a Deploy-Once script usi= ng =E2=80=93 you guessed it =E2=80=93 a SQLCMD variable:
When deploying outside Visual Studio, jus= t make sure you specify a variable/value pair for each of the database name= s that are referenced from your project.
For a variety of reasons, sometimes it=E2= =80=99s necessary to exclude certain objects from your database projects: m= aybe some objects are needed only in Production (such as log tables or repo= rting objects) or perhaps their deployment is managed by a completely separ= ate deployment mechanism (like in a CRM system). This can be a problem when= using the ReadyRoll DBSync tool, which will eagerly imp= ort any new objects from the source database that are added to your schemas= .
To make it easier to prevent these types = of objects from appearing in your project altogether, ReadyRoll 1.4 introdu= ces regex-based exclude rules to the project system. For example, say you w= anted to exclude the [reporting].[log] table object from= the database. To do so, just add the following to the top of your .sqlproj file:
<Prop= ertyGroup> <ExcludeObjectsFromImport> Table=3D\[reporting\]\.\[log\]; </ExcludeObjectsFromImport> </PropertyGroup>
If you=E2=80=99d like to exclude all Tabl= es & Stored Procedures in a particular Schema (along with the Schema it= self) from your project, try this:
<Pr= opertyGroup> <ExcludeObjectsFromImport> Schema=3D\[reporting\]; Table=3D\[reporting\]\.\[(.*?)\]; StoredProcedure=3D\[reporting\]\.\[(.*?)\]; </ExcludeObjectsFromImport> </PropertyGroup>
Don=E2=80=99t worry=E2=80=A6 we won=E2=80= =99t be offended if you add lots of exclude rules to your project! Tha= nks to @DataChomp for = the idea.