August 24, 2015 at 10:59 am
Hi There.
We need to make our UK database available to US and East Asia sites available but our bandwidth is not very good.
Functionality wise, we would like something like Mirroring with a Snapshot database so that the "replicated" site can read the database locally.
Unfortunately, this requires SQL Enterprise edition on the reading site if I get this right...
Is there a cheaper way to achieve this sort of functionality?
Many Thanks
Eric
August 24, 2015 at 11:07 am
Log Shipping is cheap and easy AND Standard Edition!! I have been using this every since it first came out. STANDBY mode on the restores allow you to have read-only workloads against the database in it's current state. Note that you cannot restore while other connections are active, but there are a number of options to deal with that depending on your needs.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 25, 2015 at 1:29 am
Might be what we need...
If I understand properly, the Secondary databases are available for reading but not during restores...
Could you point me to where I can find best practices for this, please?
I suspect it might not be acceptable for our users to be quicked out once in a while...
Many Thanks 🙂
Eric
August 25, 2015 at 8:08 am
Eric Mamet (8/25/2015)
Might be what we need...If I understand properly, the Secondary databases are available for reading but not during restores...
Could you point me to where I can find best practices for this, please?
I suspect it might not be acceptable for our users to be quicked out once in a while...
Many Thanks 🙂
Eric
There is no best practice. What is right for your entity could be completely hosed up for another.
You have very limited options for what you want to do (cheaply). So you either wait for reports to finish and then do restores or you kick them out forcibly and do restores. It's that simple. There are relatively simple automations you can do for either choice. You can also create your own queuing mechanism for reports or read-only queries so you control what gets fired when, which can help (but not eliminate) read-only workloads blocking timely restores. Lot of hassle for minimal gain - trust me on that. :hehe:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 26, 2015 at 5:31 am
You may also want to look at tranactional replication.
August 26, 2015 at 6:53 am
I tend not to like that one because I find it too complicated and sometimes looking like a black art.
I have not seen installations running smoothly without an "expert" lurking around just in case it goes wrong... which it seems to easily do for various reasons.
Also, I want the entire database replicated, not just some tables and/or columns.
I'd probably rather go for "Mirroring" or "Always On" although there is a cost to it...
August 26, 2015 at 8:16 am
Eric Mamet (8/26/2015)
I tend not to like that one because I find it too complicated and sometimes looking like a black art.I have not seen installations running smoothly without an "expert" lurking around just in case it goes wrong... which it seems to easily do for various reasons.
Also, I want the entire database replicated, not just some tables and/or columns.
I'd probably rather go for "Mirroring" or "Always On" although there is a cost to it...
Replication can have SUBSTANTIAL overhead in multiple ways, and indeed it is finicky. I avoid it like the plague, and advise my clients to do the same, whenever possible.
The "cost" of Always On is MUCH more than just paying up for Enterprise Edition. It is a very complex subsystem with LOTS of bugs, caveats, limitations, provisos, gotchas, etc. You also cause modification of PRIMARY data structures with the 14-byte version store pointer for modified rows (with multiple costs associated with that) just by making a secondary readable. Most people aren't aware of that particular nicety. :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 26, 2015 at 3:08 pm
You are not selling that one too well!!! :crying:
Let's hope that Log shipping does the trick for our users (I set up a test and it seemed so easy)
August 27, 2015 at 6:48 am
I see there is already an approved answer selected so wanted to waste everyone's time anyway 😉
I don't see many suggestions for using Change Data Capture (CDC) in an SSIS package to transfer the data to the "read only" server. I feel it's a viable reporting solution and not very difficult to set up. I've also read a couple of articles where it's a viable solution for data warehousing.
Steve
August 27, 2015 at 7:17 am
I must admit I came across CDC before and it might be a viable option too.
I don't have any experience of it though...
Has it got a reputation for reliability? I really was not impressed by replication.
Would it be likely to generate less network traffic do you know?
August 27, 2015 at 7:32 am
When used with SSIS, you would schedule a job to port the data over from the _CT tables, so I think it's less traffic than replication might have. I haven't come across many "negative" arguments on CDC. It even seems a decent way to keep a SYSTEST, or UAT environment up to date.
Here's a good msdn link to get you started:
August 27, 2015 at 7:45 am
Eric Mamet (8/27/2015)
I must admit I came across CDC before and it might be a viable option too.I don't have any experience of it though...
Has it got a reputation for reliability? I really was not impressed by replication.
Would it be likely to generate less network traffic do you know?
1) Change Data Capture is Enterprise Edition only. So not a viable solution for the OP's stated needs.
2) CDC is a FAT construct!!
3) It stores all changes. Many reporting systems only care about the state of data at a given moment in time. This is clearly the case for the OP since he could use log shipping or mirroring snapshots.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 27, 2015 at 8:04 am
TheSQLGuru (8/27/2015)
Eric Mamet (8/27/2015)
I must admit I came across CDC before and it might be a viable option too.I don't have any experience of it though...
Has it got a reputation for reliability? I really was not impressed by replication.
Would it be likely to generate less network traffic do you know?
1) Change Data Capture is Enterprise Edition only. So not a viable solution for the OP's stated needs.
True, so this option is not a viable one for the OP, and a total waste of time like I said in my first reply.
2) CDC is a FAT construct!!
What do you mean by this?
3) It stores all changes. Many reporting systems only care about the state of data at a given moment in time. This is clearly the case for the OP since he could use log shipping or mirroring snapshots.
I agree, but there are a few ways with handling snapshot reporting. The OP did say that he wanted the data as near "real time" as possible.
August 27, 2015 at 8:29 am
CDC is not available in Standard Edition.
There are other forms of SSIS DataFlow tasks that could do the job, but they put an unnecessary query load on the source server, a task must be created in SSIS for each table, and then the tasks must be modified whenever the data model or schemas change. For those reasons, SSIS is really not a practical tool for keeping two databases in sync.
The beauty of transaction log shipping / restoration is that it leverages the regular transaction logs generated on the source server whenever DML and DDL operations occur, so there is no additional overhead.
Kevin can confirm for me, but I believe that no special accomodations need to be made to transaction log replication when the data model or schemas change, because the transaction logs include DDL operations. Once setup, it just works seamlessly from that point.
You had mentioned a concern about users getting knocked off when the transaction logs are restored on the target server. This will be an issue, if the target database is automatically restored like clockwork at regular intervals. However, one way to work around this is to instead have the scheduled job first query the sys.dm_exec_sessions table at short intervals, like every five minutes, and then have a 2nd job task that kicks off the restore only during windows of inactivity.
select session_id, status
, last_request_start_time, last_request_end_time
from sys.dm_exec_sessions where session_id >= 51;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 27, 2015 at 9:44 am
Thanks for these feedback, it's really interesting.
We do have Entreprise edition on our main server but it would be nice to stick to Standard edition for the "read only" servers.
Looks like both CDC and Replication are too "complicated".
Log shipping is in the lead!
I like the idea of timing the restore based on the activity, however, I suppose there will always be various sessions inactive.
Would you just filter on sessions active?
select session_id, status
, last_request_start_time, last_request_end_time
from sys.dm_exec_sessions
where session_id >= 51
and status = 'Running'
and session_id <> @@SPID;
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply