locked
Multiple database vs ? RRS feed

  • Question

  • We are currently re-designing an OLTP database that handles sensitive data and which also contains a fair amount of configuration and master file data. There are 2 main issues that we are trying to overcome with our new design:-

    1) Sometimes we need to retrieve the customers configuration and master file data to either diagnose issues or further enhance their configuration - customers control their configuration under normal circumstances but we often get involved in more complex configuration work. With the database holding sensitive data we have to go through a process of getting a copy of database with the sensitive data removed which can involve some hurdles e.g. sign-off by IT committees etc.    With the configuration data in a separate database we could ask for a backup of this database to be created and sent to us with no sensitive data issues.  It would result in a quicker turnaround for the customer and overall less hassle for everyone concerned.

    2) Customers have several environments (production, test, training) and often the configuration and master file data needs to be synchronised across these environments e.g. new configuration data in test ported across to live.   We end up having to script up changes since some configuration changes by the customer can be extensive.   Again, having a single database for all configuration data with version numbering built in to allow different versions to be applied to different environments would assist us greatly.

    Having a single configuration database would appear to be a good solution but it would need to benefit us and our customers enough to make it a worthwhile endeavour.     My main concerns are to do with performance and configuration...

     e.g. will queries across multiple database incur a significant performance hit?

           would we have to explicitly name the configuration database in all of our queries or could we use an alias?

           i.e. select * from [Production].[dbo].[table1] inner join [Configuration].[dbo].[table2]

                or could we do ... select * from [Production].[dbo].[table1] inner join <alias>.[dbo].[table2]       - and then define the alias somewhere

    I have considered another alternative where we have the tables from the configuration database duplicated in all of the production, test, train databases and then manage a copy over of data from the configuration database in real-time.   This would hopefully negate the performance and configuration issues noted above and just leave us with a synchronisation process to manage.

    Any thoughts or recommendations would be greatly appreciated.    When we decide on a course of action we will go with it and test thoroughly and be able to provide feedback on the positives and negatives we encountered.

    Many thanks.

    Friday, October 9, 2015 3:57 PM

Answers

  • will queries across multiple database incur a significant performance hit?

    No.  Cross-database queries on the same instance are optimized like any other.  Linked servers are another matter, though.

    would we have to explicitly name the configuration database in all of our queries or could we use an alias?

           i.e. select * from [Production].[dbo].[table1] inner join [Configuration].[dbo].[table2]

                or could we do ... select * from [Production].[dbo].[table1] inner join <alias>.[dbo].[table2]       - and then define the alias somewhere

    3-part names are needed only when referencing objects in other databases.  So in the context of the Production database, you could use:

    SELECT * 
    FROM dbo.table1
    INNER JOIN Configuration.dbo.table2;

    Alternatively, you could create synonyms for objects in other databases:

    CREATE SYNONYM dbo.table2 FOR dbo.Configuration.dbo.table2;

    And then use only 2-part names in queries:

    SELECT * 
    FROM dbo.table1
    INNER JOIN dbo.table2;

    The synonym approach avoids the need to hard-code database names in queries, thus making it easier to use a different database name if needed.  That allows you to have multiple instance of the same application on the same SQL instance, each with different database names (e.g Test1/Configuration1, Test2/Configuration2).  The database schema could be the same but the synonyms would differ.

    Although a separate configuration database could facilitate copying config data back to test like you mentioned, it's unclear to me if that is how you plan to promote configuration changes as part of your SDLC.  In my opinion, it would be better to develop a tool to export/import configuration data rather than use T-SQL scripts.  Such a tool could be used for both promotion of changes as well as getting copies of configuration data from production, making the need for separate databases moot.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP, Editor Sunday, October 11, 2015 2:40 PM remove extraneous text from query
    • Marked as answer by yoda24 Monday, October 12, 2015 9:22 PM
    Sunday, October 11, 2015 2:39 PM
    Answerer

All replies

  •  <<< would we have to explicitly name the configuration database in all of our queries or could we use an alias?

    https://msdn.microsoft.com/en-us/library/ms177544.aspx


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, October 11, 2015 6:38 AM
  • will queries across multiple database incur a significant performance hit?

    No.  Cross-database queries on the same instance are optimized like any other.  Linked servers are another matter, though.

    would we have to explicitly name the configuration database in all of our queries or could we use an alias?

           i.e. select * from [Production].[dbo].[table1] inner join [Configuration].[dbo].[table2]

                or could we do ... select * from [Production].[dbo].[table1] inner join <alias>.[dbo].[table2]       - and then define the alias somewhere

    3-part names are needed only when referencing objects in other databases.  So in the context of the Production database, you could use:

    SELECT * 
    FROM dbo.table1
    INNER JOIN Configuration.dbo.table2;

    Alternatively, you could create synonyms for objects in other databases:

    CREATE SYNONYM dbo.table2 FOR dbo.Configuration.dbo.table2;

    And then use only 2-part names in queries:

    SELECT * 
    FROM dbo.table1
    INNER JOIN dbo.table2;

    The synonym approach avoids the need to hard-code database names in queries, thus making it easier to use a different database name if needed.  That allows you to have multiple instance of the same application on the same SQL instance, each with different database names (e.g Test1/Configuration1, Test2/Configuration2).  The database schema could be the same but the synonyms would differ.

    Although a separate configuration database could facilitate copying config data back to test like you mentioned, it's unclear to me if that is how you plan to promote configuration changes as part of your SDLC.  In my opinion, it would be better to develop a tool to export/import configuration data rather than use T-SQL scripts.  Such a tool could be used for both promotion of changes as well as getting copies of configuration data from production, making the need for separate databases moot.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP, Editor Sunday, October 11, 2015 2:40 PM remove extraneous text from query
    • Marked as answer by yoda24 Monday, October 12, 2015 9:22 PM
    Sunday, October 11, 2015 2:39 PM
    Answerer
  • Are the databases being accessed by something like a web application? If so, the connection strings and other system variables can be set at startup using a single master configuration database quite easily.

    If it is TSQL only that you are looking at executing, you could look at some sort of dynamic SQL that could inject the server/database/schema/table, below is a snippet from one of the procedures I have used in my current role, you will of course need to maintain all of the linked servers and access to them.

    CREATE PROC DynamicSQL(@Server NVARCHAR(100)=NULL,@Database NVARCHAR(100),@Schema NVARCHAR(100),@Table NVARCHAR(100)) AS BEGIN
    
    DECLARE @Cmd NVARCHAR(MAX)
    SET @Cmd='
    SELECT *
    FROM '+ISNULL('['+@Server+'].[','[')+@Database+'].['+@Schema+'].['+@Table+'] d WITH (NOLOCK) '
    
    PRINT @Cmd
    EXEC sp_executesql @Cmd
    END


    Gavin Clayton Claytabase Ltd www.claytabase.co.uk

    Monday, October 12, 2015 10:43 AM
  • Thanks.  Synonyms aren't something that I've used before but I'm sure they will form part of our solution.
    Monday, October 12, 2015 9:07 PM
  • Thanks for your answers, suggestions and additional info on synonyms.   We're currently setting up a separate configuration database using synonyms as a separate database. We haven't explored separate databases before so we're going to trial this first - it may not be feasible but we'd like to see the advantages and pitfalls first.  We have considered your preferred method of import/export scripts and we will still look at this.  Fortunately we're in a discovery phase with our design so everything is an option and nothing is set in stone yet.  

    Thanks again.

    Monday, October 12, 2015 9:15 PM
  • Yes the database will be accessed by a web application.   The master configuration database has been discussed already and we like this approach.

    I'm always a bit wary of injection.   We have done something similar before but because of the sensitive nature of the data and declarations we have to make regarding any vulnerabilities relating to injection this probably isn't something we would consider using in the future.

    Thanks.


    • Edited by yoda24 Tuesday, October 13, 2015 1:26 PM
    Monday, October 12, 2015 9:22 PM