3

We got a database of approximately 100-120 GB on a server with 128GB ram, while the db is sitting on an 256 GB SSD drive.

The bulk of the database space is spent on the tables below:

Page_contents (page_content_id, member_id, page_id, added_on, status)
Custom_fields (page_content_id, custom_field_key, custom_field_name)

and then statistics like

messages, visits, clicks table where we store the raw data (page_content_id, timestamp, page_campaign_id) on each table per status (visits on visits table, messages on messages etc)

Those tables take up pretty much 90% of the db space.

We had a blow up in production recently due to a failure on the raid controller and we are looking to see if the database can be replicated to a second equivalent server.

Questions:

  • What's the best way to do that on a SQL Server 2012 database?
  • Should we expect performance degradation?
  • Is the setup described above adequate for our size you think? We expect the database to keep growing.
  • Is there a way to optimize the schema above that you can think of. I understand you have little information.

EDIT: To answer some of the questions in the comments

The SQL Server is 2012 Enterprise edition. I am in control of what I can do and how I use the two boxes. I can decide to drop one box and double the power on the other one. It's really up to me.

I would like to leave questions 3 & 4 in case someone can think of optimizations of the existing schema. If there are questions to answer to clarify things I l happily do so.

My requirement is for the database to be accessible at all times. I don't mind losing some data (1 hour) in terms of the analytics, but can't sustain more than 5 minutes loss of data for the other two tables. If I can increase the performance as well as prevent downtime given the requirements above I would prefer that option for obvious reasons.

4
  • Bullet Questions #3,#4 seem too broad or unanswerable given the information you've given us -- I recommend editing that out of the question. Jun 25, 2014 at 17:06
  • Also, what version of SQL Server 2012 are you using? Enterprise? Standard? Web? Jun 25, 2014 at 17:06
  • What exactly are your requirements? What do you have available to you? How much data loss can you sustain? Jun 25, 2014 at 17:08
  • Edited my question to clarify
    – Yannis
    Jun 25, 2014 at 19:45

1 Answer 1

2

If I were you I'd be looking at AlwaysOn Availability Groups with a synchronous replica. It'll add a couple of milliseconds of latency to the SQL commands when they are run, but it'll give you a second server that's available in the event of a server failure without any loss of data. It requires no shared storage so you can keep using your local SSD drives.

It does require setting up Windows clustering but the SQL Server instances don't need to be clustered.

2
  • Given that the two boxes are on different data centers doesnt this mean that the latency added will be significantly more?
    – Yannis
    Jun 26, 2014 at 11:52
  • If they will be in two different data centers then you'll want to use asynchronous data movement which means you won't have any automatic failover. Because of this you'll want to have a local synchronous copy that you can use for automatic failover. Failover to an async copy is a manual failover, and doesn't allow for failback as the LSN chain will be broken.
    – mrdenny
    Jun 26, 2014 at 18:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.