February 1, 2019 at 10:08 am
I am running sql server standard 2012 with SP3.
I am thinking about setting up database mirroring to a report server.
The database is 3.5 Tb.
Is the mirrored database on the secondary server fully accessible? Can I use it for a report server?
I assume only tlogs are replicated to the mirror server?
February 1, 2019 at 10:30 am
dhydier - Friday, February 1, 2019 10:08 AMIs the mirrored database on the secondary server fully accessible? Can I use it for a report server?
No and No. You might want to create Database Snapshots on the Mirror database to be able to query the data, but Snapshots are not supported in standard edition.
February 1, 2019 at 11:25 am
dhydier - Friday, February 1, 2019 10:08 AMI am running sql server standard 2012 with SP3.
I am thinking about setting up database mirroring to a report server.
The database is 3.5 Tb.
Is the mirrored database on the secondary server fully accessible? Can I use it for a report server?
I assume only tlogs are replicated to the mirror server?
Replication or log shipping would be options with SQL Server 2012 standard. Which option depends on your needs and environment. Log shipping has some issues for reporting - especially with the need for exclusive access when restoring logs. Some can live with it or allow more latency with applying the logs but it can be too much of a disruption for some.
Sue
February 1, 2019 at 12:58 pm
Sue_H - Friday, February 1, 2019 11:25 AMdhydier - Friday, February 1, 2019 10:08 AMI am running sql server standard 2012 with SP3.
I am thinking about setting up database mirroring to a report server.
The database is 3.5 Tb.
Is the mirrored database on the secondary server fully accessible? Can I use it for a report server?
I assume only tlogs are replicated to the mirror server?Replication or log shipping would be options with SQL Server 2012 standard. Which option depends on your needs and environment. Log shipping has some issues for reporting - especially with the need for exclusive access when restoring logs. Some can live with it or allow more latency with applying the logs but it can be too much of a disruption for some.
Sue
Hi Sue, thank you for your reply.
Would it be a bad idea to setup transactional replication of the whole database between the 2 sql servers? They are connected by 10Gb fiber.
February 1, 2019 at 1:24 pm
dhydier - Friday, February 1, 2019 12:58 PMSue_H - Friday, February 1, 2019 11:25 AMdhydier - Friday, February 1, 2019 10:08 AMI am running sql server standard 2012 with SP3.
I am thinking about setting up database mirroring to a report server.
The database is 3.5 Tb.
Is the mirrored database on the secondary server fully accessible? Can I use it for a report server?
I assume only tlogs are replicated to the mirror server?Replication or log shipping would be options with SQL Server 2012 standard. Which option depends on your needs and environment. Log shipping has some issues for reporting - especially with the need for exclusive access when restoring logs. Some can live with it or allow more latency with applying the logs but it can be too much of a disruption for some.
Sue
Hi Sue, thank you for your reply.
Would it be a bad idea to setup transactional replication of the whole database between the 2 sql servers? They are connected by 10Gb fiber.
No, it would not be a bad idea. This was, and still is, a very good method to offload reporting.
Here are some things to think about.
1 You probably do not need the entire database for reporting. You can pick and choose the tables you need, and only replicate the data for reporting.
2. You can tune the destination database for reporting. The indexes on the current system may not be optimal for reporting.
3. Depending upon the nature of the database, it may make sense to break the articles into multiple publications. In the past, I have created different publications that were either broken into equal parts based upon usage, or "like tables", such as all client and related tables.
You also have availability groups available to you, although SQL 2012 was the first version that offered it. It is far more robust in later versions.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 1, 2019 at 1:45 pm
dhydier - Friday, February 1, 2019 12:58 PMSue_H - Friday, February 1, 2019 11:25 AMdhydier - Friday, February 1, 2019 10:08 AMI am running sql server standard 2012 with SP3.
I am thinking about setting up database mirroring to a report server.
The database is 3.5 Tb.
Is the mirrored database on the secondary server fully accessible? Can I use it for a report server?
I assume only tlogs are replicated to the mirror server?Replication or log shipping would be options with SQL Server 2012 standard. Which option depends on your needs and environment. Log shipping has some issues for reporting - especially with the need for exclusive access when restoring logs. Some can live with it or allow more latency with applying the logs but it can be too much of a disruption for some.
Sue
Hi Sue, thank you for your reply.
Would it be a bad idea to setup transactional replication of the whole database between the 2 sql servers? They are connected by 10Gb fiber.
Like Michael said 🙂 I think it would be a good idea.
One other thing to mention as an FYI....you can break things apart, have different publications, etc as already mentioned. If you want to replicate the whole database, take a look at initializing with a backup. For me, I think it makes things easier with larger databases like yours.
Sue
February 4, 2019 at 11:32 am
Hi Sue and Michael,
Thank you so much for your help,
Do either of you have experience with snapshot replication? I read that if the tables do not have primary keys transactional replication will not work for them.
What about snapshot replication? Are there cons to this? I have a 3.5 TB database and my data might change 5% each day. I am planning on replicating once a day in the early morning. Does that mean that the snapshot will grow to about 200 GB.
February 4, 2019 at 11:56 am
dhydier - Monday, February 4, 2019 11:32 AMHi Sue and Michael,
Thank you so much for your help,
Do either of you have experience with snapshot replication? I read that if the tables do not have primary keys transactional replication will not work for them.
What about snapshot replication? Are there cons to this? I have a 3.5 TB database and my data might change 5% each day. I am planning on replicating once a day in the early morning. Does that mean that the snapshot will grow to about 200 GB.
Snapshot replication can replicate tables without a primary key where transaction replication can't. Whatever the size is of the publication is what would be in a snapshot. Snapshot replication doesn't track changes. If I had 6 tables in a publication for snapshot replication, the schema and data for all 6 tables are in the snapshot. If the publication is large and you want to replicate daily then it's a large snapshot daily so that may not be feasible. Snapshot isn't going to care about the changes as it's only going to grab the publication schema and data as it is at that point and create the snapshot.
Sue
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply