July 22, 2019 at 12:03 pm
Hello there,
Hope you help me out with the following research (case study).
The case is this, there is a need for accessing several tables within the company database (limited access, for reporting use, writing select and views creation is the expected need).
The data itself is enormous and it's connected to many tools and services.
I wonder if this can happen and how - New DB creation with tables from all other databases and its connections (table-to-table), plus somehow to keep this link between the existing tables (coming from other DB's) and those who are in the new DB. The ideal situation is all tables coming from different DB to be linked with its corresponding tables (from the new DB), for a simultaneous update.
All suggestions will be highly appreciated!
July 22, 2019 at 12:28 pm
If I misunderstood, I apologize.
What you're saying you want is a separate database to use for reporting. That separate database will consist of data from a bunch of other databases in the system. You want that separate database to be "linked" meaning, zero, or near zero, lag on the data from this disparate other systems, yet, all querying will be done locally against this copy of the database?
Is that a good summary?
If so, good luck.
There's not a single tool that's going to do this for you and do it well. A lot of people might lean on linked servers to deal with this, but they put all, or more most, of the process back on the original systems. You might look at multiple customized replication services, but that's going to be serious nightmare to set up and maintain. Mirroring is out. Availability groups are out. Both because you can't selectively pull pieces of the database into a common location.
Chances are, to make this happen, you're going to have to build a customized system. That's the only way I know to do it. An alternative might be to set up Availability Groups and read only secondaries for the databases you're interested in. Then, create your custom, all-in-one, database that pulls from various sources through linked servers. Just link to the read only secondaries so that you're offloading the processing from your production instances.
Another alternative, one I don't know much about, is SQL Server 2019 and the Big Data Cluster (hate the name). This allows you to build out a single source for querying multiple disparate sources using an application of containers through Kubernetes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply