August 31, 2015 at 2:12 am
We are looking for different solutions of following requirements on SQL Server 2012/14 version:
1- High availability, 24X7
2- Well planned Disaster recovery
3- Online system (read only) for reporting purpose.
The current DB size is in terabyte and there would be thousand of entries per minute.
1- Solution for backup strategy?
2- Performance of application based on availability of Database?
3- How fast/frequently we can up the other/secondary system/(database/hardware) ready in case of disaster.
4- Is transnational Replication is a solution for online reporting? There would be continuous development in database (DDL), and would it be automatically propagate to subscriber?
5- Require Less maintenance for all these requirements and easy to solve issues in case they arrives .
6- Would AlwaysOn (High availability) configuration resolve all these issues?
7- Would AlwaysOn also update/replicate the schema changes (DDL) activity online?
https://msdn.microsoft.com/en-us/library/ff878253.aspx
Anyone who has any idea, Please share.
Regards,
Shamshad Ali
August 31, 2015 at 2:48 am
Availability Groups should do most of that (though you'd still need to design a backup strategy to meet your business SLAs). If you've never designed or implemented a HA/DR solution before, I'd recommend you get a consultant in to guide you. There's many ways to get it wrong, which with a HA/DR of a critical system could lead to serious consequences.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 31, 2015 at 3:28 am
Hello Gail, based on your answer, Could you please let me where to go for consultation? If you don't mind, can you guide me some material for research myself. The place where I am doing job now these days as DBA, I need to provide them solution for this as their consultant. So I need your help.
Please let me know if you can share something in this regards, I will be thankful to you.
Regards,
Shamshad Ali
August 31, 2015 at 3:33 am
Without knowing what part of the world you're in, it's a little hard to recommend a 3rd party.
There's a lot of material available. Perry wrote a Stairway that's available here. There's good info in Books Online, pluralsight courses, blogs, books. You can (and should) set up test VMs and try scenarios out.
I strongly recommend you don't make your first HA/DR design one that's for a mission-critical system of that size. You're doing your client a huge disservice if you tell them you can do it without experience. The best advice would be for them to get a specialist in.
Incorrectly designed/implemented HA/DR solutions can cost a business millions if there's a disaster and the implemented solution can't meet the SLAs. Companies have closed as a result.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 31, 2015 at 3:58 am
I need some links if you can share. Please don't mind. I want to learn.
I can provide you some details as required, if you take personal interest? Please share your email, contact. How can I reach you in person?
Its all I want to learn and get experienced, no one get learning by birth, every qualified consultant must have faced problems and then they understood what would be best for a system for HA/DR. I do not want to put my company in problem (its understood) but for my personal understanding and learning, I must achieve this goal.
Shamshad Ali.
August 31, 2015 at 4:12 am
Start with Perry's stairway, on this site, look at the left-hand menu, and Books Online (I hope I don't need to provide a link to that)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 31, 2015 at 9:46 am
shamshad.ali (8/31/2015)
I need some links if you can share. Please don't mind. I want to learn.
Please see my stairway series starting at this link
http://www.sqlservercentral.com/articles/Failover+Clustered+Instance+(FCI)/107536/[/url]
shamshad.ali (8/31/2015)
Its all I want to learn and get experienced, no one get learning by birth, every qualified consultant must have faced problems and then they understood what would be best for a system for HA/DR. I do not want to put my company in problem (its understood) but for my personal understanding and learning, I must achieve this goal.Shamshad Ali.
Read the stairway and if you're unsure you should at least get a consultant to come in and help you setup a test system and document the deployment\management so that ongoing you'll be able to deploy\manage the system yourself
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 2, 2015 at 10:36 am
Your SQL environment is only part of what you need to consider, and the optimal solution for your situation will depend on other factors.
* Do you have multiple data centers, if so what is the bandwidth you have or can move to between them.
* How is your storage organised, is it virtualised in any way within a single data center or between data centers
* What server virtualisation do you have in place
* Last but also very important, what portion of your solution are you willing to put into the cloud, and what are the capabilities of your preferred cloud supplier
I currently work at a charity, which means substantial discounts from hardware and software vendors, and the resulting costs mean that keeping our DR in house is the cheapest option. We operate out of two data centers in the UK, one in London the other 150 miles away in Wales and have excellent band width between the sites. Our storage is virtualised across both sites, with storage-layer replication that mirrors a given LUN in both locations. Our server layer is also virtualised using Hyper-V, and we can run any given server image at either site. We also replicate just about all data to a third site 250 miles away in Scotland that just has a file storage capability.
We have chosen to use SQL 2014 with clustering to provide our HA and DR. The underlying infrastructure allows us to run one or both nodes at either site, but typically we run only one node at a given site. Failover then becomes the normal failover time for a cluster, maybe a minute or two for the busiest systems.
We did consider using Availability Groups, but found this very difficult to set up at the infrastructure level. However, we do plan to try this again in a few months.
SQL Server gives some very good options for HA and DR, but you cannot choose what will be best for your organisation until you know the capabilities and limitations of the underlying infrastructure, and also the capabilities of the staff who will have to build and maintain what is put in place.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 2, 2015 at 11:30 am
To add
Alwayson availability groups do have a certain limitation especially around network performance and redo on readable secondarys.
Depends on the expected volume as to how it affects the 2 areas mentioned above.
A lot of requirements analysis and planning will be required for opting for a particular technology
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 2, 2015 at 10:39 pm
and the resulting costs mean that keeping our DR in house is the cheapest option. We operate out of two data centers in the UK, one in London the other 150 miles away in Wales and have excellent band width between the sites. Our storage is virtualised across both sites, with storage-layer replication that mirrors a given LUN in both locations. Our server layer is also virtualised using Hyper-V, and we can run any given server image at either site. We also replicate just about all data to a third site 250 miles away in Scotland that just has a file storage capability.
We have chosen to use SQL 2014 with clustering to provide our HA and DR. The underlying infrastructure allows us to run one or both nodes at either site, but typically we run only one node at a given site. Failover then becomes the normal failover time for a cluster, maybe a minute or two for the busiest systems.
We did consider using Availability Groups, but found this very difficult to set up at the infrastructure level. However, we do plan to try this again in a few months.
As mentioned earlier, I am looking for HA/DR solution, not yet implemented but I have to provide few options. Then based on budget and resources, management will decide. From your above quotes, Can you please share the diagram, it will be easy to understand then, how it is implemented. As mentioned, its part of my job and I know I have to study a lot but still you peoples' guidance to the point will help fasten my learning and produce something quickly that I can share.
Please help.
Shamshad Ali
September 3, 2015 at 5:47 pm
shamshad.ali (9/2/2015)
Can you please share the diagram, it will be easy to understand then, how it is implemented.
Have you even bothered to read my stairway article linked above?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 4, 2015 at 12:39 am
I have to provide few options. Then based on budget and resources, management will decide
You are right that technical staff need to supply the options and that management will decide which one to proceed with. However, this needs the technical staff to fully understand the implications of the options they are presenting, and have the skills to recommend which is best for the business.
From the questions you have asked I do not think you have the skills needed to recommend an option. If you recommend the wrong option or underestimate the work needed this could harm the survival of your business.
In past situations where I have been asked to recommend a solution that is beyond my skill level to understand or scope, I have outlined the possible options and risks but recommended that the business employ a consultant organisation to review the choices and recommend the best way forward.
Your Microsoft Account Manager will be happy to recommend suitable Gold Partners in your area that have the necessary skills, and can also suggest organisations from other countries if you do not feel confident about a local organisation. The consultant will bring to your organisation the skills needed to fully scope the options and workload, and will bring to you an opportunity to learn a lot from them while they do the work.
My suggestion is to be honest about your skill levels to yourself and your management. You obviously understand the possible options, and this forum has given some of the risks they have. Ask Microsoft or Google for suitable Gold Partner organisations and ask them about their skills in SQL Server HA and DR. Present this information to your management along with a recommendation to employ who you think will be the best organisation to identify what is best for your business.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply