Post reply

cross database querying

  • August 2, 2007 at 2:34 am

    #117672

    Would like some peoples opinions on this...

    In the past I have been against it, but moving forward I am not sure. We have both SQL2000 and SQL2005 systems running.

    Basically this came up when we install MicroSoft CRM, then decided to customize it . Due to the support contract we are not able to put custom objects into the JR_MSCRM (CRMs main DB), else it voids our support contract.

    Am I being paranoid? What are the pros VS cons of cross database querying?

    Thanks in advance,

    Graham

  • August 2, 2007 at 8:21 am

    #724191

    That is exactly what I am doing with other CRMs. Either these DBs are too complex either the support company would not recommend to customize the databases. I created additional databases for reporting with views, custom procedures, functions, user authentication for reporting for different projects.

    To think of it, major reporting tools including SQL Server Reporting Services have their own databases and working with reports you have to access 2 or more databases anyway. Also, consider system databases - Master, MSDB,tempdb - you are using them with working with your production database.

    Regards,Yelena Varsha

  • August 2, 2007 at 9:06 am

    #724218

    some good points.

    I particularly like the bit about master, msdb, etc

    I am not seeing a major issue, just in the past, we have always chosen to NOT use cross database querying. IE. x1.dbo.usp_name_s runs this commad select column1 from x2.dbo.name

    Is there any issues with the optmizer? Indexes not used proerly?

    Thanks

     

  • August 2, 2007 at 10:04 am

    #724248

    Not sure about indexes.

    I would worry about cross-database ownership chaining. First, you have to enable it for the databases or on the server level and then you have to design schemas, owners and permissions accordingly.

    Regards,Yelena Varsha

  • August 3, 2007 at 3:39 am

    #724438

    thanks for that.

    I am going to try and stay away from cross database ownership chaining, will try...

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

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