none
Test Database Configuration

    Question

  • Where I work, we currently have something that resembles a test/development "environment" (we have a dev server where our working directories are, and it theoretically has a test database for our main application).  As part of some upgrades we are doing, I am wanting to change this so we do local development (keep our working directories on our PC's instead of on the network, which is terribly slow and requires that we run our VisualStudio through Citrix).

    Anyway, my question is, in a distributed environment (12 sites, each with their own database server), what is the best way to set up a test/dev environment?  Our WAN can't really handle the traffic, so if we put a test database on each production server (which is my initial suggestion to my perceived problem), I need to be able to convince my boss that we will be able to easily modify the databases to keep the schemas synced.  Additionally, by moving our working folders locally, I need to convince him that there will not be any untoward consequences (I happen to know that it can be done, since this is the first job I've had that was set up in this manner).

    So, any personal advice you guys have, or links you can give me would be greatly appreciated.  If you need more information, let me know.

    Monday, September 23, 2013 3:05 PM

Answers

  • I

    Honestly I am a big opponent of mixing production and test. As I showed I several demos at PASS Summit, the handling of security is an almost impossible task, if you want to prevent developers to harm production. And that even without caring about the possible performance implications.

    In reality of course, compromises may be necessary.

    Recommending anything is a non-trivial task, as it is not only a matter of bandwidths and number of sites, but mostly there are many tiny bits involved as well.

    I do not know, what stands against a test environment per (developer) site – rather than production.

    Maybe I am missing a point, but just throwing in a thought. This would also keep the traffic for the transmission of changes from production until tests have completed successfully…

     

    I am not saying it “the best option”, just my initial thought.


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    • Marked as answer by dgjohnson Saturday, September 28, 2013 3:24 PM
    Monday, September 23, 2013 10:38 PM

All replies

  • Firstly, I understand this is probably not the most appropriate forum for this question.  I have tried a couple other places with no luck, and you guys are quick, so maybe I'll get an answer here.  If you are a mod, and know where this *should* be, feel free to move it.

    Where I work, we currently have something that resembles a test/development "environment" (we have a dev server where our working directories are, and it theoretically has a test database for our main application).  As part of some upgrades we are doing, I am wanting to change this so we do local development (keep our working directories on our PC's instead of on the network, which is terribly slow and requires that we run our VisualStudio through Citrix).

    Anyway, my question is, in a distributed environment (12 sites, each with their own database server), what is the best way to set up a test/dev environment?  Our WAN can't really handle the traffic, so if we put a test database on each production server (which is my initial suggestion to my perceived problem), I need to be able to convince my boss that we will be able to easily modify the databases to keep the schemas synced.  Additionally, by moving our working folders locally, I need to convince him that there will not be any untoward consequences (I happen to know that it can be done, since this is the first job I've had that was set up in this manner).

    So, any personal advice you guys have, or links you can give me would be greatly appreciated.  If you need more information, let me know.

    Monday, September 23, 2013 9:04 PM
  • I am moving it to database engine.


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Monday, September 23, 2013 9:29 PM
  • I

    Honestly I am a big opponent of mixing production and test. As I showed I several demos at PASS Summit, the handling of security is an almost impossible task, if you want to prevent developers to harm production. And that even without caring about the possible performance implications.

    In reality of course, compromises may be necessary.

    Recommending anything is a non-trivial task, as it is not only a matter of bandwidths and number of sites, but mostly there are many tiny bits involved as well.

    I do not know, what stands against a test environment per (developer) site – rather than production.

    Maybe I am missing a point, but just throwing in a thought. This would also keep the traffic for the transmission of changes from production until tests have completed successfully…

     

    I am not saying it “the best option”, just my initial thought.


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    • Marked as answer by dgjohnson Saturday, September 28, 2013 3:24 PM
    Monday, September 23, 2013 10:38 PM
  • Perhaps if we used a named instance for a test database?  In our environment the security concern is not a concern, as all developers are also front line support, and have full access to production and test.

    Personally, I would prefer a central server with test and development databases.  The problem I have with that is our WAN.  If I can also get us to an actual test environment (where we can push out an executable for someone to test besides the developer, against a system that is not production), then network performance may present a problem.

    Tuesday, September 24, 2013 2:28 AM
  • The problem I have with that is our WAN.  ... then network performance may present a problem.

    Hello,

    The network performance or more the latency of it will be a problem, when your application performs a lot a server roundtrips; but then it's mainly an issue with your application, not with the network. Avoid server round trips, and your application will run fine.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Tuesday, September 24, 2013 6:46 AM
  • Perhaps if we used a named instance for a test database?  In our environment the security concern is not a concern, as all developers are also front line support, and have full access to production and test.

    Personally, I would prefer a central server with test and development databases.  The problem I have with that is our WAN.  If I can also get us to an actual test environment (where we can push out an executable for someone to test besides the developer, against a system that is not production), then network performance may present a problem.


    A named instance is a much nicer alternative, yes.

    It’s much easier to limit this one in terms of memory and CPU – plus security-wise, too.

    I just understood you have multiple production servers. (“on each production server“), that’s why I thought of a “local test instance”. Central is certainly favourable..

     


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Tuesday, September 24, 2013 12:41 PM
  • Is there a way to test the network latency on the queries?  My location has the worst bandwidth, so theoretically would get the slowest times.

    Tuesday, September 24, 2013 6:31 PM
  • sure

    run some queries locally

    then run them remotely

    the difference is the network overhead


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Tuesday, September 24, 2013 6:37 PM
  • sure

    run some queries locally

    then run them remotely

    the difference is the network overhead


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Ha, a case of thinking about something too hard on my part......
    Tuesday, September 24, 2013 6:48 PM
  • ..

    Ha, a case of thinking about something too hard on my part......
    we all have that from time to time... :-D

    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Tuesday, September 24, 2013 10:27 PM
  • Is there a way to test the network latency on the queries?

    Not really on the queries as it, but on your network. Start a ping from the client to the server; it returns you the response time ~ latency. On a high speed WAN you may get an average latency of ~25ms, with DSL WAN may ~60 ms; on LAN <= 1 ms.

    And if you have 60 ms latency, this means you can send at max 1000 ms/60ms = 16 of even such a simple query

    SELECT 'Test Connection' AS Result

    to SQL Server; therefore avoid server round trips and prefer batch processing.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, September 25, 2013 5:46 AM
  • Hi,

    Since your WAN can't really handle the traffic, therefore the SQL Server high availability solutions(such as Database Mirroring, log shipping, Always On) can't be used for sync data, because they need good network. Personally, your suggestion about putting a test database on each production server is available.

    Thanks

    Candy Zhou

    Wednesday, September 25, 2013 7:24 AM