sql server database replication?

I was given a spec sheet to build a sql sever environment which is highly available. I was told the servers need to be at two separate offices. Only one sever should be active at a time, but the secondary sever should always have the most recent database. Nothing is currently built. I'm up to suggestions and looking for the simplest and most economical solution.

Given the details below how can I setup a single instance sql cluster with database replication?

Primary and secondary site are virtualized with VMware
Primary and secondary site are utilizing two different vendor SANs
Windows Server 2012 R2 Standard on both sides to host SQL
SQL Server 2008 R2 Standard will be on both Windows servers
LVL 1
First LastAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Nothing is currently built. I'm up to suggestions and looking for the simplest and most economical solution
Given the details below how can I setup a single instance sql cluster with database replication?

I don't think the Replication would be the simplest and most economical solution. Did you think in others solutions?
Mirroring or Log shipping for example? A regional cluster perhaps?
0
First LastAuthor Commented:
I am purchasing licenses two allow for up to two servers with a copy of SQL 2008 R2 Standard on each.

Isn't Mirroring only available in Enterprise edition of SQL? I have SQL 2008 R2 Standard :/

If I do a regional cluster, does that take care of the database too? I thought clustering meant there was a shared storage SAN where the databases reside. I have two different SANS thus zero shared storage, because the SQL servers will be at two different offices.


I'm very new to this, a few hours into reading, and this link below is what I've used to help me understand different options.
https://nirajrules.wordpress.com/2008/12/08/snapshot-vs-logshipping-vs-mirroring-vs-replication/
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Isn't Mirroring only available in Enterprise edition of SQL?
No. It's also available for Standard Edition. Check here for Features by Edition.

I thought clustering meant there was a shared storage SAN where the databases reside
Yes it does but when you have a node (or more) in a separate location/datacenter you'll need to have the ability for SAN replication. So this depends on your SAN solutions.
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

First LastAuthor Commented:
My SANs are from different vendors. There is no real SAN replication. The best I have is some software called Veeam which will backup a drive from one virtual machine and replicate it to another virtual server in a different location. It is slow using changed block tracking. I'm looking for something faster.  

Considering what you know about my needs, what would you suggest?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
From my experience I wouldn't use Replication to replicate full database. It's good for replicate some tables or records but not full database. For that you can use mirroring or log shipping since geo-cluster may be complex and also not cheap.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Aaron TomoskySD-WAN SimplifiedCommented:
From your description, MS points you toward "Availability Group for HA and DR"
http://blogs.msdn.com/b/sqlcat/archive/2013/11/20/sql-server-2012-alwayson-high-availability-and-disaster-recovery-design-patterns.aspx

I've no experience with this personally, but the vendor recommendation is usually a good place to start.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.