3

We have a database for each customer, for over 700 databases. All the schemas are the same. Now we want to conduct Reporting for : All Databases Customer Analysis. Many db professionals prefer single tenant databases. All database reside on the same server instance. Single Tenant Database Discussion

CREATE TABLE CustomerOne.[dbo].[CustomerTransaction]
(
    [CustomerTransactionid] [int] identity(1,1) primary key NOT NULL,
    [Customerid] [int] NOT NULL default(1),
    [QuantityBought] [int] NULL,
)

Does anyone know of any other ideas not listed below, which would work? Our idea is to either create large Views, however, View performance is slow. The second option is stored procedures.

(1)

CREATE VIEW Reportingdb.dbo.CustomerTransaction
as
SELECT 
    [Customertransactionid]
    ,[Customerid]
    ,[Quantitybought]
FROM [CustomerOne].[dbo].[customertransaction]

UNION ALL

SELECT 
    [Customertransactionid]
    ,[Customerid]
    ,[Quantitybought]
FROM [CustomerTwo].[dbo].[customertransaction]

2) We can also create a stored procedure. Stored Procedure which will insert into this large table. However there will be a time lag between runs, we want instantaneous data.

insert into ReportingDB.[dbo].[customertransaction] 
select * from CustomerOne.[dbo].[customertransaction]
UNION ALL
select * from CustomerTwo.[dbo].[customertransaction]

SQL How to Convert Single Tenant Databases to a Multitenant Database

  • Do you actually need real-time analytics? I'm thinking of a reporting database with SSIS packages that update it once a day. I don't think the stored procedure route will perform much better than the views, unless you let the stored procedure do the filtering of the data. – Jonathan Fite Sep 6 '18 at 2:53
  • You aren't going to get good performance out of it in any event. Every query you run will in turn have to execute at minimum 700+ queries to individual databases. The rest of the question is where you want to spend your development/management time? I think having the business accept a lag for valid data (and a day was just a dart on the wall, you can go more frequently, even continuously if you want) is the easiest, and then develop your reports in the simplest way. You could even have some reports be more real time than others and save on some resources that way. – Jonathan Fite Sep 6 '18 at 12:33
3
+50

Given that the data is in 700 separate databases, and likely to stay there, and that you want reporting to show the current state of the transactional database I see basically two choices. One is send the query to the data; the second is send the data to the query.

Your view would be an example of sending the query to the data. Each client DB performs some of the work and the reporting DB collates and formats the results. There will be a couple of challenges with this. Cross-database queries are slower than single database queries. I don't have numbers to quote, but this is the received wisdom. Likely the diverse databases will be accessed serially - first DB1 is read, then DB2 is read, then DB3 etc. - meaning the reporting query's execution time will be (at least) 700 times an individual DB's query time. Ouch. There may be clever ticks to force parallel processing in this circumstance. I don't know of any. Certainly a SQL CLR procedure could be written to spawn a thread for each client DB, then correlate the results. A sort of Hadoop Lite, if you will. If I were going to those lengths, however, I think I'd prefer to have BIML generate the necessary packages and run them on demand. It would be a close-run thing to decide which I disliked more - 700 extra threads running inside the SQL Server process or 700 SSIS packages running simultaneously.

By sending the data to the query I mean having a second copy of the data in a single reporting database. It is against this copy that report queries run. This strategy comes in two flavours - pull and push. Pull is the reporting DB sucking data from client DBs according to the reporting DB's schedule and requirements. Your second suggestion using a stored procedure would fall into this approach. BIML-generated SSIS packages could also be used for this. It will have the normal issues with polling (figuring out what's changed, wasted cycles when nothing has). SQL Server has features to help with this (Change Tracking and Change Data Capture), but still.

Push techniques are generally favoured because they avoid the polling overhead. A simple trigger may suffice, duplicating the data to the reporting DB or a staging table on each write to a client DB. This is extra work in a client-facing transaction, however, and extra opportunity for failure. For this reason asynchronous techniques would be preferable. One option is to have the application put a message on a reliable service which the reporting application picks up and processes. There is even a message service baked into SQL Server which could be used (Service Broker). Processing, coordination, failure and recovery are all you concern in client code, however.

SQL Server comes with a number of replication options. These reliably copy data from a source to a destination. Initiation, synchronisation and recovery are all available. I think this is likely to be your best option. Configure SQL Server to replicate changes in client DBs asynchronously but reliably to the reporting DB. Run queries off this reporting DB.

0

You can use a cursor driven from sys.databases to get the fully qualified table names, then union them all like this:

    declare @db varchar(255),
    @table varchar(255),
    @sql nvarchar(max)

    set @sql = ''

    declare c cursor local for

    select name from sys.databases

    open c
    fetch from c into @db
    while @@fetch_status = 0

    begin

    set @sql = @sql + ' select * from ' + @db + '.sys.tables union'

    fetch from c into @db
    end

    --delete the last 'union' (sloppily)
    set @sql = left(@sql, len(@sql)-5)
    print @sql

    --exec sp_executesql @sql
    close c
    deallocate c

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy