November 17, 2008 at 6:09 am
In our company there are 2 camps regarding the physical approach to SQL development.
In one camp, we have development, test and production servers. All SQL development is against the development instance. We usually set up the development and test as instances on the same server. The argument in favor of this approach is that the server based versions are more likely to be consistent in configuration, making the reaction to a move to test or production more predictable. Of course, it also entails the expense of a server and a full SQL license for development.
In the other camp, all SQL development is done on desktops using the Developer Edition. The argument in favor of this is that the licenses are cheaper and so is the hardware. The disadvantage is that there is no forced consistency with test and/or production relative to settings and development in isolation never results in developers having to compete with other applications for SQL resources, like their code will in the real world.
How does you organization handle the physical development infrastructure and why? What are the pros and cons of your approach?
Secondly, one camp relies on SSMS exclusively for development. The other camp develops database projects exclusively in Visual Studio. There are some issues with how the two environments store content in SourceSafe which causes loss of the ability to compare versions and see real changes rather than formatting shifts. What is your choice for IDE and why?
------------
Buy the ticket, take the ride. -- Hunter S. Thompson
November 17, 2008 at 7:39 am
Lots to add here.
First, on the licensing - you can install the Development Edition of MSSQL on a server as long as everyone connecting to it has a developer or MSDN license. It is pretty common to do this. MS is reasonably flexible in their development licensing. The issue is more likely the "test" environment, which is probably going to be accessed by users without a development license - as soon as you do this, you need a full license of MSSQL. This is true even if you have the developer edition installed on a developer PC, if a user without a development license connects to this "server", you have violated the licensing agreement.
On the "develop on the desktop" subject. I use virtual PC to keep an image of a complete server available for myself and the other developers here. This works great because the image can be updated and managed in one place, but a developer gets their own version of the server to modify if they need to.
As far as source control, since we use MSSQL and Oracle, we have some additional issues to deal with, but we have pretty much abandoned trying to use any integrated source control for database version management. Nothing really seems to work all that well. Using Visual Studio for all of the MSSQL database changes to get the integrated source control does not seem to work, using the solution / source control for Management Studio (as you have already noticed) is a joke. So we deal with change scripts and a difference generator (Red Gate SQL Compare works great, but we use one of the competing packages). This allows us to script schema and data (for small "lookup" tables) and drop it into source control manually.
November 17, 2008 at 7:50 am
Michael,
Thanks for the insight. In both camps, we have MSSQL fully licensed in test. That part is common. The disagreement comes in development. The developer edition solution you propose is one I'm going to pursue.
------------
Buy the ticket, take the ride. -- Hunter S. Thompson
November 17, 2008 at 9:23 am
First, on the licensing - you can install the Development Edition of MSSQL on a server as long as everyone connecting to it has a developer or MSDN license. It is pretty common to do this. MS is reasonably flexible in their development licensing. The issue is more likely the "test" environment, which is probably going to be accessed by users without a development license - as soon as you do this, you need a full license of MSSQL. This is true even if you have the developer edition installed on a developer PC, if a user without a development license connects to this "server", you have violated the licensing agreement.
Not completely true. In a Test/User Acceptance environment, users accessing the system for testing only, as long as they CAN NOT update any code/database objects, you can still use the Developer Edition even if the users do not have a Developer Edition license. You need to move to Express/Work Group/Standard/Enterprise Edition for a production environment.
As for development on local PC's versus dedicated development servers, I'd prefer to see both. Initial development on a local PC development environment where the developer has full control over the environment. He can trash things without worrying about impacting other developers work. If the developer needs to restore a database, he (or she) does not need to coordinate it with the other developers.
The dedicated development server comes into play when the developer needs to start integrating his (or her) part with the work of the other developers. Here is where coordination between developers starts and continues on through test, uat, and final deployment to production.
November 17, 2008 at 10:48 am
Lynn,
Thanks for the feedback. I hadn't considered the mixed-mode development model. It could be a more palatable middle ground and a way to reign in totally uncontrolled development.
------------
Buy the ticket, take the ride. -- Hunter S. Thompson
November 17, 2008 at 4:41 pm
I've tried two things. One, give each developer their own schema on a central server and then "change object owner" when things move to the point where we want them integrated. I've also done the desktop thing.
In general, if the DBA can control things, I prefer a central server for changes, especially if it means compromising on data sizes and things with desktop editions, but with disk cheap, it starts to make some sense to let developers build an environment and have control. Then they send scripts to one person to consolidate onto a test server of some sort.
The issues with desktops is then getting the other develop objects back down to them. You can backup and restore, but often you need good developers that make sure they know what's "in flight" on their machines and can easily add those things back in after a restore.
Alternatively, you can let them do limited development on their desktop and really delpoy most of their objects to the central server. This keeps thinng in sync better. The downside is you really need one person that can control deployment and run all scripts. If you don't have a dedicated person, I'd rotate this job, and make sure that only that person can create objects on their week, just to keep control. You can have them also be keeping track of what's in flight in the VCS system as well, meaning what's changed that week.
November 18, 2008 at 6:05 am
Steve,
As always, thanks for the input.
I'm in the server camp. We have operated this way for years. The developers have enough control over the database server to do what they need to in order to get the project done. But the development server is, by definition, configured to match test and production as much as possible...disk configuration, maintenance scheduling, backups, security settings, et cetera. We have very few issues shifting code or entire databases to production because of this.
My concern with the desktop model is exactly that...since the desktop is completely under the control of the developer, the developer is likely working in an environment that does not match the target servers. (Hmmm, seem similar to the BYOC editorial?) How much additional work can/will that entail when it comes time to promote to a server, and is that time worth the savings in licensing/hardware? There aren't enough of us as it is, and ensuring difficulty in the promotion of code does not strike me as a cost-effective way of managing resources. Besides, in many cases, the licenses and servers are sunk costs. Purchasing desktop licenses represents additional cost.
------------
Buy the ticket, take the ride. -- Hunter S. Thompson
November 18, 2008 at 7:16 am
I would agree when the Developer Edition of SQL Server was more expensive. With the Developer Edition only costing $50.oo, and it can be used in the development, test, and user acceptance/qa environments; and the only licenses you need are for the developers and those few others that may make changes to the database objects/code, the cost of licensing is much lower today on the development side of the house.
I personally like having a development database local. I can try things there that could impact the work of others without worry, and (as happens where I work) if there are any network problems, they won't affect me while using the local copy of the database.
November 18, 2008 at 6:09 pm
Either way has pros and cons, and I have seen both. I don't have a problem with developers having a local copy to work with and test against. It can make development move faster, and gives them some flexibility. And if they work closely with the db development ( some do) it works well.
But, in either case, you need to control the testing and staging environments, so that what evenually goes prod is consistent and tested.
So, focus on the testing/staging area.
The more you are prepared, the less you need it.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply