[this intelligent life]



This Intelligent Life

By Mark Wojciechowicz 23 May, 2021
Database deployments have had two dominant models over the past decade or more – a declarative model and a migration script approach. The major tools for the declarative model are SSDT (SQL server data tools) and BIML by Varigence (I have no experience with the latter). The rest of the market is made up of the migration script model with the leading tools being Ready Roll and Liquibase. Both approaches have their own challenges. In the migration script paradigm: The definition of the code is not the source of truth. That is buried in the accumulation of the migration scripts. So viewing object level differences in source control is impossible Scripts need to be applied in the specific order that they are checked in. This makes merge conflicts a common nuisance. It’s easy enough to write a bad script and removing or undoing that change can also be painful. If there’s schema drift in the target database, things can get messy pretty quick, either leaving the database in a unique unknown state or the deployment failing altogether. What do you do with functions and procs? Since we are not comparing differences during deployment, you need to deploy this code each time in case there are changes. On the model based approach: External dependencies (database references) are awful to deal with on anything but a greenfield project. i.e. database1 has a proc that updates data in a table in database2. Database2 has a view that selects from tables in database1 and database2. Which database do you deploy first? The answer is neither, you cannot have circular references like this so you need to fix your code. The memory footprint of a large database project (and all of its reference databases) can bring visual studio to its knees. It will take a long time to open the project and resolve all the references. In both approaches, the team needs a lot of discipline. They need to adopt the new process and only apply changes this way or whatever they deploy out of band will be overwritten. I happen to prefer the model based approach because it addresses the schema drift issue, drawing the development team back into the practice of using the tool. SSDT is a fantastic tool and has a million great features, including tracking the rename of objects with an automation to help facilitate it. Besides the two issues noted above, another bit problem is that it only works with SQL Server. So what do we do about other relational databases, MySQL, Postgres, Snowflake…? I think we need another tool. I think it’s unfeasible to try to do everything that SSDT does, but in the very least it should fill the model based gap with these features in mind: Provide a model based approach for any popular relational database that follows ANSI standards Schema Compare between the project and the database or two live databases Reverse Engineer existing databases Define the project as a collection of databases. This will remove the issue of circular database references because both databases can be deployed at the same time. It won’t address real circular references, that’s still on the developer. Emphasize a small memory footprint, which will be even more challenging with the point noted above. Default to best practices, such as using an additive approach. i.e. Do not delete objects not in the project, ignore column order in table deployments. Provide the other essentials: Dependency tracking Rename operations Rollback deployment scripts on error Pre and post deployment scripts  One time migration scripts, a hybrid model will allow for flexibility when you need it
By Mark Wojciechowicz 17 May, 2019
By Mark Wojciechowicz 28 Mar, 2019
If you've tried to upgrade an existing database from SSAS 2016 to 2017 using SSDT, you may have encountered this error: Deploying the ' ' database to ' '. The JSON DDL request failed with the following error: Failed to execute XMLA. Error returned: 'The operation cannot be performed because it references an object or property that is unavailable in the current edition of the server or the compatibility level of the database. We opened a ticket with Microsoft to resolve this, after banging around with the problem for way to long. The short answer is SSDT cannot upgrade an existing database to 2017. Instead, you need to do this via an xmla script, changing the database name as appropriate:
By Mark Wojciechowicz 27 Mar, 2019
Tabular models store data in a highly compressed form and sometimes this has unexpected consequences. In what I encountered today, we had a column which was input by users. Since this was test data, it had all sorts of nonsense with values like:  aaa  AAA  aAA The tabular model looks at this through the lens of collation. In the instance of case insensitive models, all three values are considered the same and only one value will be stored. In fact, the first value it encountered will be the winner. So the only output from the model was "aaa." I can't imagine this is a big issue - I mean case sensitive data models are a nightmare. But it's definitely something to be aware of. Here's more on that: https://docs.microsoft.com/en-us/sql/analysis-services/tabular-models/table-properties-ssas-tabular?view=sql-server-2017
By Mark Wojciechowicz 08 Feb, 2018
2018-02-08 Post to slack from SQL Agent? Why on earth would I want to do that? Because email sucks and slack is cool. Also, SQL agent doesn't shed to much light on what went wrong in an SSIS package execution. It just says go look at the logs, which is not very helpful. What do we need? Permission to execute powershell Permission to read the log tables from ssisdb A slack url to post to Getting the url from slack Once permissions are sorted out, go to https://my.slack.com/services/new/incoming-webhook/ On this page, choose the channel you want to post to and hit the Add Incoming Webhooks Integration button:
By Mark Wojciechowicz 12 Dec, 2017
Occasionally, you may wind up with an SSIS package that is stuck in the status "Created Execution." In my case, the cause for this has been that new parameters have been added to the project, but a sql agent job has not been updated with values to set them. Then the sql agent job was kicked off and the job fails because it is missing the required values. This is even true when the values have been added to an environment and the sql agent job is setting them via the environment. Whatever the cause, this is how you fix it: In SSMS, under the Integration Services node, navigate down to the package and view the all executions report. Click on the overview link for the one that is in the "created execution" status Go to the "parameter values" table on the right and find the parameters with no value
By Mark Wojciechowicz 05 Aug, 2017
One Friday, on the way home from work, it occurred to me that I've probably written some form of this SQL test framework a half a dozen times. I realized that I would save a lot of time for myself and others, who are interested in testing ETL code, if I wrote an open source library. So, I took it upon myself that weekend, to pour all my learnings, from a wealth of mistakes, into a code base that would handle all the boilerplate stuff that you need for writing tests efficiently. My goal was to create something that was easy to use, easy to debug and easy to get started. SqlTest is meant to be used in a C# test project in conjunction with a test harness like nunit or mstest. I’m well aware that most SQL / SSIS developers do not live in .net and some are downright afraid of it. In fact, another popular testing framework, tSQLt , is an excellent option for many projects because you can write tests in native SQL, plus you get all the goodies that come with it, such as fakes. However, there were several reasons why I chose to go my own way: tSQLt wraps everything in transactions, so all test behavior is rolled back at the end of each test. This is great for database tests, but no good for SSIS tests which are going to initiate their own connections and transactions. Nunit has parameterized tests (or TestCase ). This allows you to create a dozen related tests very quickly, each with different input to the same test. This is tremendously efficient. By going the route of SqlTest and Nunit, this allows a consistent approach to testing for both the database and SSIS. Database tests in mstest are an elaborate hunk of junk. They don’t allow you to wrap a test in a transaction and roll it back, if you use Setup / tear down They have several layers of set up and tear down methods, one which executes for a given test and another which executes for all. There are 7 different windows to find a test, setup, teardown, initialize, cleanup, asserts and assert properties. Never mind that every test is in its own window. Needless to say, it is a nightmare to debug. Diffs cause eye-pain in source control But they do give you syntax highlighting, which is nice. I’m telling you all this so you know what horrors I’m sparing you from The initial release of SqlTest contains the essentials for testing. Methods for: executing any adhoc sql statement for setting up and running a test fetching a scalar result from the database executing a package in the catalog faking a table and dropping the fake faking a view and dropping the fake There are certainly some other features which will be useful, like executing a package from the file system for those poor souls who’ve yet to migrate to the project model. And there are some things which are less useful, like asserts on a dataset, which tends to be very fragile. I will plan to add more in time – as well, I hope for some suggested features from you. There’s documentation about how it works here: https://github.com/MarkWojciechowicz/SqlTest/wiki I encourage you to try it out today, this is an excellent compliment for any SSDT database project. Just create a new c# project in the same solution. You can install SqlTest from nuget by opening the Package Manager Console in visual studio and typing Install-Package SqlTest. A sample comes with the install, but below is a quick example to see how to use it. Enjoy!
By Mark Wojciechowicz 08 Jul, 2017
SSIS allows the ability to force a task to fail in order to test unhappy paths. It's a bit confusing about how to do this because you could do any of the following: Add an error to a sql task like divide by zero Set the execution result to failure in a script task Change the Forced execution value in the property of the task The first two options could work, but for the purposes of avoiding changing code, you can just set the ForceExecutionResult property for the task as shown below. The other execution result properties do not do anything and fail package on failure will not help either.
By Mark Wojciechowicz 30 Jun, 2017
Jenkins (the CI server) has a myriad of plugins, one of which is for executing powershell scripts. I rely on powershell heavily because I find a number of the other add-ins very limiting. In this particular case, I needed to run nunit tests and I needed the job to fail when the tests fail. I'll post more on the nunit thing at a later date, for which this is a plugin but it won't work for database projects. When the powershell script throws an error, the build just writes the error to the log and keeps chugging along. The magic button to make the build fail is to add this to the top of the script:
By Mark Wojciechowicz 17 Jun, 2017
Recently, I had the task of rebuilding a very large table in order to partition it and put it on a columnstore index. While testing this out in the lower environments, I quickly blew up the log and ran out of disk on the server, so I decided to batch it out instead. Below is an example of the query I used.
More Posts
Share by: