Placing a Development Database on the same SQL Server instance as the Production database

  • In discussions with a client, the option of placing a development copy of the database on the same server which will later be used for the production database. This is not the first choice, but does merit consideration as an option, I think. The reason for doing so would be to avoid the cost of a second license for SS on a development server.

    However, before we consider that, we'd like to know what the potential dangers might be. One that was raised is that somehow a failure in the app using the development database could cause it to corrupt and, in turn, cause the server itself to crash, removing the server from service for the organization.

    My questions are

    A) how realistic is it to be afraid that a total corruption in one database (the dev version) would cause the loss of service in all other databases on that same server?

    B) are there other reasons to avoid putting dev and production databases on the same server?

    DOn't be afraid to tell me this is a bad idea if it is. I am just trying to consider all options on the table.

    Thanks.

    George

  • I just thought of a second, more likely problem. An error in the development database could cause consumption of resources that would impact other databases. Also, there's the danger of the developer connecting to production instead of development and really hosing things up.

    So, this does sound like a bad idea, but I'd still like to hear thoughts on the possibility of taking the whole server offline, as suggested, if something goes wrong in the development database.

  • There are so many reasons this is a bad and so few reasons it is a good idea. Usually devs will have elevated privileges to the dev server that are not available to them in production. The resources being consumed on dev will have an impact on production, etc etc etc...

    Get a copy of sql express installed somewhere else as your dev instance. Unless you need all the feature of enterprise on your dev system it should be adequate in most situations.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • SQL Server Developer Edition is only $50.00, so that SQL Server license cost is fairly small.

  • The client told me that the Dev Edition was limited to a 180 evaluation period. Was he wrong about that?

  • I'm gonna pile on.

    This is a bad idea, there is so much that can go wrong that endangers your production environment that it simply isn't worth the risk.

    You could build another instance perhaps but that would be very dependent on the amount of memory and processors, at least in this case you could better control your risks.

    With all that said if they demand you do it I'd write up something specifying the dangers and make them sign off on it explicitly so later WHEN something happens they can't claim "we didn't know". Its not much but its something..

    CEWII

  • Forget I asked. 😀

    I am looking into actual limitation of the Developers edition to verify the 180-day limitation objection.

  • Dev edition is Enterprise Edition with a license that basically say you can't use it in production. It does not have a time limit. If you install a copy with an evaluation license it will expire. You pay for Developer Edition, its cheap like $50, you could install it as an eval and you would have 180 days but at that price point WHY?

    CEWII

  • The problem turns out to be finding the developer version for 2008 R2. Gee MS likes to be obscure.

  • Google: purchase sql server 2012 developer edition

    You should have at least 5 places to buy from. Keep in mind that you won't be able to buy SQL 2005 developer but you might be able to use the license for 2012 developer for 2005 developer.. I only point that out because of the fact you posted this in the SQL 2005 section.

    CEWII

  • George Hepworth (6/21/2013)


    The problem turns out to be finding the developer version for 2008 R2. Gee MS likes to be obscure.

    It is not easy to find, but Google is your friend:

    http://www.nextag.com/Microsoft-SQL-Server-2008-739594965/prices-html

  • Thanks for all the good advice.

    I was able, finally, to get the client's IT to install SS 2008 R2 Express, but then I had to argue for two days to get him to uninstall the "Katmai" version of SSMS and replace it will SSMS 2008, which isn't perfect, but is functional.

  • As others have said, running mixing non-Production and Production on the same server is a bad idea.

    One of the main issues is Governance. It is established good practice to separate Production and non-Production to separate environments, so much so that any mixing of environments is seen as bad governance by almost everyone with the skills to work out what you have done or gets to hear about it. With the trivial per-instance cost today of running a separate virtualised non-Production environment, no-one can justify mixing non-Production and Production on the same server instance.

    If your customers get to hear you are doing this, you will suffer reputational damage. Period. Potential customers will walk rather than sign with people that take governance this lightly. If you are under statutory regulation than do not expect the regulators to treat this lightly.

    The next issue is stability and performance of your Production environment. This will suffer if you mix non-Production and Production on the same instance. If you ever get a Production outage that is caused by a non-Production system do not expect your senior management to treat this lightly. If this affects your stock market price do not expect yuor shareholders to treat this as competant management of their resources.

    Part of choosing your own software vendors is to pick one who make either no or a nominal charge for non-Production environments. Vendors understand the need for good governance, and typically will prefer that you protect the Production environment rather than risk bad publicity because you can blame tham that your Production failure was mandated by their licensing policies. If you are talking with a vendor that insists on charging the same for non-Production as Production, this should be your turn to walk and find someone else.

    You may want to look at http://sqlserverfinebuild.codeplex.com/wikipage?title=SQL%20Server%20Administration for more details.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • I was able, finally, to get the client's IT to install SS 2008 R2 Express,

    SQL Server 2008 R2 Express is NOT the same thing as SQL Server 2008 R2 Developer. It is with Express that your client will run into the 180 licensing limitation unless they purchase a fully licensed copy.

    Chris Powell

    George: You're kidding.
    Elroy: Nope.
    George: Then lie to me and say you're kidding.

  • Yes, I know that SQL Express and SQL Developer edition are different. No, SQL Express is not time limited. The version I keep on my own Development machine has been around for a couple of years now.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply