April 25, 2018 at 9:23 am
Greetings,
The DB environment that I work in is primarily data warehousing. Copies of data imported through SSIS packages, etc. and stored for reporting purposes. Not a high availability environment, so the most we've done with replication type things has involved saving/restoring backups.
We've been asked by another department to take a nightly (at minimum) copy of their database and have it available for reporting. Their transactional database needs to not have the data crunching load that reporting could potentially cause. We would bring this reporting DB up on a separate virtual server with separate data storage.
I've been reading about DB transactional replication (no need to merge data) and Always-On. Some things I read make it sound like Always-On is an option under replication, and other things make it sound completely separate. It's confusing.
One thing this department has discussed is being able to create views and stored procedures in the reporting copy of the database. One solution that was presented was to restore their nightly backups in the reporting DB, but that would overwrite anything they created and also be a manual operation from hell.
Is there a site/document/chart that discusses a comparison of the technologies in one place? I've been bouncing around various web pages and each discuss implementing one solution and it would be nice to have something easier to interpret when deciding which technology to use. Also, if there's something newer than this, or better than this, I'd appreciate hearing about it. Does anyone have a recommendation based on your experience and my description?
The source DB is SQL Server 2014. The target would be SQL Server 2017.
Thanks, I appreciate any responses.
John
April 25, 2018 at 11:55 am
jvandermey - Wednesday, April 25, 2018 9:23 AMOne solution that was presented was to restore their nightly backups in the reporting DB, but that would overwrite anything they created and also be a manual operation from hell.
Is this database big?
The most feasible thing i see is restore production backups in your instance and scramble the sensitive data.
April 25, 2018 at 12:06 pm
Superwaffle - Wednesday, April 25, 2018 11:55 AMjvandermey - Wednesday, April 25, 2018 9:23 AMOne solution that was presented was to restore their nightly backups in the reporting DB, but that would overwrite anything they created and also be a manual operation from hell.Is this database big?
The most feasible thing i see is restore production backups in your instance and scramble the sensitive data.
120 GB
I want to set this up to be very low maintenance with low server/network impact. There's no transactional data being entered at night, but that's also when we do all our other DB and server backups, warehouse data imports, etc. Replication or Always-On SOUND like good solutions so far, but I just don't know which direction to go.
April 25, 2018 at 12:53 pm
jvandermey - Wednesday, April 25, 2018 12:06 PMSuperwaffle - Wednesday, April 25, 2018 11:55 AMjvandermey - Wednesday, April 25, 2018 9:23 AMOne solution that was presented was to restore their nightly backups in the reporting DB, but that would overwrite anything they created and also be a manual operation from hell.Is this database big?
The most feasible thing i see is restore production backups in your instance and scramble the sensitive data.120 GB
I want to set this up to be very low maintenance with low server/network impact. There's no transactional data being entered at night, but that's also when we do all our other DB and server backups, warehouse data imports, etc. Replication or Always-On SOUND like good solutions so far, but I just don't know which direction to go.
If i was in your shoes i would actually make a job that restores the backup at night.
Setting up Always-ON (AG or FCI) or replication on the long run will be harder to maintain than a couple of scripts.
But if you are going for the Always-On you should know that you would have to make a WSFC in both servers, production and reporting, the only way i would choose this or replication is if the business owners want the reports data on real time.
I'm not proficient on the Replication or Always-On side, i'm still a newbie to these.
Let's wait for the experts to see their opinion.
Greetings!
April 25, 2018 at 7:20 pm
As I understood, your use case is to have a copy of your DB for reporting purposes.
I don't think Always On or Replication fits in this scenario. Both are HA and DR solution.
Always On will be beneficial if you implement it on your transaction DB and have a asynchronous read replica for reporting purpose.
If you implement Replication then your target DB will be in restore mode always, which means un-readable by anyone.
April 26, 2018 at 10:21 am
Why not restore the database nightly and have them place their code in another database on the same instance. Then you would only need to create a database role on the restored database after restore that allows read only to the restored database. That would also allow you to contain write/ddl/exec permissions on the database that will contain their views and stored procs.
May 3, 2018 at 4:06 am
jvandermey - Wednesday, April 25, 2018 9:23 AMSome things I read make it sound like Always-On is an option under replication
Replication is not part of the AlwaysOn suite of technologies. AlwaysOn covers High Availability and Disaster Recovery, which replication doesn't strictly provide.
jvandermey - Wednesday, April 25, 2018 9:23 AM
and other things make it sound completely separate
It is
jvandermey - Wednesday, April 25, 2018 9:23 AM
It's confusing.
Yes it is, There's a lot of information on the internet which does confuse.
jvandermey - Wednesday, April 25, 2018 9:23 AM
One thing this department has discussed is being able to create views and stored procedures in the reporting copy of the database. One solution that was presented was to restore their nightly backups in the reporting DB, but that would overwrite anything they created and also be a manual operation from hell.
For them to persist with minimal effort they would need to exist in the source but that's probably not best
jvandermey - Wednesday, April 25, 2018 9:23 AM
Is there a site/document/chart that discusses a comparison of the technologies in one place? I've been bouncing around various web pages and each discuss implementing one solution and it would be nice to have something easier to interpret when deciding which technology to use. Also, if there's something newer than this, or better than this, I'd appreciate hearing about it. Does anyone have a recommendation based on your experience and my description?The source DB is SQL Server 2014. The target would be SQL Server 2017.
Thanks, I appreciate any responses.
John
Disparate versions of sql rule out alwayson technologies for replicating the database as the secondary copy would never be readable.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 3, 2018 at 8:51 am
mohammad.hafizullah - Wednesday, April 25, 2018 7:20 PMAs I understood, your use case is to have a copy of your DB for reporting purposes.I don't think Always On or Replication fits in this scenario. Both are HA and DR solution.
Always On will be beneficial if you implement it on your transaction DB and have a asynchronous read replica for reporting purpose.
If you implement Replication then your target DB will be in restore mode always, which means un-readable by anyone.
We should able to read the Replicated copy, Not sure why you mention this "If you implement Replication then your target DB will be in restore mode always, which means un-readable by anyone"
May 3, 2018 at 8:56 am
jvandermey - Wednesday, April 25, 2018 9:23 AMGreetings,
The DB environment that I work in is primarily data warehousing. Copies of data imported through SSIS packages, etc. and stored for reporting purposes. Not a high availability environment, so the most we've done with replication type things has involved saving/restoring backups.We've been asked by another department to take a nightly (at minimum) copy of their database and have it available for reporting. Their transactional database needs to not have the data crunching load that reporting could potentially cause. We would bring this reporting DB up on a separate virtual server with separate data storage.
I've been reading about DB transactional replication (no need to merge data) and Always-On. Some things I read make it sound like Always-On is an option under replication, and other things make it sound completely separate. It's confusing.
One thing this department has discussed is being able to create views and stored procedures in the reporting copy of the database. One solution that was presented was to restore their nightly backups in the reporting DB, but that would overwrite anything they created and also be a manual operation from hell.
Is there a site/document/chart that discusses a comparison of the technologies in one place? I've been bouncing around various web pages and each discuss implementing one solution and it would be nice to have something easier to interpret when deciding which technology to use. Also, if there's something newer than this, or better than this, I'd appreciate hearing about it. Does anyone have a recommendation based on your experience and my description?
The source DB is SQL Server 2014. The target would be SQL Server 2017.
Thanks, I appreciate any responses.
John
If you setup the Always on between Prod and Reporting server, You can't write anything in the Reporting db. You can read(to me it will work for your cause).
If you don't need all the tables from prod, If you look for few selected table data needed for reporting then you go for Replication. In replication you can replicate the selected table to Reporting server.
May 7, 2018 at 11:08 am
If you use always on, the copy is read only. They user will not be able to create views, procedures, etc. The work around is to create the objects on the primary, which will then transfer to the secondary. Or create a "reporting" database on the secondary and the various objects will need to refer to the read-only database in three part naming.
if you restore the backups, the users will be able to create objects and tune the database to suit their needs, but it will need to be re-created every day, and the data will be a day old. This would be my last choice.
IMHO, set up transactional replication.
This gives you a number of advantages:
1. Security can be changed as needed
2. The database can be tuned to support the reporting. The usage for reporting is likely very different than the OLTP database. You will be able to create indexes, as an example, that support reporting.
3. The data will be near real-time.
4. The users can create views/procs/etc so that they can get their jobs done easier.
5. You will be able to transfer only the tables that are required for reporting
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/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply