Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mcdye
Starting Member
3 Posts |
 Posted - 2014-12-11 : 14:22:41
|
| I am hoping to get some guidance on the best approach for a read only copy of a database that is ~ 1TB for reporting purposes. The primary database is fed nightly with an ETL process. We are currently trying to duplicate the ETL to read only server but that process is not going well. So we are looking at other options to let SQL make the copy. The primary database is on a Win12R2 with SQL 12 or 14, a 2 node A/P failover cluster.The read only copy will be on a Win12R2 with SQL 12 or 14. It is not a requirement to fail over to the read only copy if the primary should go down.What would best the approach to accomplish the end result? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-12-11 : 14:25:20
|
| You can use transactional replication, but I think I would rearchitect the existing system to use an Availability Group where you can have a readable secondary. Use a 3-node AG cluster: primary ETL server, failover server for ETL and then readable secondary. You could get away with having the failover server be the readable secondary so that you could eliminate the third server, but that would depend on your requirements.This assumes Enterprise Edition.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
mcdye
Starting Member
3 Posts |
Posted - 2014-12-11 : 15:03:18
|
| Thanks for the reply. We do have the option to redesign with 3 servers at our disposal. We do have to buy ENT license so that is factor too.Not sure I follow your comment "You could get away with having the failover server be the readable secondary so that you could eliminate the third server, but that would depend on your requirements." A two node WSFC then a AG setup with a level of failover? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-12-11 : 15:13:11
|
| You wouldn't be using a failover cluster if you used AG. It's a similar technology but different. The servers would still be in a Windows cluster, but it wouldn't be a SQL Server failover cluster. AG provides failover and readable secondaries. It is done at the AG group level and not at the SQL instance level like a SQL Server failover cluster would be. I would start researching AGs to see if it meets your needs, that's if EE licenses are an option.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
DB Read only Copy