Post reply

Use of 4-Part object naming and effects on local plan execution

  • April 9, 2012 at 7:19 am

    #258904

    [Background]

    I'm in the unfortunate situation where the only data I have to develop against is located on a Production SQL 2k8 Server, but all new SQL objects have to be created first on the Development SQL 2k8 server. To circumvent this limitation we use 4-part object

    naming & a linked server from DEV to PROD in order reference lookup tables on the Production server database(s).

    [Issue]

    Now that the database previously on Development has been deployed to Production the query plans still indicate a "Remote Query" step despite the fact the server name used in the Views, Procs, etc... is now the local server (in this case the Production box).

    I'm getting the impression that SQL Server is basically doing a lookup into syservers, resolving the name back to the local (Prod) box and then executing the query, but before I tread down the path of correcting for this I was hoping to get some confirmation that it is in fact the case.

    Any feedback is greatly appreciated.

    Thanks

  • April 9, 2012 at 7:25 am

    #1470642

    Instead of using the 4-part naming directly in your development, why not create synonyms that use the four part naming so that your development efforts look like you are using those lookup tables directly in development.

  • April 9, 2012 at 7:28 am

    #1470645

    Excellent suggestion! That should resolve the issue rather nicely.

    Thanks

  • April 9, 2012 at 7:32 am

    #1470649

    Why not populate the lookup tables in the dev box from the production box instead of using linked servers to test. It isn't really a valid test if linked servers aren't used in development.

    Typically lookup tables are small so populating them on the dev box using a linked server query in a job shouldn't be bad. Just a MERGE statement across the linked server that you schedule to run during low-use time. A tool like SQL DataCompare could be used on a regular basis as well. BCP on a regular basis, or using SSMS to create insert statements.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing 4 posts - 1 through 4 (of 4 total)

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