User profile for Kendra_Little

Hi Gill, The shadow database for SQL Change Automation doesn't get automatically dropped. If you manually wish to drop the database, it is OK as it will get recreated when it's needed. We don't automatically drop this because keeping it in place can speed up the process of verifying -- sometimes significantly. You do have a couple of options when it comes to maintaining the database, if you don't want to manually drop it: You could place the shadow database elsewhere -- even on a non-Azure SQL Database instance, if you wish. Please do note that this will only realistically work if you're not using TSQL which only compiles/successfully executes against Azure SQL Database. (For example if you have a pre-script which creates External Tables.) To configure the shadow database location, you edit your user settings file. You could use the Serverless tier for your Shadow database if you still wish to use an Azure SQL Database for verification, but wish to not be billed for idle time. This can be done by editing the CreateDatabase.sql script in the Provisioning folder and specifying the SERVICE_OBJECTIVE you wish to use in the script. Please note that when you use the Serverless tier, it is normal for SQL Server to throw an error when you wake up / resume a database. Hope this helps, Kendra / comments

Hi Gill,The shadow database for SQL Change Automation doesn't get automatically dropped. If you manually wish to drop the database, it is OK as it will get recreated when it's needed. We don't auto...

0 votes

Hi @GurpreetGill , I might handle this in different ways, depending on how frequently I expect this pattern to happen in the course of my workflow. If this may happen frequently, disabling the programmable object feature for the project and using a "versioned" migrations only approach is likely the best option. If you would like to do this, there are two steps. 1) The "ProgrammableObjectHandling" setting can be set to ScriptInMigrations. 2) You likely would also want to modify the "SyncToOfflineSchemaModelObjectTypes" setting so that includes programmable objects in the offline schema model. If this is a rare occurrence, the simplest option is to split the deployment in two -- deploy the PO first in one deployment and then the migration in the next deployment. If this is a rare occurrence and splitting the deployments into two is an issue, one could also leave POs enabled and manually create a migration script with the same PO in it for ordering purposes, but manual validation that these are in sync for that deployment is of course needed. Essentially, the Programmable Object feature does generally make ordering of changes simpler. But for the scenario you mentioned it actually makes it a bit difficult. For that reason we do support disabling the feature for workflows where it may not be a good fit. Hope this helps, Kendra / comments

Hi @GurpreetGill ,I might handle this in different ways, depending on how frequently I expect this pattern to happen in the course of my workflow. If this may happen frequently, disabling the prog...

0 votes

Hi, We don't have plans to change this on our roadmap at this time. Do you have a specific use case which is causing you to ask this question / is there a particular scenario that this would enable you to implement? Understanding that need is always useful to us in our planning. Thanks, Kendra / comments

Hi,We don't have plans to change this on our roadmap at this time. Do you have a specific use case which is causing you to ask this question / is there a particular scenario that this would enable ...

0 votes

Hi @EricLamontagne , Thanks for this question. This is something that I have been meaning to blog about a bit to help our existing users understand how these things relate to one another. Essentially, what we find is that new customers who are getting used to our tooling get overwhelmed if we start throwing a lot of product names at them. Instead, they just want to initially know: what are the capabilities that you offer? But for customers who are already with us and who are already familiar with some products, this can be a bit confusing as you already know the product names and want to know how they work together. The shorter version of the answer is: Redgate Deploy is a subscription offering. It contains SQL Change Automation and Flyway (along with other tools). SQL Change Automation's strength is that is offers many more features for SQL Server users than Flyway does: SQL Change Automation helps you author scripts, automatically numbers them, helps identify merge conflicts, and performs full CI/CD support including testing, code analysis, etc. Flyway, on the other hand, is a tool that helps you automate deployments (a subset of that functionality) -- Flyway's strength is that it works with many databases. Here's a more detailed version of how these all relate: What is Redgate Deploy? Redgate Deploy is a subscription-based solution for versioning and Continuous Integration / Continuous Delivery for databases. Redgate Deploy has features for version control, automatic script authoring, continuous integration (including unit testing and code analysis), automated deployments, drift detection, and database virtualization (cloning) Redgate Deploy SQL Server Components To implement versioning and CI/CD for SQL Server, Redgate Deploy users can create database projects using SQL Source Control or SQL Change Automation, depending on the team's preferred style of working. If teams wish to take on a "combined" approach, they can even combine these types of projects into a single workflow. Fully automated builds, tests, code analysis, and deployments are done by SQL Change Automation. Additionally, Redgate Deploy contains SQL Compare and SQL Data Compare both for interactive use, or for use in automation via command lines. SQL Clone is included to support ephemeral database creation, both for development environments as well as development, test, and pipeline automation scenarios such as Pull Requests. Redgate Deploy Oracle Components To implement versioning and CI/CD for Oracle, Redgate Deploy users can create database projects using Source Control for Oracle or Redgate Change Control, again based on the team's preferred style of working. A combined approach with these projects is also available. Deployment automation can be run by Redgate Change Automation. Redgate Deploy contains Schema Compare for Oracle and Data Compare for Oracle for investigative/interactive use, and these can also be used in command line automation to supplement pipelines as desired. Data virtualization for Oracle databases is planned to become available in Redgate Deploy in the second half of 2021. Redgate Deploy Support for 18 Additional Databases Redgate Deploy also includes Flyway to provide deployment automation support for additional databases: PostgreSQL, MySQL, DB2, Aurora MySQL, MariaDB, Percona XtraDB Cluster, Aurora PostgreSQL, Redshift, CockroachDB, SAP HANA, Sybase ASE, Informix, H2, HSQLDB, Derby, Snowflake, SQLite, Firebird. While Flyway also works for SQL Server and Oracle, for most projects we find that customers will choose the specific SQL Server or Oracle components listed above, as they not only save time by helping author the code which will be deployed, they also offer much richer CI/CD support. Hope this helps, and very happy to answer any questions! Kendra Edit 20200217 - used "SQL Server" when I meant "Oracle" in one spot 😂 / comments

Hi @EricLamontagne ,Thanks for this question. This is something that I have been meaning to blog about a bit to help our existing users understand how these things relate to one another. Essential...

0 votes

Hi @pkettlew , We looked into this, and I believe that we had phrased this in the documentation in a confusing way. I think the following sentence was the source of the confusion: By default, the shadow database will be created on the same SQL Server Instance as the development database with the name [project]_$(username)_SHADOW. This was meant to explain the naming convention used behind the scenes and represent a placeholder for the user name, not to imply that you could use the variable when customizing the shadow database connection string. I have updated the documentation page a bit to try to make how this works more clear. I changed the sentence quoted above to "[project]_[YourUserName]_SHADOW" so that it doesn't resemble an environment variable. I also reworded a little bit of info below there which may prove useful to you (the second bullet point is new-- we previously had that on a linked page and not here-- and the third is reworded for clarity). You can change where the shadow database is created by editing the user settings file for your project: You may need to create the file if it doesn't exist, make sure it follows the naming convention: '<YourProjectName>.sqlproj.user' This user file contains settings specific to your local environment which shouldn't be committed to version control – see our list of files to be excluded from version control When using a database as a development source, you may choose to omit the "Initial Catalog" portion of the ShadowConnectionString. In this case the shadow database name will continue to be derived from the development database name and the shadow database will change if the development database is changed. I hope this helps, and thanks very much for bringing this to our attention. Kendra / comments

Hi @pkettlew ,We looked into this, and I believe that we had phrased this in the documentation in a confusing way. I think the following sentence was the source of the confusion:By default, the sha...

0 votes

Hi @Kris_Lawton , I am a little confused about what type of pipeline we are talking about. Is this a YAML pipeline? Or one of the classic pipeline types? If it is a YAML pipeline, the build task publishes the build artifact to the pipeline, so you can always pull it down to the local agent with: - task: DownloadPipelineArtifact@2 displayName: Download build artifact inputs: buildType: 'current' artifactName: 'Database Build Artifact' targetPath: '$(System.DefaultWorkingDirectory)' I am not clear why this would have suddenly changed in your pipeline, though. Edit: struggling with code formatting / comments

Hi @Kris_Lawton ,I am a little confused about what type of pipeline we are talking about. Is this a YAML pipeline? Or one of the classic pipeline types? If it is a YAML pipeline, the build task pub...

0 votes

Hi, We cover the answer to that in this documentation page: https://documentation.red-gate.com/sca/developing-databases/concepts/baseline Hope this helps, Kendra / comments

Hi,We cover the answer to that in this documentation page: https://documentation.red-gate.com/sca/developing-databases/concepts/baselineHope this helps,Kendra

0 votes

Hi @pkettlew , Currently, External Tables are not supported by the SQL Compare Engine and they are not scripted out. External Tables / External Data Sources / External File Formats are the main thing that has come up recently which are not scripted. The main categories of other things which aren't scripted are database configuration properties and settings and instance-level things like SQL Server Agent jobs, linked servers. There are some "dependency chain" scenarios which cause some objects to script as "unsupported", which is a slightly different scenario. Work for adding support of External Tables is currently ongoing, and we expect support for External tables to ship this quarter (Q1 2021) in SQL Compare and its related tools (including SQL Change Automation). Creating External Tables in a pre-deployment script is the current workaround. Kendra / comments

Hi @pkettlew ,Currently, External Tables are not supported by the SQL Compare Engine and they are not scripted out. External Tables / External Data Sources / External File Formats are the main thi...

0 votes

Hi, The definition of the external tables and anything that the external tables are dependent on (ie the external data source) would need to be set up by the pre-script. Things that reference an external table, like views, procedures, etc, do not need to be modified in the baseline script (the baseline script is run *after* the pre-deployment script.) Hope this helps, Kendra / comments

Hi,The definition of the external tables and anything that the external tables are dependent on (ie the external data source) would need to be set up by the pre-script.Things that reference an exte...

0 votes

Hi @pkettlew, The shadow database is where scripts are run locally -- more information on it is here: https://documentation.red-gate.com/sca/developing-databases/concepts/shadow-database Cheers, Kendra / comments

Hi @pkettlew,The shadow database is where scripts are run locally -- more information on it is here: https://documentation.red-gate.com/sca/developing-databases/concepts/shadow-databaseCheers,Kendra

0 votes

How can we help you today?

Kendra_Little

Comments