February 7, 2008 at 8:15 am
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?
February 7, 2008 at 8:40 am
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.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 7, 2008 at 11:25 am
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.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 7, 2008 at 11:34 am
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.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 7, 2008 at 11:40 am
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....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 7, 2008 at 12:17 pm
True, but have you seen the Suite license cost per seat? We'll be adding procedures for the developers for some time to come.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 7, 2008 at 12:32 pm
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"...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 10, 2008 at 8:20 am
Grant Fritchey (2/7/2008)
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.
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?
February 10, 2008 at 9:35 am
tymberwyld (2/10/2008)
Grant Fritchey (2/7/2008)
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.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?
It depends. Either the developers have their own VSTS licence and can add stuff to the project themselves. Or, they write their code in some dev database somewhere and then someone with a VSTS licence uses Schema Compare to get the new/changed code into the project.
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
February 10, 2008 at 12:32 pm
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?
February 11, 2008 at 1:49 am
tymberwyld (2/10/2008)
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)?
A few options:
1. Copy and paste from wherever the sproc has been written
2. The sproc is written in the dev database and gotten into the project using a schema compare
3. The sproc is written in the datadude project and gotten into te dev database using a schema compare
tymberwyld (2/10/2008)
Assuming only one Proc has changed, do you still have to perform a diff just to move the changes to say Staging / Production?
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
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
February 11, 2008 at 6:25 am
tymberwyld (2/10/2008)
Grant Fritchey (2/7/2008)
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.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?
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.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 11, 2008 at 7:38 am
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
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
February 20, 2008 at 4:58 am
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!
August 17, 2009 at 2:12 pm
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.
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply