Post reply

Pair of dbs; need sandbox

  • July 15, 2010 at 11:32 am

    #237692

    I have two databases, G1 and P1, in a single-instance SQL Server 2008 environment. They are used by a website.

    My requirement is to create a separate, but identical website which access a clone of these two databases (G2 and P2)

    So far so good, I got this all done, with the new website referring only to the cloned (sandbox) databases and then discovered a BIG problem.

    Some of the stored procedures in each database refer to the other database, and do so by name.

    For example in a stored proc in G1 there may be

    SELECT Users FROM P1.dob.UserTable

    So of course the identical code is in the same stored proc in G2, but instead of referring to P2, it refers to P1.

    There's lots of them, and I'm reluctant to modify them all due to the risk of screwing up, sync problems, etc.

    I could for example test the DBNAME() in which I'm running, and use an IF statement to use a variant of the SELECT statement, which I think is unmaintainable.

    I need some ideas, and if this needs to be moved to some other forum, please advise.

    Phil

  • July 15, 2010 at 11:43 am

    #1193668

    i think you'll have to script out all the functions,views and procedures to a file, then do a find and replace...

    P1. -->[P2].

    [P1] -->[P2]

    then replace each CREATE PROCEDURE/CREATE FUNCTION/CREATE VIEW with ALTER PROCEDURE/ALTER FUNCTION/ALTER VIEW , and finally run the script to change the objects so they refer tot eh otehr sandbox database.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 15, 2010 at 11:55 am

    #1193679

    Thanks, that actually seems do-able.

    There's no way to dynamically change the P1.dbo.Table syntax, is there? (without using the dreaded Dynamic SQL).

    If I follow your suggestion, I have a syncronization and maintenance problem, but the sandbox databases are intended to be discardable anyway.

  • July 15, 2010 at 11:57 am

    #1193683

    Use alias on the second box.

    Alias g1 point database g2.

  • July 15, 2010 at 12:09 pm

    #1193687

    You could install another database instance (possibly even express edition), and copy the databases using the same names. I can't imagine your website code likes different database names any more than the database code does.

    --J

  • July 15, 2010 at 12:13 pm

    #1193694

    jvanderberg (7/15/2010)

    You could install another database instance (possibly even express edition), and copy the databases using the same names. I can't imagine your website code likes different database names any more than the database code does.

    --J

    much better suggestion than what i came up with. follow this sage advice instead.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 15, 2010 at 12:45 pm

    #1193715

    Pointing to a different db is easy with asp.net (web.config).

    How do I install another instance, is it just the same as installing a whole new SQL server? What risks are there? Does it run its own SQL service?

    I just finished modifying the scripts and my observations are:

    - you're never really sure you got all the places

    - sometimes it's written [P1].[dbo]. and others [P1].dbo. and (hey, I'm not perfect

    - It's hard to hit all the CREATE statements without hitting the word Create elsewhere, and CREATE PROC can be CREATE PROCEDURE and there can be any number of spaces

    - then when you ditch the sandbox databse and replace it, you gotta do it again, from scratch, or you might be regressing some procs and views etc.

  • July 15, 2010 at 12:54 pm

    #1193722

    Yes, you would install SQL Server to a machine. You can install it to the same machine or a different machine. Using a different machine means you can do load tests without bogging down your production machine. If you development web server is running on a different machine, I recommend running SQL Server on that machine.

    The only real risk is licensing. If you're licensed per instance, a full version of SQL Server might not work for you. But you probably don't need that anyways. Since this is essentially a "disposable" database, SQL Server Express Edition might work fairly well for you (and it's free!). You would just download and install it.

    You lose some features by going to express, and there's a database size limit of 10 G, but otherwise it shouldn't really effect you. From the website code, it looks pretty much identical to your other server. Check out the feature comparison to make sure express edition will work for your needs:

    http://technet.microsoft.com/en-us/library/cc645993.aspx

    --J

  • July 15, 2010 at 1:27 pm

    #1193745

    Thanks to all. Yeah it's a licensing issue but I did all the development work, initially, on SQL Express on my dev machine so I know it's feature rich enough.

    So I'll think I'll follow that plan even though I have it working already thanks to all of you.

    If I put it in another instance, then I don't have to change anything inside the dbs at all.

  • July 15, 2010 at 2:58 pm

    #1193788

    It may require a little bit of time initially to set it up, but those synronization and maintenance problems go away. You'll save time in the long run.

    --J

Viewing 10 posts - 1 through 9 (of 9 total)

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