September 25, 2017 at 9:48 am
Eric M Russell - Monday, September 25, 2017 9:40 AMkevaburg - Monday, September 25, 2017 8:30 AMrichardmgreen1 - Monday, September 25, 2017 8:24 AMThe data on server A changes continuously and we need to have near-real-time reporting from it.
I'm not sure what a materialised view is (never heard of that one) so I don't know.I got my Oracle and SQL Server mixed up..... it is an indexed view in SQL Server....
If a view has an clustered index on it, it persists to disk. My thought was that if it was a slowly changing table the view could be refreshed against a schedule. The fact that the data changes continuously and you need realtime reporting shoots that plan down in flames....
SQL Server indexed views can only reference tables contained locally within the same database as the view. There is a reason for that limitation; the I/O and network overhead of pushing inserts/updates/deletes from the remote tables to the indexed view would be significant.
https://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views
But it seems to me that simply persisting a distributed query to a local staging table on a schedule would be achieve the same result (possibly even better results) as creating an indexed view were that option possible. Essentially all we're talking about here is the need to periodically stage data from remote database locally on the data warehouse server.
Thanks for the correction.....I can understand why the limitations are there.
I think a large part of the solution lies in knowing how "actual" the data has to be with each call made but the local staging table idea is appealing.
September 26, 2017 at 3:03 am
Hi all
Thanks for all the advice.
We need to move a lot of data on a regular basis (which is why we were looking at replication).
I don't fancy setting up an ETL process for a couple of hundred tables (but that's what it may come down to).
September 26, 2017 at 10:07 am
richardmgreen1 - Tuesday, September 26, 2017 3:03 AMHi allThanks for all the advice.
We need to move a lot of data on a regular basis (which is why we were looking at replication).I don't fancy setting up an ETL process for a couple of hundred tables (but that's what it may come down to).
A couple of hundred tables are required? The plot thickens. In that case, log-shipping (or simply a daily restore) of the entire database locally would make sense.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 26, 2017 at 10:14 am
Eric M Russell - Tuesday, September 26, 2017 10:06 AMrichardmgreen1 - Tuesday, September 26, 2017 3:03 AMHi allThanks for all the advice.
We need to move a lot of data on a regular basis (which is why we were looking at replication).I don't fancy setting up an ETL process for a couple of hundred tables (but that's what it may come down to).
A couple of hundred tables are required? The plot thickens. In that case, log-shipping (or simply a daily restore) of the entire database locally would make sense.
It thickens indeed.....
Do you have a high availability groups infrastructure in place? Could you install a readable secondary in the same serverroom as the server that you are making the call from? The readable secondary database could even reside on the same instance as the server making the call.
You can help to reduce the latency in the high availability Group by installing it asynchronously to the Primary.
Thoughts?
September 26, 2017 at 11:21 am
richardmgreen1 - Friday, September 22, 2017 7:27 AMWe could do that, but we need to move a lot of data across to the new server.
The powers-that-be want near-real-time reporting and we aren't allowed to run too much on the original server (apparently it slows other stuff down too much).The 3rd-party software vendor throws a wobbly when we try to set up replication (it upsets a few of their processes apparently) so we're trying to figure out ways round that little lot.
Just curious about this - when you say it upsets their processes is it because they are doing metadata changes during the day as part of the normal usage of the application or is it just when particular processes need to run - e.g. as part of a patch/upgrade, or as part of a very particular nightly process?
I'm asking this because on my current client one of the software used also does not support replication (They are using serialized transactions for particular changes) but this only happens when a DDL has to be applied to a table.
outside this and as part of the normal usage of the software replication has no impact at all.
What I implemented here is a process whereby we turn off replication before doing the processes that require DDL changes, and we kick it off again once that process is done - this only happens 3-4 times a month so its doable for us.
And I could have gone even further with the process and turn replication off only for the tables affected by the change - but it required a bit more development effort so this extra functionality was put on hold. but it is feasible to do.
September 27, 2017 at 2:26 am
They use a home-grown version of ODBC to transfer the data (something called "DR Manager").
With replication in place, DR Manager sees replication, panics and shuts down to update process (they also use this for updating the database tables, etc.).
We could switch replication off and back on again, but it would add a good few hours to do that (and the powers-that-be aren't happy with that bit).
Our databases are in the 250GB range (and we have 2 of them) so you can hazard a guess at the amount of time to take the snapshots.
September 27, 2017 at 7:01 am
What would be the downside of restoring a local copy of the database from backups? That would be very simple to implement and would have no impact on the architecture or performance of the source server.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 27, 2017 at 9:15 am
We could but we'd have to do it several times a day.
September 27, 2017 at 9:33 am
This is why I expressed an interest in using a readable secondary. The data would be available locally for querying.
September 28, 2017 at 1:39 am
Eric M Russell - Tuesday, September 26, 2017 10:06 AMrichardmgreen1 - Tuesday, September 26, 2017 3:03 AMHi allThanks for all the advice.
We need to move a lot of data on a regular basis (which is why we were looking at replication).I don't fancy setting up an ETL process for a couple of hundred tables (but that's what it may come down to).
A couple of hundred tables are required? The plot thickens. In that case, log-shipping (or simply a daily restore) of the entire database locally would make sense.
kevaburg - Tuesday, September 26, 2017 10:14 AMEric M Russell - Tuesday, September 26, 2017 10:06 AMrichardmgreen1 - Tuesday, September 26, 2017 3:03 AMHi allThanks for all the advice.
We need to move a lot of data on a regular basis (which is why we were looking at replication).I don't fancy setting up an ETL process for a couple of hundred tables (but that's what it may come down to).
A couple of hundred tables are required? The plot thickens. In that case, log-shipping (or simply a daily restore) of the entire database locally would make sense.
It thickens indeed.....
Do you have a high availability groups infrastructure in place? Could you install a readable secondary in the same serverroom as the server that you are making the call from? The readable secondary database could even reside on the same instance as the server making the call.
You can help to reduce the latency in the high availability Group by installing it asynchronously to the Primary.
Thoughts?
Sorry guys, I missed these two posts.
We're currently exploring log-shipping as an option (but it's not clear what that will do to our 3rd-party (again) backup solution.
We don't currently (AFAIK) have AG in place but it may be a good idea.
We could make an AG group on the same server as the current databases and the replicate those across (if my understanding is correct).
September 28, 2017 at 4:42 am
richardmgreen1 - Thursday, September 28, 2017 1:39 AMEric M Russell - Tuesday, September 26, 2017 10:06 AMrichardmgreen1 - Tuesday, September 26, 2017 3:03 AMHi allThanks for all the advice.
We need to move a lot of data on a regular basis (which is why we were looking at replication).I don't fancy setting up an ETL process for a couple of hundred tables (but that's what it may come down to).
A couple of hundred tables are required? The plot thickens. In that case, log-shipping (or simply a daily restore) of the entire database locally would make sense.
kevaburg - Tuesday, September 26, 2017 10:14 AMEric M Russell - Tuesday, September 26, 2017 10:06 AMrichardmgreen1 - Tuesday, September 26, 2017 3:03 AMHi allThanks for all the advice.
We need to move a lot of data on a regular basis (which is why we were looking at replication).I don't fancy setting up an ETL process for a couple of hundred tables (but that's what it may come down to).
A couple of hundred tables are required? The plot thickens. In that case, log-shipping (or simply a daily restore) of the entire database locally would make sense.
It thickens indeed.....
Do you have a high availability groups infrastructure in place? Could you install a readable secondary in the same serverroom as the server that you are making the call from? The readable secondary database could even reside on the same instance as the server making the call.
You can help to reduce the latency in the high availability Group by installing it asynchronously to the Primary.
Thoughts?
Sorry guys, I missed these two posts.
We're currently exploring log-shipping as an option (but it's not clear what that will do to our 3rd-party (again) backup solution.We don't currently (AFAIK) have AG in place but it may be a good idea.
We could make an AG group on the same server as the current databases and the replicate those across (if my understanding is correct).
That is pretty much right. The AG will have it's own network name and could be synchronously bound to two servers for high-availability. A third server (the one you use for reporting) would be asynchronously installed with the database on the same server as your other databases. For this database you can refer to it directly rather than using the network name so that you always retrieve the data you want locally.
At least, that is what I would do with the information I have.....
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply