SQL Server Database Tests / Changing Server

Question

Post reply

SQL Server Database Tests / Changing Server

Phil Parkin

SSC Guru

Points: 246960
More actions
May 1, 2018 at 6:08 am

#410470

We use database tests in our CI environment and these are configured to run on our QA server. This means that the app.config file in the database testing project contains the QA server's connection details.
Is there a way to quickly switch things around, so that I can run the tests locally before I check changes in for deployment to QA? (Without me having to go through the process of modifying app.config and resetting it.)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

If this were an SSIS project, I'd be thinking SSIS configuration database. Is that the case, or is this something else?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

sgmunson - Tuesday, May 1, 2018 8:58 AM
If this were an SSIS project, I'd be thinking SSIS configuration database. Is that the case, or is this something else?

It's a database unit-testing project (to create one in SSDT, right click on any proc and select 'Create Unit Tests').

You end up with a folder structure like this

Where the name of my database is 'Config' and the three .cs nodes contain different types of unit tests.
The app.config file contains this line:

<ExecutionContext Provider="System.Data.SqlClient" ConnectionString="Data Source=ServerName;Initial Catalog=Config;Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True"

Which effectively hard codes the binding between the tests and the server on which the tests will execute.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

sgmunson SSC Guru Points: 110640 More actions · Answer 3

Phil Parkin - Tuesday, May 1, 2018 12:02 PM
sgmunson - Tuesday, May 1, 2018 8:58 AM
If this were an SSIS project, I'd be thinking SSIS configuration database. Is that the case, or is this something else?
It's a database unit-testing project (to create one in SSDT, right click on any proc and select 'Create Unit Tests').
You end up with a folder structure like this
Where the name of my database is 'Config' and the three .cs nodes contain different types of unit tests.
The app.config file contains this line:
<ExecutionContext Provider="System.Data.SqlClient" ConnectionString="Data Source=ServerName;Initial Catalog=Config;Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True"
Which effectively hard codes the binding between the tests and the server on which the tests will execute.

Might not be convenient, but how about having several copies of app.config that you keep in other folders, and the folders are labelled based on which of the files they contain, and only one app.config goes in any given folder. You could, at least in theory, copy over any one of the files over the original at any point in time and do a build, as long as you remember to put it back later. Not necessarily great, but at least plausible.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

sgmunson - Tuesday, May 1, 2018 12:09 PM
Phil Parkin - Tuesday, May 1, 2018 12:02 PM
sgmunson - Tuesday, May 1, 2018 8:58 AM
If this were an SSIS project, I'd be thinking SSIS configuration database. Is that the case, or is this something else?
It's a database unit-testing project (to create one in SSDT, right click on any proc and select 'Create Unit Tests').
You end up with a folder structure like this
Where the name of my database is 'Config' and the three .cs nodes contain different types of unit tests.
The app.config file contains this line:
<ExecutionContext Provider="System.Data.SqlClient" ConnectionString="Data Source=ServerName;Initial Catalog=Config;Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True"
Which effectively hard codes the binding between the tests and the server on which the tests will execute.
Might not be convenient, but how about having several copies of app.config that you keep in other folders, and the folders are labelled based on which of the files they contain, and only one app.config goes in any given folder. You could, at least in theory, copy over any one of the files over the original at any point in time and do a build, as long as you remember to put it back later. Not necessarily great, but at least plausible.

This is a good idea, I was already considering something along these lines.
But before I went down that slightly inelegant road, I was hoping for something more streamlined.
Thanks for the response.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

sgmunson SSC Guru Points: 110640 More actions · Answer 5

Phil Parkin - Tuesday, May 1, 2018 12:22 PM
sgmunson - Tuesday, May 1, 2018 12:09 PM
Phil Parkin - Tuesday, May 1, 2018 12:02 PM
sgmunson - Tuesday, May 1, 2018 8:58 AM
If this were an SSIS project, I'd be thinking SSIS configuration database. Is that the case, or is this something else?
It's a database unit-testing project (to create one in SSDT, right click on any proc and select 'Create Unit Tests').
You end up with a folder structure like this
Where the name of my database is 'Config' and the three .cs nodes contain different types of unit tests.
The app.config file contains this line:
<ExecutionContext Provider="System.Data.SqlClient" ConnectionString="Data Source=ServerName;Initial Catalog=Config;Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True"
Which effectively hard codes the binding between the tests and the server on which the tests will execute.
Might not be convenient, but how about having several copies of app.config that you keep in other folders, and the folders are labelled based on which of the files they contain, and only one app.config goes in any given folder. You could, at least in theory, copy over any one of the files over the original at any point in time and do a build, as long as you remember to put it back later. Not necessarily great, but at least plausible.
This is a good idea, I was already considering something along these lines.
But before I went down that slightly inelegant road, I was hoping for something more streamlined.
Thanks for the response.

Glad to help out. And should a more streamlined option come along, this won't be all that hard to undo,

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Lowell SSC Guru Points: 323518 More actions · Answer 6

can you create some Aliases in SQL configuration manager, so, for example servers named [ProdCluster01] and [ProdETSource] etc etc are all aliased to point to your local machine? or to the Dev machine so you don't touch production? then no config file changes are needed at all, right?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

RAThor SSC Eights! Points: 941 More actions · Answer 7

Phil Parkin - Tuesday, May 1, 2018 6:08 AM
We use database tests in our CI environment and these are configured to run on our QA server. This means that the app.config file in the database testing project contains the QA server's connection details.
Is there a way to quickly switch things around, so that I can run the tests locally before I check changes in for deployment to QA? (Without me having to go through the process of modifying app.config and resetting it.)

I've found XML Transforms (syntax) to be invaluable for environment-specific settings such as connection strings. It's built-in to newer versions of Visual Studio. I've used the SlowCheetah extension for years, and there are other Visual Studio extensions if you are using an older version, or want the enhancements the extension may provide.

Transforms are essentially overrides of the config file, w/ one for each Solution Configuration. The config files are generated w/ the transformations applied when deploying through Visual Studio (and can be previewed). The app.config would contain your local connection string, & the QA config transform would contain the QA connection string.