BorlandTalk.com Borland discussion newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  What is your database application development environment?          BorlandTalk.com Forum Index -> Delphi Non-Technical View previous topic :: View next topic   Author Message Kevin Frevert Guest Posted: Mon Mar 20, 2006 4:03 pm    Post subject: What is your database application development environment? We recently had a 'heated' discussion on our current database application development environment. It is currently.. Production - Test - Sandbox We develop in the sandbox, test it on a recent restored database, and if all goes well, the code goes to production. This environment has worked extremely well since MSSQL 6.5. Last week a developer accidentally copied a stored proc from the sandbox to production (skipping the test db) without checking to make sure the production sp was the same as what was in the sandbox (before he made the requested changes). Without getting into the politics, the <bleep> hit the fan and the 'powers-that-be' decided to 'fix' the problem by weekly production restores/overwrites of both the test and sandbox databases. We already had weekly restores of the test database, but we all felt the sandbox was exactly that, an area that let us 'play' and try out new things. Our sandbox never was, and never intended to be, a mirror of the production environment. The 'powers-that-be' said we would have no more of this 'cowboy development' around here and 'everyone else' does it his way (whoever 'everyone' is and whatever 'it' is). Most of the other developers with laptops also have a (smaller) copy of the database to do development in which we were told we also had to do weekly restores. I'll throw in the 'power-that-be' has zero database administration experience. What was wrong with the way we were doing things? Is this how other shops do database app development? What is your database application development environment? Any links/articles with advice is also appreciated. krf Back to top Paul Nichols (TeamB) Guest Posted: Mon Mar 20, 2006 4:03 pm    Post subject: Re: What is your database application development environmen Kevin Frevert wrote: Quote: We recently had a 'heated' discussion on our current database application development environment. It is currently.. Production - Test - Sandbox Most call it Dev, Test-QA, Production, but whatever you call it, this is the norm. Quote: We develop in the sandbox, test it on a recent restored database, and if all goes well, the code goes to production. This environment has worked extremely well since MSSQL 6.5. Most of the time, Dev databases are much smaller. Many Test or QA databases contain a recent snapshot of Production. Quote: Last week a developer accidentally copied a stored proc from the sandbox to production (skipping the test db) without checking to make sure the production sp was the same as what was in the sandbox (before he made the requested changes). Without getting into the politics, the <bleep> hit the fan and the 'powers-that-be' decided to 'fix' the problem by weekly production restores/overwrites of both the test and sandbox databases. Well that too, would be the norm. Quote: We already had weekly restores of the test database, but we all felt the sandbox was exactly that, an area that let us 'play' and try out new things. Our sandbox never was, and never intended to be, a mirror of the production environment. Usually that is the case. Something like SPs however, should be tested before they are promoted to Test or QA Quote: The 'powers-that-be' said we would have no more of this 'cowboy development' around here and 'everyone else' does it his way (whoever 'everyone' is and whatever 'it' is). Most of the other developers with laptops also have a (smaller) copy of the database to do development in which we were told we also had to do weekly restores. I'll throw in the 'power-that-be' has zero database administration experience. That sounds like overkill, but be thankful at least they understand that there should be a promotion process and some manner of Change Control. Not bad, but pushing Prod to Laptops definitely is a little extreme. Quote: What was wrong with the way we were doing things? Is this how other shops do database app development? What is your database application development environment? Ideally, no developer should have access to Prod. That is a no-no, even though far too many companies have no formal Change Control processes in place. I shudder to think what a real Enterprise would do, if developers were free to promote to production and kill half of the systems by an inadvertent change. Not a good idea for Developers to promote their own code at all. I would absolutely agree with the "powers that be" on this one. Quote: Any links/articles with advice is also appreciated. No links, but having been in several large fortune 1000 and 500 companies, as R&D Team Lead and Architect, I can tell you that 90% would not let a developer near production. Development should end at Test. Promotion should be done by a Change control and security team, only after a thorough QA and UAT process has been performed. These promotion/QA teams may require a code review and code walkthrough at the QA level as well. Although developers working in small companies may think this odd, when you have hundreds of programs that the company MUST depend upon from CMS to BPM to Financials to HR, the last thing you want is for roque code to bring down a system. I would not look at this at losing control or power, but rather as protecting your posterior. I would feel much better if during the testing process, some bug was found rather than discovering that some bug in the production environment affected some HR process and 1000 people did not get paid, or were paid too much/not enough. You can bet your buns would get burned and overtime is something you may not want to complain about. It is far better to let someone else take responsibility. Bugs are going to happen and occur and no test plan is perfect, but the practice of eliminating as many bugs as possible, before they can affect entire systems, is worth two pounds of cure. You may want to express your concern on trying to install huge databases on development environments, but to make sure that no code is promoed before its time, is a good idea. Sounds like you have a pretty good company to work for there, overall. Back to top Paul Nichols (TeamB) Guest Posted: Mon Mar 20, 2006 4:03 pm    Post subject: Re: What is your database application development environmen Jon Robertson wrote: Quote: Paul Nichols (TeamB) wrote: Most of the time, Dev databases are much smaller. Many Test or QA databases contain a recent snapshot of Production. For several years, our dev databases were much smaller. Then we'd be bit because our app performed much slower in production than it did during development. Today, our dev database is pretty big. Probably bigger than 75% of our customer's production databases. It can be a little cumbersome at times. But we identify performance issues early during the development process, instead of after the product release. :) Tis always OK if you do more dev testing before promoting to QA. What we will normally do, is use a small dev database for initial development, then switch to the Test or QA database for optimization, even before we promote to QA. Running explains helps a great deal in query optimization. We usually have a query plan as well. Stored Procs usually go by a DBA before being pushed into QA. Nothing wrong with your approach, but I find most companies have much less hardware or CPU cycles available for dev. Not a good way to do true testing and load analysis on Dev boxes, IMHO. But I am not going to bad mouthed it either. Each company is different. Your company may have DEV boxes or CPU cycles that closely resemble QA and production. If so, you are probably close to following the model below. (BTW, I will never bad mouth thorough testing ). Some companies also have a Dev, Test, Staging, and Production environment. This is the ideal, though a little more costly for Hardware. Two companies I have worked for, had this type of environment and it was great. Developers had access to Dev and Test. QA and Prod were under CHange Control and Promotion Teams. If we have bugs in QA, it went back to Dev and Test, and the procedure began anew. Good version control is a must here, on all environments. So are roll back plans. Back to top Eric Grange Guest Posted: Mon Mar 20, 2006 5:03 pm    Post subject: Re: What is your database application development environmen Sounds to me that the solution wasn't a solution of the problem, but an "optimistic" way of reducing the probability of appearance of that issue. The actual issue was that Quote: Last week a developer accidentally copied a stored proc from the sandbox to production ....should have not been possible, as in "developpers don't even have access to the production database", or as in "you just can't go and alter or fiddle stuff in the production database *manually*". The way we implement database changes here is to make a small utility, which is basicly a nicely wrapped collection of: - SQL scripts that check for the presence/absence of DB structures - SQL scripts that alter the DB - SQL scripts that check alterations where actually applied with SQL limitation features: checking scripts f.i. are checked to be only of the "select" variety, you can't update or alter anything in a "check" SQL. Once executed, the utility leaves a manifest in the DB (version number, tags...) which subsequent utilities may check. This utility then gets tested by someone another developper on several production DB images, and is later applied by a third person to the production DBs (by a consultant, who's role also goes from making sure the developpers followed the procedure to notifying the customer of the DB change). So for a messup to happen, three persons have to messup. As for our dev DBs, they're basicly free-for-all and everything is fair game with them, some deviate for months before their deviations gets integrated into the production DBs (like when a new module with a bunch of new tables gets developped). Eric Back to top Bryce K. Nielsen Guest Posted: Mon Mar 20, 2006 6:03 pm    Post subject: Re: What is your database application development environmen Quote: We recently had a 'heated' discussion on our current database application development environment. It is currently.. Production - Test - Sandbox We tend to have one more in that mix: Production - Test - Integration - Sandbox(es) There's usually more than one Sandbox/Developer database floating around, and those are by all means "owned" by the Developers. Usually, the DBAs don't give the Developers any rights to Production systems, and limited rights to Test. The "Integration" database is still the Developer's database, and is effectively the same as "test" but that the testers don't normally jump into Integration, they just jump into Test. And Test/Integration are updated regularly from production. Sandboxes are updated whenever the developers feel like it. -BKN Back to top Oliver Townshend Guest Posted: Mon Mar 20, 2006 11:03 pm    Post subject: Re: What is your database application development environmen Quote: What was wrong with the way we were doing things? Is this how other shops do database app development? What is your database application development environment? Well personally I wonder why the developer has the rights to put a stored proc into production. I would have thought that you would have a deployment person who would be in charge of installing packages of software to the production database. The state of the sandbox is irrelevant if that is the case, although personally I don't understand why anyone would want to develop against an out of date database. Refresh once a month IMO. Oliver Townshend Back to top Robin Guest Posted: Mon Mar 20, 2006 11:03 pm    Post subject: Re: What is your database application development environmen Kevin Frevert wrote: Quote: We recently had a 'heated' discussion on our current database application development environment. It is currently.. Production - Test - Sandbox Test? What do you mean test? It compiles doesn't it? <g> -- Robin. "He who treats the database as a flat-file repository of data is doomed to burn in Hell. It's true, I asked." - Dratz Back to top Andy Gibson Guest Posted: Tue Mar 21, 2006 5:03 am    Post subject: Re: What is your database application development environmen Kevin Frevert wrote: Quote: We recently had a 'heated' discussion on our current database application development environment. It is currently.. Production - Test - Sandbox We develop in the sandbox, test it on a recent restored database, and if all goes well, the code goes to production. This environment has worked extremely well since MSSQL 6.5. Another 'us too' We have dev, test and prod, with an additional training db. Only the DBA, and a couple of programmers have admin access to the production DB, most don't have admin access to test. Develop in Dev, move to test, and once tested move to prod. We use scripts to move from dev->test-> products. As for restoring dev, on one hand, it's good to have a recent version of production in there since it gives you realistic data to work on. OTOH, it can be nightmare synching it up without losing the most recent work in the dev database. It seems like the problem was caused by a developer moving stuff to production directly, which could have been avoided by not giving production admin rights to anyone except the DBA, and one or two select developers as a backup to the DBA. Cheers, Andy Gibson Back to top Jon Robertson Guest Posted: Tue Mar 21, 2006 5:03 am    Post subject: Re: What is your database application development environmen Paul Nichols (TeamB) wrote: Quote: Most of the time, Dev databases are much smaller. Many Test or QA databases contain a recent snapshot of Production. For several years, our dev databases were much smaller. Then we'd be bit because our app performed much slower in production than it did during development. Today, our dev database is pretty big. Probably bigger than 75% of our customer's production databases. It can be a little cumbersome at times. But we identify performance issues early during the development process, instead of after the product release. :) -- Jon Robertson Borland Certified Advanced Delphi 7 Developer MedEvolve, Inc http://www.medevolve.com Back to top Andy Gibson Guest Posted: Tue Mar 21, 2006 5:03 am    Post subject: Re: What is your database application development environmen Oliver Townshend wrote: Quote: I don't understand why anyone would want to develop against an out of date database. Refresh once a month IMO. Me either except that development is ongoing while testing is completed. We have problems with refreshing dev since it can overwrite the most recent development work. Andy Gibson Back to top Jon Robertson Guest Posted: Tue Mar 21, 2006 6:03 am    Post subject: Re: What is your database application development environmen Paul Nichols (TeamB) wrote: Quote: What we will normally do, is use a small dev database for initial development, then switch to the Test or QA database for optimization, even before we promote to QA. Initially, both our Dev and QA processes used smaller databases. Then we switched QA to a larger database. We still found that we were doing considerable amounts of rework that could have been avoided if we'd developed better code initially. Quote: Stored Procs usually go by a DBA before being pushed into QA. What's a DBA? Oh wait, I think they want me to be the DBA. Our company has had 3 - 5 developers for the past seven years. For a while, one of those developers was focused on SQL Server and learning everything he could. He was moving away from development and toward more of a DBA role/expertise. After he was laid off, the database responsibility fell to me. I wish we had a dedicated DBA. But for our small team, we all do a little of everything. Quote: Nothing wrong with your approach, but I find most companies have much less hardware or CPU cycles available for dev. We're an ISV. We do write some small tools for in-house use. But our primary development is on a commercial product that we sell. Our development machines and servers are the most powerful computers in the office. Quote: Your company may have DEV boxes or CPU cycles that closely resemble QA and production. Unfortunately, our dev boxes are still only a faction of what our customers are using for production servers. Except for the new one, which should get here any day now. It's a dual proc, with hyperthreading, 2.8 Ghz and 4GB. I can't wait... Quote: Some companies also have a Dev, Test, Staging, and Production environment. I think that meets our environment. Our Dev environment is our local development workstations, each running SQL Server Developer. We have a shared Test server that is shared between QA and Dev. We have an isolated Test server that's on a separate server/domain, and only QA has access to update/modify the server. We have a production server for each version of our software that we support (currently three versions). Our support team uses the production "servers". However, they're actually virtual machines running on a single server. So our "production" environment is the slowest of them all. Quote: QA and Prod were under CHange Control and Promotion Teams. Right now, I play a round-robin role. I have the final say on our database schema, although each developer writes their own DDL and submits it to me. I'm the StarTeam/Change Control administrator, although I'm slowly transitioning the role to QA. And I'm a Senior Developer, primarily responsible for the architecture of the application. We all laugh when we go to conferences where speakers talk about the conflict between DBAs and developers. Ah, to have teams that size... Quote: If we have bugs in QA, it went back to Dev and Test, and the procedure began anew. Good version control is a must here, on all environments. So are roll back plans. This is how we're working today. We've come a long way. We actually have Modification Definitions approved by a review board. We actually track source code changes for each Modification Definition. QA actually tests the new functionality against the Modification Definition. We actually have a monthly code review of all new code checked in for the past month. We spend much less time coding these days. But we seem to be getting more done, closer to on time and on budget. Strange how that works... -- Jon Robertson Borland Certified Advanced Delphi 7 Developer MedEvolve, Inc http://www.medevolve.com Back to top Oliver Townshend Guest Posted: Tue Mar 21, 2006 6:03 am    Post subject: Re: What is your database application development environmen Quote: Me either except that development is ongoing while testing is completed. We have problems with refreshing dev since it can overwrite the most recent development work. Backup? Oliver Townshend Back to top Andy Gibson Guest Posted: Tue Mar 21, 2006 7:03 am    Post subject: Re: What is your database application development environmen Oliver Townshend wrote: Quote: Me either except that development is ongoing while testing is completed. We have problems with refreshing dev since it can overwrite the most recent development work. Backup? Well, there's always that option, it's all in version control anyway, but it's still a pain. Sometimes, the moons are all in alignment, and there is no new dev work when we do a new release, and we can do a straight refresh. Not seen that for a couple of years now. Cheers, Andy Back to top Murdoc Guest Posted: Tue Mar 21, 2006 10:03 pm    Post subject: Re: What is your database application development environmen Kevin Frevert wrote: Quote: We recently had a 'heated' discussion on our current database application development environment. It is currently.. Production - Test - Sandbox As we do, Devel -> Test -> Live. However, our 'Live' is sort of a 'Pending-Live', as all our changes are bundled together at release/patch time, then applied to the actual Live database(s). We also have a dedicated, and isolated network for system testing releases and patches. Quote: We develop in the sandbox, test it on a recent restored database, and if all goes well, the code goes to production. This environment has worked extremely well since MSSQL 6.5. Last week a developer accidentally copied a stored proc from the sandbox to production (skipping the test db) without checking to make sure the production sp was the same as what was in the sandbox (before he made the requested changes). Without getting into the politics, the <bleep> hit the fan and the 'powers-that-be' decided to 'fix' the problem by weekly production restores/overwrites of both the test and sandbox databases. We already had weekly restores of the test database, but we all felt the sandbox was exactly that, an area that let us 'play' and try out new things. Our sandbox never was, and never intended to be, a mirror of the production environment. The 'powers-that-be' said we would have no more of this 'cowboy development' around here and 'everyone else' does it his way (whoever 'everyone' is and whatever 'it' is). Most of the other developers with laptops also have a (smaller) copy of the database to do development in which we were told we also had to do weekly restores. I'll throw in the 'power-that-be' has zero database administration experience. What was wrong with the way we were doing things? Is this how other shops do database app development? What is your database application development environment? Our devel/test environments are (usually) never restored from Live. If necessary, we obtain a copy of the client's database, and do our development against that. That way (a) we have realistic figures and scenarios; and (b) we can do some realistic performance testing. Murdoc -- Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         BorlandTalk.com Forum Index -> Delphi Non-Technical All times are GMT Page 1 of 1   Jump to: Select a forum C++ Builder Newsgroups----------------C++ BuilderC++ Builder (ActiveX)C++ Builder (Announcements)C++ Builder (Command Line Tools)C++ Builder DatabasesC++ Builder Databases (ADO)C++ Builder Databases (Desktop)C++ Builder Databases (InterBase Express)C++ Builder Databases (Multi-Tier)C++ Builder Databases (SQL Servers)C++ Builder (Graphics)C++ Builder (IDE)C++ Builder (Internet)C++ Builder (Internet Socket)C++ Builder (Internet Web)C++ Builder (Jobs)C++ Builder (Language)C++ Builder (Language C++)C++ Builder (Compatibility with MFC)C++ Builder (Native API)C++ Builder (Non-Technical)C++ Builder (Students)C++ Builder (Third Party Tools)C++ Builder (Upgrade)C++ Builder (VCL)C++ Builder (VCL Components Usage)C++ Builder (VCL Components Development)C++ Builder (VCL Usage)C++ Builder (WinAPI)C++ Builder Installation Delphi Newsgroups----------------Delphi (General)Delphi ActiveX Controls UsageDelphi ActiveX Controls DevelopmentDelphi As400Delphi DatabasesDelphi Databases (ADO)Delphi Databases (dbExpress)Delphi Databases (Desktop)Delphi Databases (InterBase Express)Delphi Databases (Multi-Tier)Delphi Databases (SQL Servers)Delphi DeploymentDelphi GraphicsDelphi IDEDelphi InternationalizationDelphi InternetDelphi Internet Isapi-WebBrokerDelphi Internet WebSnapDelphi Internet WinsockDelphi JobsDelphi Language BASMDelphi Language ObjectPascalDelphi Model Driven Architecture (General)Delphi Model Driven Architecture (Third Party)Delphi MultimediaDelphi Native APIDelphi Non-TechnicalDelphi ObjectPascalDelphi OLE AutomationDelphi OO designDelphi OpenToolsAPIDelphi Reporting-ChartingDelphi RTLDelphi StudentsDelphi TeamSourceDelphi Thirdparty ToolsDelphi Thirdparty Tools (General)Delphi Thirdparty Tools (IntraWeb)Delphi Thirdparty Tools (RaveReports)Delphi UpgradeDelphi VCLDelphi VCL ComponentsDelphi VCL Components UsingDelphi VCL Components WritingDelphi WebServices SOAPDelphi WinAPIDelphi XMLDelphi Installationcomp.lang.pascal.delphi.components.misccomp.lang.pascal.delphi.components.usagecomp.lang.pascal.delphi.components.writingcomp.lang.pascal.delphi.databasescomp.lang.pascal.delphi.misccomp.sources.delphi JBuilder Newsgroups----------------JBuilderJBuilder AnnouncementsJBuilder Applet IssuesJBuilder As400JBuilder CompilerJBuilder CORBA/RMIJBuilder DatabasesJBuilder DB SwingJBuilder DebuggerJBuilder DeploymentJBuilder DocumentationJBuilder EnterpriseJBuilder HandheldJBuilder IDEJBuilder JavaJBuilder JavaAPIJBuilder Java LanguageJBuilder Java SwingJBuilder Java BeansJBuilder Java Beans UsageJBuilder Java Beans DevelopmentJBuilder JBCLJBuilder JDataStoreJBuilder JobsJBuilder LinuxJBuilder MacintoshJBuilder & MobileSetJBuilder MultiLingual AppsJBuilder Non-TechnicalJBuilder OpenToolsJBuilder Servlets (JSP)JBuilder StudentsJBuilder Team DevelopmentJBuilder Thirdparty ToolsJBuilder UpgradeJBuilder Installation InterBase Newsgroups----------------InterBaseInterBase Bug DiscussionInterBase GeneralInterBase IBConsoleInterBase OpenSourceInterBase ReplicationInterBase SQLInterBase ThirdpartyInterBase Installation Miscellaneous Borland Newsgroups----------------AppCenterAppServerEnteraVisiBroker  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.