CODEWITHSUNDEEP
sql-serverasp.net-mvcdatabase-design
PineCone
PineCone

Reputation: 2353

How to update SQL Server database from multiple data sources for ASP.Net MVC 4 application

I have 10+ SQL Server databases, from where I would use one table from each database to display information using the application that I am creating. For instance, DB1, DB2....DB10. NewDB (Account, Country, Costcenter....etc.).

I have started with creating a new database which would contain all the information from all those 10+ databases. However, I am confused in many cases.

  1. First of all what process should I follow? Shall I create a table (in new database) with the same structure as the actual data source and insert data from actual data source to new database?

  2. Should I be doing this on the DBMS? If so, is it some scripting? (hint expected since very new to this)

I am creating a report generation application which has 10+ data sources. I need some hint which way should I proceed?

Thanks for advice/help in advance.

Upvotes: 0

Views: 485

Answers (2)

Brian White
Brian White

Reputation: 1314

Is this essentially a reporting database? If so, the classic solution is to make a new database (named Reporting or something like that) and use SSIS (or some other data extraction/transformation tool) to move over just the data you care about reporting on. That way your reporting activity isn't hammering your transactional databases, and you can arrange the reporting database in a way that is easy to report on. A "Full" solution would extract data into a star schema. A partial solution may just do things like put the person's name on every table (or product name etc.) so you don't have to join to the user table all the time and can just report on "Recent Logins" from the Logins table.

Upvotes: 0

Steve
Steve

Reputation: 216358

If the tables are on the same server instance you could simply create a view that links together the tables like this

CREATE VIEW [dbo].[MULTITABLELINK]
AS
SELECT     ID, 'Database1' AS dbSource , Account, Country
FROM       [database1].dbo.AccountTable
UNION
SELECT     ID, 'Database2' AS dbSource , Account, Country
FROM       [database2].dbo.AccountTable

but in your case (different SqlServer Instances) you probably need to create a Linked Server that allows to see the different instances inside the one working for you

For example:

      EXEC master.dbo.sp_addlinkedserver 
@server = N'TheFirstLinkedServerName', 
@srvproduct=N'SQLSERVER', 
@provider=N'SQLNCLI', 
@datasrc=N'TheRemoteServerName', 
@catalog=N'TheCatalogToUse'

      EXEC master.dbo.sp_addlinkedsrvlogin 
@rmtsrvname=N'TheLinkedServerName',
@useself=N'False',   -- True if your users are on the same windomain
@locallogin='TheLocalDomainAndUserName',
@rmtuser='TheAllowedUserOnTheRemoteInstance',
@rmtpassword='ThePassowordOfTheRemoteUser'

Upvotes: 1

Related Questions