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. Second option is stored procedures.
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]