Options
Dynamically provision the login/pw as part of the db create?
dgreene_mobi
Posts: 3
I'm evalutating RedGate suite of products for my team, and we're looking to replace our home-grown solution with RedGate or another product.
So far, I like what I see, but we have some specific requirements around how it all integrates with our current process.
Question #1) How can I dynamically have a newly deployed DB from source control have a different login/password.
The requirement is because we may have 5 copies of the same database on our QA DB server, but each DB should have it's own login/password. historically, we've just used the same string to construct the schema and user as well, but this isn't as strict of a requirement as moving to a standard schema per database wouldn't be bad (and is probably a good thing to do overall).
Question #2 - long question, more of a general "how does SC work for Redgate specicially sort of thing:
How do migrations get merged with SC changes. Specifically, how would red gate handle the following situation:
Commit #1:
create table schema.db_table_name
id bigint not null identity(1,1),
first_name varchar(64),
last_name varchar(64)
Time passes, data is added to table from our application...
Commit #2:
I want to merge the two columns, so I write SQL to copy and concatenate the last_name at the end of first_name.
Next statement, I drop last_name.
I don't bother to rename the column to name, I just leave it as first_name.
Commit #3:
My boss says "Just kidding we want two columns again, but we want to add a third column with the middle name" so I write another SQL migration script to parse out the two columns, I create last_name again and copy its data back in, I parse out the middle name if it exists and put it in a middle_name column (new). After this, I update first_name so that it only includes the actual first name.
Now, if I was doing a SQL compare between Commit 1 to Commit 3, it would only show the addition of the third column. Further, it would have no idea that I actually populated that column through a series of business logic steps.
Does the upgrade handle this by step-by-stepping through the migration scripts to get to the Commit #3 version or does the source control just see the differences and alter the schema accordingly?
So far, I like what I see, but we have some specific requirements around how it all integrates with our current process.
Question #1) How can I dynamically have a newly deployed DB from source control have a different login/password.
The requirement is because we may have 5 copies of the same database on our QA DB server, but each DB should have it's own login/password. historically, we've just used the same string to construct the schema and user as well, but this isn't as strict of a requirement as moving to a standard schema per database wouldn't be bad (and is probably a good thing to do overall).
Question #2 - long question, more of a general "how does SC work for Redgate specicially sort of thing:
How do migrations get merged with SC changes. Specifically, how would red gate handle the following situation:
Commit #1:
create table schema.db_table_name
id bigint not null identity(1,1),
first_name varchar(64),
last_name varchar(64)
Time passes, data is added to table from our application...
Commit #2:
I want to merge the two columns, so I write SQL to copy and concatenate the last_name at the end of first_name.
Next statement, I drop last_name.
I don't bother to rename the column to name, I just leave it as first_name.
Commit #3:
My boss says "Just kidding we want two columns again, but we want to add a third column with the middle name" so I write another SQL migration script to parse out the two columns, I create last_name again and copy its data back in, I parse out the middle name if it exists and put it in a middle_name column (new). After this, I update first_name so that it only includes the actual first name.
Now, if I was doing a SQL compare between Commit 1 to Commit 3, it would only show the addition of the third column. Further, it would have no idea that I actually populated that column through a series of business logic steps.
Does the upgrade handle this by step-by-stepping through the migration scripts to get to the Commit #3 version or does the source control just see the differences and alter the schema accordingly?
Comments
http://www.red-gate.com/supportcenter/C ... Migrations
Writing a custom migration script sounds like it should be what you need, so I'm glad you found the documentation.
With regards to the first question, SQL Source Control doesn't create the database for you, so you could create the user when you create the new database that you deploy to. If that's not ideal you could set up a migration script to create the new user when you deploy to revision 0. A new blank database will be assumed to be revision 0.