User profile for Kendra_Little

Hi Peter, These options aren't stored in the metadata in SQL Server after the index is created -- EXCEPT for fillfactor. For fillfactor, you can change the 'SyncOptionIgnoreFillFactor' setting in the .sqlproj file. Instructions on that are here, and the note on that setting indicates it also will mean that any index padding settings you use will be scripted as well. For the options which apply to the index creation process only and aren't persisted in the metadata, like ONLINE and SORT_IN_TEMPDB, there is a User Voice page on this here which has an explanation of some of the considerations around why we don't have this presently. I personally think it's worth expanding the options list in Compare to have global enable/disable for each of these as it would be much nicer for you to be able to configure that preference rather than having to use snippets. Please do consider adding a note to that User Voice forum if you have the time. I'll also internally copy this thread over to the SQL Compare team as well, so they are aware. Kendra / comments

Hi Peter,These options aren't stored in the metadata in SQL Server after the index is created -- EXCEPT for fillfactor. For fillfactor, you can change the 'SyncOptionIgnoreFillFactor' setting in th...

0 votes

Hello, You can control this behavior by setting the "SyncOptionIgnoreCollations" to false in your .sqlproj file. Notes on how to make this change and example syntax are in the article, "Configuring comparison & script generation options." Hope this helps! Kendra / comments

Hello,You can control this behavior by setting the "SyncOptionIgnoreCollations" to false in your .sqlproj file. Notes on how to make this change and example syntax are in the article, "Configuring ...

0 votes

Note: striking this for other readers, as I did an incorrect step in my repro Thanks for clarifying! I did some testing. I believe the bug is with the Release Artifact -- in my repro it contains that exact text as well. However, it is misleading you-- the post deployment script is still being run in my testing. For my repro, I used this script and found that the job would be recreated even when I made no state change in the database itself, and the release artifact displayed that message. The team has created a Jira bug to prioritize this and make sure it doesn't get forgotten. But for the time being I think the actual deployment functionality is what you want. Kendra / comments

Note: striking this for other readers, as I did an incorrect step in my reproThanks for clarifying!I did some testing. I believe the bug is with the Release Artifact -- in my repro it contains that...

0 votes

Note for readers who come by later: this was not correct, see later in the thread please! ps: just confirmed from chat with the team that this is considered a bug, the desired behavior is to include the pre- and post- sql code into the Release Artifact for display (as well as executing it, which is occurring). The Jira item will help make sure we track that. / comments

Note for readers who come by later: this was not correct, see later in the thread please!ps: just confirmed from chat with the team that this is considered a bug, the desired behavior is to include...

0 votes

Sorry about this, Tom -- the team suggested I wasn't seeing the right behavior, and I ran through it again on the plane today. I made an error in my repro setup, and it was the *build* creating the the job that I saw, not the deployment. You're correct that it's not running it. Would committing a small state change as a workaround make this functional for you in the meantime? You could do a a change as small as adding / editing an extended property in the database itself where you want the post-deployment script to run. Sorry about the misinformation! / comments

Sorry about this, Tom -- the team suggested I wasn't seeing the right behavior, and I ran through it again on the plane today. I made an error in my repro setup, and it was the *build* creating the...

0 votes

From a chat in the team Slack channel, I believe that it's intended that you *should* be able to deploy pre / post scripts without a schema change, so it could be a bug. I'd like to set up a repro. I'm guessing that you're using SQL Source Control / State Based + SQL Change Automation? (Just checking for the repro setup as migrations approach also has pre and post scripts). / comments

From a chat in the team Slack channel, I believe that it's intended that you *should* be able to deploy pre / post scripts without a schema change, so it could be a bug. I'd like to set up a repro....

0 votes

Hi @tomslickers , I think it can often make sense to create a dedicated InstanceConfiguration database (just picked a random name) to version changes for instance level objects such as linked servers and SQL Agent jobs, for a few reasons: 1. As you've found, sometimes you want to just deploy a change to the instance level object, and nothing to the database itself. Doing a deployment for that database is problematic from an audit trail /workflow perspective if you aren't actually changing anything in the database, but rather a related item elsewhere. 2. On many instances, a set or family of databases is used together to support an application, so there's no obvious choice as to which database you'd manage instance-level objects from. In these cases, often SQL Agent jobs will interact with multiple databases, as well. Creating a standard database for this on each instance you manage can make it easier to identify where code for instance configuration is stored. 3. Creating supporting objects in that database such as procedures and tables can be quite useful. Some examples: A team I worked on created a utility that we used so that we could control SQL Agent Job code in relatively simple configuration scripts, and then procedures in the utility would deploy the agent jobs. This level of abstraction was very nice because the definitions we stored of agent jobs were the type of step, contents of the step, etc, which made it much easier to review changes. Static data tables could be used to master the metadata. (Note: I'd love to write a simple open source utility like this someday!) You might want to create a process where you store some history data in tables for a short period in this database when changes to server level objects will remove the history -- such as losing job history when recreating a job. 4. There are some scenarios where it really makes sense to decouple these instance level objects from the database code itself for deployment reasons -- for example, on an Availability Group with multiple nodes, you might need to configure SQL Agent Jobs and Linked Servers on each node, while you're deploying database code ONLY to the writable node. Deploying an InstanceConfiguration database to each node can make managing that with automation easier. (You do of course have to write the code that manages these objects in a way that's aware of the AG / properly manages job status of course.) Hope this helps! Kendra / comments

Hi @tomslickers ,I think it can often make sense to create a dedicated InstanceConfiguration database (just picked a random name) to version changes for instance level objects such as linked server...

0 votes

Hi @brookal , Just tagging onto @SamTouray 's note , and I'm also assuming that you are authoring changes in SQL Source Control. If you're using SQL Change Automation for everything (authoring in Visual Studio with what used to be called ReadyRoll), just holler -- slightly different answer for that, but similar options. The method of doing this depends a little on which type of tables they are. If these are relatively small reference tables where the data only ever changes as part of the development process (not user input), such as something like a list of states, countries, etc, then you might want to add these as static data tables, which essentially checks the data into source control. Documents on that are here. If you've got static data but it needs to be different in different environments, you can do that with a pre- or post- deployment script, as in this example. For data modifications to tables that aren't suitable for static data, I would also do this with a pre- or post-deployment script. The requirements for these are here, and the first one is really important: it needs to be easily rerunnable, so you want your script to have a way to indicate that it's been done and an initial check that looks for that and skips the step if possible. As @SamTouray mentioned, there are migration scripts in SQL Source Control, but I believe that in the future the functionality of the pre-and post- scripts will be what we rely on instead of the migration scripts, which may be eventually phased out as a feature. Hope this helps! Kendra / comments

Hi @brookal ,Just tagging onto @SamTouray 's note , and I'm also assuming that you are authoring changes in SQL Source Control. If you're using SQL Change Automation for everything (authoring in Vi...

0 votes

Hi Asaf, I chatted with the team this morning and confirmed that we don't have a custom extension for AWS DevOps at this point. That doesn't mean you can't use it, however -- you can use the SQL Change Automation PowerShell module downloaded from the powershell gallery as part of a build script. The PowerShell cmdlets have full functionality, there's nothing in the extensions you can't do in the scripts. I personally like using them more than the extensions. We have worked examples and also reference documentation for the cmdlets which I've used quite a lot and which got me started fast. I've also submitted a suggestion to get an article done for Product Learning which walks through setting this up in AWS. I think it'd be a great post but the links above should get you started right away. Kendra / comments

Hi Asaf,I chatted with the team this morning and confirmed that we don't have a custom extension for AWS DevOps at this point. That doesn't mean you can't use it, however -- you can use the SQL Cha...

0 votes

Hi Joshua, Great question. I have two Product Learning articles outlined and set up to write on managing dependencies like this in builds, so good to know that it's a needed article! Here's the short version: This happens only for specific objects in SQL Server that reference the linked server, specifically views and some functions. This is because 'deferred name resolution' doesn't kick in for those objects when they are created. That's a SQL Server thing, and there's no SQL Server option to override it. The build validation step ensures that your database objects can be built from Source Control, so this issue can be a thorny one. The culprit is usually distributed views that reference a table across the linked server. Option: filter The simplest way to solve this is to simply filter the object out of your project. If you have a very small number of objects impacted and no plans to change their code / no desire to have them in the build, then that can work. Option: synonym (and maybe a linked server in the dev env) If you'd like to build the object, you could create the linked server, but just doing that is often not desirable because often the link is to a production database, and you typically would prefer to not make a build dependency on production if you don't have to (and often firewalls/ trust between environments wouldn't allow it anyway). My favorite option to make this work is to create a synonym for the remote object. The synonym provides a layer of abstraction and additional control -- it gives you the ability to make the remote object reference to a local database instead of the linked server during the build itself, for example -- or you could still use a linked server but use one in the development environment instead. Happy to give more details on either the filtering option or the synonym option, but will wait to see if either of those sound like they'll work for you. Kendra / comments

Hi Joshua,Great question. I have two Product Learning articles outlined and set up to write on managing dependencies like this in builds, so good to know that it's a needed article! Here's the shor...

0 votes

How can we help you today?

Kendra_Little

Comments