locked
Temporarily isolate database changes based on HostName RRS feed

  • Question

  • I want to configure SQL Server so that any commands coming from a specific host get processed separately. My goal is to be able to test the impact of rather drastic changes without having to make a copy of a gigantic database. Ideally, I would also be able to tell how much of a resource impact this was causing on the server.

    When I finish, I am perfectly happy to throw it all away and repeat the parts I wanted 'for real' as a test for the push to the staging / production servers. I guess it would be like transactions but across connections and applications... a big, cumulative transaction per-connecting-machine. I believe snapshots offer similar functionality but as far as I know they are read-only.

    Thanks; I hope this is the right place to ask whether or not this type of feature exists in SQL Server.

    Tuesday, September 21, 2010 10:55 PM

Answers

  • Nothing like what you are asking about exists that I know of in the product.  SQL 2008 offers the resource governor, which allows you to classify connections into resource groups that have limits set for how much of the server they can use, but if nothing else is using the resources they can run wide open.  Its not a way to separate processing.  How large of a database is this exactly?  Maybe I am misunderstanding the requirements, or expectations here, but I wouldn't do development or testing in the manner that you are asking, its not reality based and could lead to incorrect decisions about the impact of changes.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by Tom Li - MSFT Friday, October 1, 2010 12:28 PM
    Wednesday, September 22, 2010 1:29 AM

All replies

  • Nothing like what you are asking about exists that I know of in the product.  SQL 2008 offers the resource governor, which allows you to classify connections into resource groups that have limits set for how much of the server they can use, but if nothing else is using the resources they can run wide open.  Its not a way to separate processing.  How large of a database is this exactly?  Maybe I am misunderstanding the requirements, or expectations here, but I wouldn't do development or testing in the manner that you are asking, its not reality based and could lead to incorrect decisions about the impact of changes.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by Tom Li - MSFT Friday, October 1, 2010 12:28 PM
    Wednesday, September 22, 2010 1:29 AM
  • Activity in one SQL Server instance cannot really be separated, even with the use of the resource governor.  By restricting the amount of CPU resources, you may affect the query plans generated by the regular workload.  You still share the same TempDB, and have a single transaction log for the database, and it's likely that you'll be sharing the same disks, unless you can completely separate your testing area into a new filegroup.  Additionally, during your test, you'll have locks occurring on object in your database, potentially affecting the production workload.

    If the drastic changes end up pushing 100 GB of transaction log activity through, and you're not aware of this or prepared for it in advance, you could bring down the production database.  If your query happens to block another transaction, and you stop this 30 minutes into the process, it may take another 30 minutes to rollback the transaction and release the lock.  

    My recommendation would be to have a dev/test server for this kind of testing, and have it sized large enough to hold a copy of the production database.  If this is not possible, you might consider bringing your DR site online and testing it there (and accepting the risk that you'll be running exposed/delayed during the test).

    Wednesday, September 22, 2010 2:19 AM
    Answerer
  • The closest that you can get is using the Replay feature of SQL Server profiler trace. You would need to capture a replay trace with a filter on hostname and then replay the trace against your database and capture the necessary statistics for the same regarding resource usage.

    Reference: http://technet.microsoft.com/en-us/library/ms187857(SQL.90).aspx


    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: http://troubleshootingsql.wordpress.com
    Twitter: @banerjeeamit
    SQL Server FAQ Blog on MSDN: http://blogs.msdn.com/sqlserverfaq
    Thursday, September 23, 2010 9:08 AM