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

Question

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.

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?
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.

Could you tell if the tables have the same schema and if they are hosted in the same sql-server instance? — Steve
– Steve, Commented Dec 17, 2012 at 16:45
The tables have same schema, but they are hosted in different SQL-server instances. — PineCone
– PineCone, Commented Dec 17, 2012 at 16:48

Steve · Accepted Answer · 2012-12-17 17:14:56Z

1

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'

edited Dec 17, 2012 at 17:14

answered Dec 17, 2012 at 17:00

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

PineCone Over a year ago

Does this view need to be created on the database that I have newly created to save information from all other data sources? This seems that data will be read directly from the actual data source, which is tricky depending on access permission.

Steve Over a year ago

Yes, you could place the View inside the database used to create the report. It is tricky from the point of view of the permissions, but if you could setup this view then you don't have anymore to worry about changing data in the linked servers database's tables. (Of course, the permission problem exists also if you try to connect to each database and import the data in your working database)

Brian White · Accepted Answer · 2012-12-17 22:59:51Z

0

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.

answered Dec 17, 2012 at 22:59

Brian White

1,3141 gold badge11 silver badges16 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related