CODEWITHSUNDEEP
sql-serverdatabasereportingsql-server-2014
aspirant_sensei
aspirant_sensei

Reputation: 1673

Copying tables for reporting purposes to a separate database

I have a transactional database (SQL Server 2014) with around 60 tables, and there is a requirement to create a separate reporting database for reporting purposes.

This will only need to run every 24 hours - however I will be needing to move the data into a different, more query-friendly schema!

Because of this I would hope I could just create some Views on the Transactional Db and then create a table based on that view in the Reporting db and copy across the data.

I originally thought of writing a scheduled Windows Service that somehow extracts data from the tables and inserts into the new one, but then thought if the schema changes it has to update in two places, and also thought surely an enterprise SQL Server license must have some tricks.

I then looked into 'database mirroring' on specific tables but this looks to soon be deprecated.

'Log shipping' looks like more of a disaster recovery solution!

Is there an industry 'best' approach to this problem?

Upvotes: 0

Views: 195

Answers (1)

user6691848
user6691848

Reputation:

You will need to devise an ETL process to extract data from your source database, transform it and load it into your reporting database. There are many tools available to you to make this easier. You can use SSIS, Azure Data Factory for Azure SQL, and there are many other options. You can use the SQL Agent to schedule stored procedures to run your ETL process.

Your target database will look much different than your source database. There is really no quick way (quick as in scheduling a backup) to accomplish this. There is a lot of information on data warehouse and ETL design available to you to assist you in deciding how to proceed.

Upvotes: 1

Related Questions