December 11, 2014 at 12:31 pm
I am hoping to get some guidance on the best approach for a read only copy of a database that is ~ 1TB. 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?
December 11, 2014 at 12:46 pm
cdye (12/11/2014)
I am hoping to get some guidance on the best approach for a read only copy of a database that is ~ 1TB. 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?
Are the 2 servers on the same site? Well connected network? Spare network capacity?
What have you considered so far?
The secondary instance will need to be the same version of sql server as the source
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 11, 2014 at 1:19 pm
Perry Whittle (12/11/2014)
Are the 2 servers on the same site? Well connected network? Spare network capacity?
What have you considered so far?
The secondary instance will need to be the same version of sql server as the source
We have the existing 2 node WFC on the same domain between Pri/Sec data centers fiber connected. Then a 3rd standalone available for the reporting read copy.
We had 1 system in the past that was Transaction Rep (sunrise), the vendor supported and provided the setup. This system is Epic/Clarity, getting the impression we are on own to set up a secondary report server. We have the option to redo the current 2 node WFC.
December 11, 2014 at 1:36 pm
I tried again and here are the results.
This is not a production box so I can blow it away if necessary.
ALTER DATABASE Staging SET ONLINE;
Msg 5120, Level 16, State 101, Line 37
Unable to open the physical file "L:\MSSQL\Log\Staging.ldf". Operating system error 5: "5(Access is denied.)".
File activation failure. The physical file name "L:\MSSQL\Log\Staging.ldf" may be incorrect.
Msg 5170, Level 16, State 1, Line 37
Cannot create file 'D:\MSSQL\Data\Staging_log.ldf' because it already exists. Change the file path or the file name, and retry the operation.
Msg 5181, Level 16, State 5, Line 37
Could not restart database "Staging". Reverting to the previous status.
Msg 5069, Level 16, State 1, Line 37
ALTER DATABASE statement failed.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply