SQLServerCentral / Discuss Content Posted by Grant Fritchey / Article Discussions / Article Discussions by Author / Multi-Environment Deployments Using Team Edition for Database Professi / Latest Posts

RE: Multi-Environment Deployments Using Team Edition for Database Professi

Grant Fritchey — Tue, 18 Aug 2009 12:15:29 GMT

While I realize that good carpenters don't blame their tools & all that... but when a carpenter is being told he has to drive nails with a coping saw... can he complain about it then? I'd say yes.This is the same situation. Yes, the tool can sort of do what you need, but not terribly well, and it's not it's primary purpose, and the gap between it's purpose and the use you're putting it to is pretty large.Good luck with the coping saw.

RE: Multi-Environment Deployments Using Team Edition for Database Professi

Scott Abrants — Tue, 18 Aug 2009 06:34:32 GMT

Hey there Grant! Thank you for the reply and pointing to the DBPro forum - I will give that as shot as well.This is a show stopper, to some degree. There is a preconcieved notion that the modeling tool should drive all schema changes. Aside from the fact of domains and a nice picture it is really compounding the problems with deployments. Not to say the modeling tools are to blame for deployment issues, but this is standing in the way of resolving some common build and deployment problems. And the worse part is there are better ways and one of them is implementing Declarative Database Development using Team Edition for Database Professionals.

RE: Multi-Environment Deployments Using Team Edition for Database Professi

Grant Fritchey — Mon, 17 Aug 2009 19:59:31 GMT

SCOTT!That's a really hard problem, as I'm sure you're aware. I don't have a good answer. You should start a new thread. You might also consider posting that question to [url=http://social.msdn.microsoft.com/Forums/en-US/vstsdb/threads]Social.msdn.microsoft[/url], the DBPro forum.I am aware of another team that's trying to solve the problem of managing databases through ERStudio. They're having a very hard time of it too. I just think it's a poor choice of tool for managing deployments. Embarcadero tools, damned fine tools, are built to support multiple environments, but they don't support any single environment, such as SQL Server, terribly well.

RE: Multi-Environment Deployments Using Team Edition for Database Professi

Scott Abrants — Mon, 17 Aug 2009 14:12:32 GMT

Not sure if this is worthy of its own discussion thread but I will start here...I am looking for some compelling reasons and or real world examples where someone has conquered the question "How do you support using Team Edition For Database Professionals for database development and at the same time the corporate environment insists on managing the schema in a data modeling tool such as ERStudio or Erwin?" This has become a major bone of contention and I have been unable to spend ample time getting DBPro and external modeling tools to work seamlessly together. My goal is to move the organization closer to Declarative Database Development (as outlined here in this wonderful blog http://blogs.msdn.com/gertd/archive/2009/06/05/declarative-database-development.aspx?CommentPosted=true).Also just so everyone knows the answer cannot be just simply reverse engineer back into the modeling tool because the push again is to drive the shema (only tables) from the modeling tools.

RE: Multi-Environment Deployments Using Team Edition for Database Professi

tymberwyld — Wed, 20 Feb 2008 04:58:56 GMT

Based on what's been said here, I'm kinda thinking that the older DB Projects were better. I've had in place now a methodology of managing scripts with specific prototypes for each object type (Table, Proc, etc.) that has helped in maintaining one single Project per database that could either script the entire database or upgrade it no matter what "version" the schema was in. One script per object that would either CREATE or ALTER the object without having to worry about whether it existed or not. I don't want to lose that now.It seems that I might like this new project type for determining an existing schema and getting it into a project or doing a comparision of different schemas, but other than that, it doesn't seem worthwhile. Thanks for all the input!

RE: Multi-Environment Deployments Using Team Edition for Database Professi

Jamie Thomson — Mon, 11 Feb 2008 07:38:04 GMT

Yeah one small thing to add. Automating incremental deploys worries me because I would rather have a human being involved in that process because there are simply some things that a computer will not and cannot know.For instance, if a table column gets a name change then DBPro won't know this unless you do Refactor-->Rename from the Schema View. If DBPro doesn't know that is a rename then it will try and drop the existing column and if there's data in it then the deploy will abort. That's the sort of situation that requires a human being to sort out.-Jamie

RE: Multi-Environment Deployments Using Team Edition for Database Professi

Grant Fritchey — Mon, 11 Feb 2008 06:25:24 GMT

[quote][b]tymberwyld (2/10/2008)[/b][hr][quote][b]Grant Fritchey (2/7/2008)[/b][hr]Nope. The one issue that we've had, and believe me the developers have howled, is that they can not simply add procedures (or drop them either). They have to let the dba team know that new procedures exist. We can pick them up from source control and add them. It actually works out, for us, because it provides us with a pretty easy mechanism to identify the procedures that need a review.[/quote]So are you saying that developers still need to go into the Database and create new procedures and then let you know they're there? Or are you saying they add them to the Project and then you "Get Latest Version" and the scripts are there?[/quote]Everything Jamie said is correct. I'll just add this. Any changes made in source control to an object, which is how we work, that is already a part of the project can be taken into the project with a simple "Get Latest Version" or get by label or get by version number, whatever. When the developers are creating a new procedure, we have them create the proc and check it into source control, so that it is protected. They then have to ask someone with a VSTS DB license to add that file to the project. We simply do a get and add the existing file to the project. We're still working on getting them to format the code correctly (too many just take the SSMS generated proc and check it in), but it's a pretty painless process.Currently, most of our development is on new systems, so we're recreating the database with each deployment. More and more existing systems are coming on line in DBPro and we're still working out how best to deal with incremental builds. We're trying to stay away from using the compare utility for deployments. Once we get it quantified, I will document it for an article here, but we're still in the early days at this point. So, we're trying a different approach than the one Jamie is advocating. His isn't right and ours isn't wrong, or vice versa, they're just different. Who knows, we may find that's the only way to go. But right now we've still got hope for the incremental process.

RE: Multi-Environment Deployments Using Team Edition for Database Professi

Jamie Thomson — Mon, 11 Feb 2008 01:49:41 GMT

[quote][b]tymberwyld (2/10/2008)[/b][hr]Ok, so last post (I hope). Would someone tell me how to simply add a Stored Procedure script to an existing project and manage it within the project (which includes scripting it to the dev database as many times as needed)? [/quote]A few options:1. Copy and paste from wherever the sproc has been written2. The sproc is written in the dev database and gotten into the project using a schema compare3. The sproc is written in the datadude project and gotten into te dev database using a schema compare[quote][b]tymberwyld (2/10/2008)[/b][hr]Assuming only one Proc has changed, do you still have to perform a diff just to move the changes to say Staging / Production?[/quote]Presume by "diff" you mean a schema compare? If so, the answer is 'yes'. I don't see it as a problem that only one object had changed. Here's a tip for you: Don't close down the schema compare window when you have finished propogating changes to somewhere. If you leave it open you can just keep hitting refresh each time you need to propogate some changes and it remembers how you previously set it up i.e. It you don't have to go through setting things to be ignored because you've already done it. I fid this to be a huge time saver.Another tip: filter the results of the schema compare to show changes only (there's a filter button near the top left of the schema compare window)-Jamie

RE: Multi-Environment Deployments Using Team Edition for Database Professi

tymberwyld — Sun, 10 Feb 2008 12:32:01 GMT

Ok, so last post (I hope). Would someone tell me how to simply add a Stored Procedure script to an existing project and manage it within the project (which includes scripting it to the dev database as many times as needed)? Assuming only one Proc has changed, do you still have to perform a diff just to move the changes to say Staging / Production?

RE: Multi-Environment Deployments Using Team Edition for Database Professi

Jamie Thomson — Sun, 10 Feb 2008 09:35:16 GMT

RE: Multi-Environment Deployments Using Team Edition for Database Professi

tymberwyld — Sun, 10 Feb 2008 08:20:34 GMT

[quote][b]Grant Fritchey (2/7/2008)[/b][hr]Nope. The one issue that we've had, and believe me the developers have howled, is that they can not simply add procedures (or drop them either). They have to let the dba team know that new procedures exist. We can pick them up from source control and add them. It actually works out, for us, because it provides us with a pretty easy mechanism to identify the procedures that need a review.[/quote]So are you saying that developers still need to go into the Database and create new procedures and then let you know they're there? Or are you saying they add them to the Project and then you "Get Latest Version" and the scripts are there?

RE: Multi-Environment Deployments Using Team Edition for Database Professi

Matt Miller (#4) — Thu, 07 Feb 2008 12:32:47 GMT

Oh I know - I'm not even going to comment on the cost-appropriateness of those licenses (especially the Volume licensing.) I was just noticing the one feature you can just about always count on working in a MS product (the licensing logic limitations...)I've been blessed with having the use of one (VSTS license), but let's just say it took some serious arm-twisting to get there.Let's just part by saying - it was the only time I ever heard a CFO use the phrase "smoking crack"...

RE: Multi-Environment Deployments Using Team Edition for Database Professi

Grant Fritchey — Thu, 07 Feb 2008 12:17:25 GMT

True, but have you seen the Suite license cost per seat? We'll be adding procedures for the developers for some time to come.

RE: Multi-Environment Deployments Using Team Edition for Database Professi

Matt Miller (#4) — Thu, 07 Feb 2008 11:40:53 GMT

Oh - entirely different problem. I see where we're heading now....But I guess that's so that you can't backdoor your way into having all of the advantages of DBPro without actually licensing DBPro for all interested parties....:)If they ARE licensed for the entire team suite though - they should load the VSTS for DB add-on. It should give them permission to update the project then....

RE: Multi-Environment Deployments Using Team Edition for Database Professi

Grant Fritchey — Thu, 07 Feb 2008 11:34:00 GMT

Oh yeah, you can have the project deploy or drop objects, yes. It's getting objects into the project (or out) that is the problem. My developers all have the Developers Edition of the Team Suite. I've got the Database Edition of the Team Suite. When you create a database project from DBPro, it can't be modified by the Developer's Edition.

RE: Multi-Environment Deployments Using Team Edition for Database Professi

Matt Miller (#4) — Thu, 07 Feb 2008 11:25:49 GMT

hmm - I could swear I saw something under deploy options to drop objects from the server that don't exist in the database project. I will have to go looking for that.

RE: Multi-Environment Deployments Using Team Edition for Database Professi

Grant Fritchey — Thu, 07 Feb 2008 08:40:55 GMT

Nope. The one issue that we've had, and believe me the developers have howled, is that they can not simply add procedures (or drop them either). They have to let the dba team know that new procedures exist. We can pick them up from source control and add them. It actually works out, for us, because it provides us with a pretty easy mechanism to identify the procedures that need a review.

RE: Multi-Environment Deployments Using Team Edition for Database Professi

tymberwyld — Thu, 07 Feb 2008 08:15:10 GMT

Hmm, I just remembered one of the reasons why I wasn't liking this Team Edition for DB. One of the things we want to do is have the Developers manage the scripts for their particular Procedures. Because the Procs scripts would be in Source Safe (or in this case TFS) it would be impossible for two developers to overwrite each others changes for the same proc (which can potentially happen now with them just going into the database).I couldn't find a way in this new edition to simply modify one script file and then run it against a Dev server instance. The scripts are all created as "CREATE" scripts so they wouldn just error out when run multiple times.Has anyone found a way to do this and still use the deployment tools?

RE: Multi-Environment Deployments Using Team Edition for Database Professi

tymberwyld — Wed, 06 Feb 2008 20:08:36 GMT

Thanks for the info. I guess I just need to let go and trust the "tools".

RE: Multi-Environment Deployments Using Team Edition for Database Professi

Scott Abrants — Mon, 04 Feb 2008 07:06:28 GMT

I really believe the biggest benefit is the ability to check and validate objects on the fly. This will point out errors with the changes as you make them; this is nice. As far as the build in schema compare and data compare Red Gate does a FAR better job at this. Of course you have to pay for those tools but the added cost is worth it. DBPro does not allow you, from what I can see, the ability to set up a reusable definition of your comparison projects something that Red Gate does nicely.

RE: Multi-Environment Deployments Using Team Edition for Database Professi

Jamie Thomson — Mon, 04 Feb 2008 06:31:13 GMT

[quote][b]tymberwyld (2/4/2008)[/b][hr]Hey guys. I've started to try and evaluate the VSDB and I wasn't really impressed. I'm so used to having more control over how things are structed by using a simple "Database Project" (which comes with any version of VS Pro and above). When I attempted to put all the "Login" scripts into one file, VS just barked at me and said I didn't know what I was doing. Also, in some cases it's nice to add a Sub-folder to my Procs folder to categorize the Procedures (because we tend to have 200+ Procs). Were currently pushing Developers to use the Projects instead of going into the databases directly so that more can be controlled using VSS (yes, I don't like it either but it works). Then, once they've signed-off on the Scripts, us DBAs evaluate and deploy them.As far as deploying it to multiple servers, that's always been easy. Just setup multiple Connections to the DB Project and depending on the User's rights (we use NT Groups here) they either can or can't script the objects on that Server.I'm just trying to get some "Pros" of using this because I haven't seen one yet.[/quote]tymberwyld,I could give you a fairly sizable list. Top of that list would probably (for me) be the Schema Compare and Data Compare features. I love the ability to easily sync up different environments against each other or against your project. Hugely valuable.-Jamie

RE: Multi-Environment Deployments Using Team Edition for Database Professi

Grant Fritchey — Mon, 04 Feb 2008 06:19:11 GMT

Automatic verification of changes to the scripts as you save them is a pretty large advantage. Refactoring object names is great. The Static Code Analysis is getting a lot more use. Using this tool & MSBuild we've been able to automate our deployments in a way that just wasn't possible just using the old style DB projects. You do have to buy into the tools approach in order to make it work well. We've found that managing all the scripts individually, rather than in groups, has its advantages too. Still, it's not for everyone.

RE: Multi-Environment Deployments Using Team Edition for Database Professi

tymberwyld — Mon, 04 Feb 2008 05:59:36 GMT

Hey guys. I've started to try and evaluate the VSDB and I wasn't really impressed. I'm so used to having more control over how things are structed by using a simple "Database Project" (which comes with any version of VS Pro and above). When I attempted to put all the "Login" scripts into one file, VS just barked at me and said I didn't know what I was doing. Also, in some cases it's nice to add a Sub-folder to my Procs folder to categorize the Procedures (because we tend to have 200+ Procs). Were currently pushing Developers to use the Projects instead of going into the databases directly so that more can be controlled using VSS (yes, I don't like it either but it works). Then, once they've signed-off on the Scripts, us DBAs evaluate and deploy them.As far as deploying it to multiple servers, that's always been easy. Just setup multiple Connections to the DB Project and depending on the User's rights (we use NT Groups here) they either can or can't script the objects on that Server.I'm just trying to get some "Pros" of using this because I haven't seen one yet.

RE: Multi-Environment Deployments Using Team Edition for Database Professi

Jamie Thomson — Thu, 24 Jan 2008 08:12:49 GMT

[quote][b]Grant Fritchey (1/24/2008)[/b][hr]We're using this tool more and more because it really is fantastic. But, because we use it more and more, all the little gotcha's get magnified.[/quote]Amen to that. It [url=http://blogs.conchango.com/jamiethomson/archive/2007/11/23/I_2700_m-loving-datadude.aspx]saves me buckets of time[/url] now that I know how to use it, but it took me a long time to get to that point.-Jamie

RE: Multi-Environment Deployments Using Team Edition for Database Professi

Grant Fritchey — Thu, 24 Jan 2008 07:45:29 GMT

Holy cow. We've been lucky so far then. We've been naming the project after the database. What a mess.With the initial release, I did find that we were hacking the XML, but after the first service pack, we haven't had to do that. Visual Studio provides a mechanism for creating more configurations with the Configuration Manager. Just don't get caught by the fact that there is a Solution Configuration and a Project Configuration. Until we had that seperation clear in our heads we ran into all kinds of problems. I haven't seen the 2008 version yet, but if it fixes the .user issue, I'd love to know. That'd give me a fantastic excuse to upgrade. I'm sure they'll address this soon. I'm pretty sure they're aware of it. If I recall correctly, and hopefully no one will beat me if I don't, I asked Gert Drapers about the .user file and he acknowledged that they shouldn't have stored the connection strings there and would probably be fixing it in the future. That was at PASS last year.We're using this tool more and more because it really is fantastic. But, because we use it more and more, all the little gotcha's get magnified.

RE: Multi-Environment Deployments Using Team Edition for Database Professi

Scott Abrants — Thu, 24 Jan 2008 07:36:46 GMT

Our solution gets us around having to hack the XML definition file; which is a good thing because not everyone here could handle that , nor should they.

RE: Multi-Environment Deployments Using Team Edition for Database Professi

Jamie Thomson — Thu, 24 Jan 2008 07:27:17 GMT

I wish there was a definitive answer here to the question "user file or dbproj file?". I use DBPro A LOT but I don't use configurations...after reading this article I'm think that I should.I see the following in my .dbproj file: PropertyGroup Condition=" '$(Configuration)' == 'Default' "(I would put the full XML in here but as soon as I add some angle brackets it doesn't show up in the preview window for some reason)so I kinda [i]assume [/i]that it should be possible to define the other configurations in there. I don't know though.I generally find that in this version hacking the XML seems unavoidable at times (for instance, there's [url=http://blogs.conchango.com/jamiethomson/archive/2007/01/19/VSTS4DBP_3A00_-Database-name-does-not-get-included-in-TFS-builds.aspx]no way to define the target database[/url] name except in the .user files)Thanks again.-Jamie

RE: Multi-Environment Deployments Using Team Edition for Database Professi

Scott Abrants — Thu, 24 Jan 2008 07:15:03 GMT

Oh yes and nice article! Keep up the good work and thank you for the plug.

RE: Multi-Environment Deployments Using Team Edition for Database Professi

Scott Abrants — Thu, 24 Jan 2008 06:43:32 GMT

One catch you also need to add this file to the project - as Grant stated in his article - and not shown in the replies.If anyone else has a better suggestion I would LOVE to hear it. The alternative works fine if no one else on your team ever has to open your project. Without that file and the contents you would have to recreate all of the settings again for a project that is checked into a central source control.

RE: Multi-Environment Deployments Using Team Edition for Database Professi

Grant Fritchey — Thu, 24 Jan 2008 05:55:17 GMT

I can't take credit for it. One of the guys in my shop, Scott Abrants, is one of those obsesive compulsive types who has to find the deep dark secret behind everything. We found that when we were opening each other's projects, the database connection settings kept going away. At first there was recriminations & finger-pointing, but when it happened to Scott, he went and found where the connection was being stored. It's in the .user file. He did a bit more experimentation and sure enough, we can check that file into TFS, and then get it to each local machine and the connection strings for each configuration travel with it.I'd sure be open to any other methods that work better and don't involve mucking about with files that normally remain hidden, but it's what we have.I'm glad you guys liked the article. We're currently working through the best way to get incremental deployments working and automated, so I may have another one in six or eight weeks.

RE: Multi-Environment Deployments Using Team Edition for Database Professi

Matt Miller (#4) — Wed, 23 Jan 2008 23:03:39 GMT

I was a bit curious about that too, but I'm seeing what Grant mentioned: when you add the separate configurations, some amount of that info seems to be put directly into the .dbproj file, and some more seems to go into the .dbproj.user file.Some of it seemed to be duplicative, but I can't say I saw everything that was in the .user, duplicated in the .dbproj. I don't know what the best practice is as the moment (I just really started dabbling with this version), but it does seem to be the DEFAULT setting. The UI seems to put those there, and I don't see any way to change where that info might get stored.For example - I can't find hide nor hair of the designated deployment DB connection string anywhere else, and I'd have to agree with Grant that that in itself would be something you'd want to set and keep with the project.Perhaps ask Jamie what the best course of action for those things might be...

RE: Multi-Environment Deployments Using Team Edition for Database Professi

Jamie Thomson — Wed, 23 Jan 2008 21:56:52 GMT

Good article Grant, I have one question though.You say "[i]you have to browse to the .user file and add it to your project and check it into source control[/i]"I was speaking to Jamie Laflen from the DB Pro dev team once and he had kittens at the very suggestion of checking in the .user file. Do we have the option to put all the configurations into the .dbproj file and, if so, what is best practice here?Great article!-Jamie

Multi-Environment Deployments Using Team Edition for Database Professi

Grant Fritchey — Wed, 23 Jan 2008 21:54:20 GMT

Comments posted to this topic are about the item [B]Multi-Environment Deployments Using Team Edition for Database Professi[/B]