Activity overview
Latest activity by ChrisGStevens
I am able to Sync-DlmDatabaseSchema for a single schema from my repository using the Filters. The first schema contains "Shared" objects which are used by other schemas but not all. I went ahead and created a second filter for a different schema, "Customer", which has dependency on the first schema. I have successfully created 2 power shell scripts and this seems to work great. So right now besides the .nuget containing all of the objects I thought this was going to work... My new issue in my attempt is Migrations. Once I added a new Mirgration to my Shared schema I was no longer able to deploy the Customer shema. Basically my migration does this. /* Add new Column */ ALTER TABLE Shared.ADRole ADD NeedMirgrationForThisField varchar(50) NULL GO /* Populate your field */ UPDATE ad SET NeedMirgrationForThisField = 'This is Initial' FROM Shared.ADRole ad GO I can't get passed the validation when I am running for the Customer schema; it is comparing the Shared schema as I get the following error. Invoke-DlmDatabaseSchemaValidation : Schema validation failed: 2 batches failed. The first error is 'Column names in each table must be unique. Column name 'NeedMirgrationForThisField' in table 'Shared.ADRole' is specified more than once.'. I can go back remove the field and remove my migrations history for the Shared sync and rerun Shared.ps1. It runs fine as well as the next run with no change. But some reason once I added the migrations the Customer.ps1 which only looks at customer schema based on the Filter is looking at the Shared objects and those objects migrations. / comments
I am able to Sync-DlmDatabaseSchema for a single schema from my repository using the Filters.The first schema contains "Shared" objects which are used by other schemas but not all.I went ahead and ...
I was able to get the New-DlmDatabaseDocumentation to use the Filter. I didn't notice the difference between Invoke-DlmDatabaseSchemaValidation and New-DlmDatabaseDocumentation. One is -Filter and the other is -FilterPath. $validatedSchema = Invoke-DlmDatabaseSchemaValidation $myScriptPath -Filter $myFilterFile -SQLCompareOptions $compareOption -TemporaryDatabase $myTempDB $documentation = New-DlmDatabaseDocumentation $validatedSchema -FilterPath $myFilterFile -SQLCompareOptions $compareOption -TemporaryDatabase $myTempDB I need to make sure I apply the same filter and config to the entire flow. Now I just need to be able to use the Filter and such when creating my package. / comments
I was able to get the New-DlmDatabaseDocumentation to use the Filter. I didn't notice the difference between Invoke-DlmDatabaseSchemaValidation and New-DlmDatabaseDocumentation. One is -Filter an...
Differences between Cmdlets
My goal is create specific objects and then deploy the static data. I have a few different static data scenarios.I have a database with several different schemas. My goal is to start small with ju...
I did the same thing krachynski. I learned that the SDK is no longer supported so I looked into other ways to monitor all of my database changes and generate our change scripts.
I got the Dashboard into Production. Then wanted to know how to change the email format as I want it to include the object lists. This is when I found out that it is no longer being updated. [image]
But I am still on the path to getting DLM Automation running. / comments
I did the same thing krachynski. I learned that the SDK is no longer supported so I looked into other ways to monitor all of my database changes and generate our change scripts.
I got the Dashboar...
Be sure to add your SQL Monitor Service Account to the MSDB Database and add it to the DatabaseMailUserRole. / comments
Be sure to add your SQL Monitor Service Account to the MSDB Database and add it to the DatabaseMailUserRole.
You make your changes to the Target database.
In Visual Studio you would use the ReadyRoll to import and generate the new migration script based on the changes it found.
The Shadow database is used for verification of the scripts so no need to modify it as it is created every time you use the sync to view pending changes or import.
Here is more info: https://documentation.red-gate.com/rr1/key-concepts/target-and-shadow-databases / comments
You make your changes to the Target database.
In Visual Studio you would use the ReadyRoll to import and generate the new migration script based on the changes it found.
The Shadow database is used...
[SQLCop].[test Tables without a primary key]
Need to handle the SQL Version differences.
This is the perfect example. [SQLCop].[test Tables without a primary key]
When reviewing the SQLCop failures I noticed that I had Database Users instead ...
Right now I am using 13.0.2.5109.
Taking the below script and doing the compare I am actually ending up with a change script does have any changes in it now; goes from BEGIN TRANSACTION and then the next batch is COMMIT TRANSACTION.
Reviewing the differences in SQL Compare I can see that the LOGINs are different because of the qa vs dev and the Database User is the same.
This is actually a little bit different than the original issue I had and trying to figure out what state I was in.
Hopefully not Kansas [image]
Here is what I have accomplished by doing it this way. When we create a new app I have a stored procedure which creates the LOGIN, DatabaseUser for all needs database and in each of the databases it creates the ROLE, SCHEMA and sets up the permissions for the securables within the schema.
Then in the compare for the Role I only have a single DatabaseUser.
When we deploy I have a script that updates the DatabaseUser with the correct enviornments login. -- DEV Instance -- CREATE SCRIPT
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'MyDomain\svc_devTest')
CREATE LOGIN [MyDomain\svc_devTest] FROM WINDOWS
GO
IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE name = N'Svc_Test')
CREATE USER [Svc_Test] FOR LOGIN [MyDomain\svc_devTest]
GO
-- QA Instance -- CREATE SCRIPT
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'MyDomain\svc_qaTest')
CREATE LOGIN [MyDomain\svc_qaTest] FROM WINDOWS
GO
IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE name = N'Svc_Test')
CREATE USER [Svc_Test] FOR LOGIN [MyDomain\svc_qaTest]
GO
/ comments
Right now I am using 13.0.2.5109.
Taking the below script and doing the compare I am actually ending up with a change script does have any changes in it now; goes from BEGIN TRANSACTION and then th...
CREATE USER should have IF NOT EXISTS
The following code is generated when doing my compare and will generate an error since my user does exist.-- User
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'MyDomain\svc...