April 6, 2016 at 9:12 am
I have a database that must be refreshed hourly from sql server 2014 enterprise to 2014 sql server standard. Server 1 is enterprise and server 2 is standard. Server 2's database will be read only. the database are about 200 gigs.
I need all the tables replicated to the new read only server. The schema's change often. I was planning on using log shipping because the entire db needs replicating and the fact that schemas change often. Is my logic correct. Someone suggested snapshot replication but i though snapshot replication would have to be set up to drop and recreate the tables hourly and hence make them unavailable for a period of time
Which would you suggest?
April 6, 2016 at 9:32 am
I have done log shipping like this for clients many times over the years. Easy-peasy and works-fine-lasts-a-long-time. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 6, 2016 at 2:47 pm
Your analysis looks correct. Given your requirements, I cannot think of a better solution out of the box than log shipping, and it should do everything you want. Snapshot replication does have an outage while the tables are dropped and recreated.
April 8, 2016 at 1:51 pm
Snargables (4/6/2016)
I have a database that must be refreshed hourly from sql server 2014 enterprise to 2014 sql server standard. Server 1 is enterprise and server 2 is standard. Server 2's database will be read only. the database are about 200 gigs.I need all the tables replicated to the new read only server. The schema's change often. I was planning on using log shipping because the entire db needs replicating and the fact that schemas change often. Is my logic correct. Someone suggested snapshot replication but i though snapshot replication would have to be set up to drop and recreate the tables hourly and hence make them unavailable for a period of time
Which would you suggest?
Log shipping would be your easiest route
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 14, 2016 at 2:20 pm
is database mirroring available in sql server 2014 standard?
April 14, 2016 at 4:18 pm
Mirroring does not allow for readable secondary.
In order to read the secondary for log shipping you'll have to enable STANDBY mode and there's potential that users will get kicked out when the transaction logs are restored. There's also issues with statistics in standby mode.
You can replicate schema changes with replication: https://msdn.microsoft.com/en-us/library/ms152562(v=sql.120).aspx (you can also do a lot more like trim down the set of replicated data and modify indexes)
Plenty have people are now going the Availability Groups route now, in order to get readable secondaries "without too much trouble" famous last words :hehe:
April 14, 2016 at 4:30 pm
Jon.Morisi (4/14/2016)
Mirroring does not allow for readable secondary.In order to read the secondary for log shipping you'll have to enable STANDBY mode and there's potential that users will get kicked out when the transaction logs are restored. There's also issues with statistics in standby mode.
You can replicate schema changes with replication: https://msdn.microsoft.com/en-us/library/ms152562(v=sql.120).aspx (you can also do a lot more like trim down the set of replicated data and modify indexes)
Plenty have people are now going the Availability Groups route now, in order to get readable secondaries "without too much trouble" famous last words :hehe:
If you can go the Availability Group route that means you are on the Enterprise Edition of SQL Server on all machines, which the OP is not for the secondary here. And if they were on the EE, they could do snapshots of the old-school mirroring secondary to get a point-in-time readable copy of the database.
There's also the NASTY 14-byte version store pointer applied to modified rows ON THE PRIMARY that occurs if you establish a secondary as readable for any purpose in Always On.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 15, 2016 at 6:29 am
Does sql server 2016 have anymore options? Theoretically we could change this to sql 2016 ent. for the primary and sql 2016 standard for the read only secondary reporting db.
April 15, 2016 at 8:04 am
SQL 2016 has a LOT of wins, but I don't know of one here for you. You can now do a "Basic Failover Group" on Standard Edition, but it is really just slightly glorified mirroring.
https://msdn.microsoft.com/en-us/library/mt614935.aspx
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 15, 2016 at 8:08 am
In addition, while I have not tested this in 2016, I can tell you from experience that you cannot mirror from Standard to Enterprise or vice versa in 2012. SQL sees the difference in versions between the two partners and prevents the mirror from forming.
April 15, 2016 at 9:35 am
but u can log ship from enterprise to standard?
April 15, 2016 at 9:59 am
Snargables (4/15/2016)
but u can log ship from enterprise to standard?
Yes.
April 15, 2016 at 11:04 am
jeff.mason (4/15/2016)
Snargables (4/15/2016)
but u can log ship from enterprise to standard?Yes.
I haven't tried it, but I wonder what happens when you do this and then create something on the Enterprise-edition Primary database that is disallowed on Standard, such as a column store index or you partition a table.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 15, 2016 at 11:11 am
TheSQLGuru (4/15/2016)
jeff.mason (4/15/2016)
Snargables (4/15/2016)
but u can log ship from enterprise to standard?Yes.
I haven't tried it, but I wonder what happens when you do this and then create something on the Enterprise-edition Primary database that is disallowed on Standard, such as a column store index or you partition a table.
I would expect that the restores of the log would work until you actually try to recover the database, at which time the restore would fail with Enterprise-only features. This is exactly BTW why mirroring is turned off in this scenario.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply