locked
Mirroring, Snapshot Replication, load balancing and other BIG questions RRS feed

  • Question

  • You will all have to excuse my ignorance. I'm a developer who also doubles up as a development DBA. I am however not particularly knowedgeable about all the really important DBA stuff.

    We've built a small BI solution using SQL Server 2000. Our problem is that our server is getting on in years (5) and doesn't really have enough disk space or grunt. We havce a number of summary cubes that we've optimised quite successfully but our billing line level cubes run to 60 million rows and, well, they're about as quick as a dead ferret. Especially given the stupid queries our data analysts keep running.

    We have however proved our point. That this can be done and indeed SQL Server can do it. So we're now looking at some infrastructure spend and some new copies of SQL2005.

    But i need some advice. Our user base is climbing through the roof, we originally had 10, now we have closer to 50 and at this rate it'll be a couple of hundred by the end of the year. We're using a plugin called XLCubed to deliver that data into Excel from the Analysis Server.

    The OLTP database that sits behind it is fairly robust but we have a number of web based apps (mostly lookup systems) that want to use the nice shiny new accurate tables of data we have created.

    So I'm looking at a fairly big server to hold the OLTP DB, this will also serve up live data to our web apps. Its worth pointing out that the source data system is a batch system that processes overnight so we load data from yesterday at 6pm each evening and process our cubes and stuff overnight. Thus the data is a couple of days out of date. Don't laugh they used to use MS Access and got one mangy data set a month so this is a massive leap forward.

    I wanted to mirror the DB to another machine but I also want to have a separate Cube Server. I wondered if the cube server could use the mirror to read its data from as opposed to loading the Main Server (the mirror would be an identical box) we would also have a separate box running some of our other systems acting as the witness.

    I also wonderd about exporting the Cubes onto file shares for use locally as opposed to via the server which is how they connect now.

    We have been using Reporting Services and some of the queries the devs write are not exactly efficient. So I was also planning on clustering a pair of smaller servers into a reporting farm. Could I use another SQL Server to serve data up to them? Could I use a DB snapshot to copy the data required to this server? What are the time / size implications of using a snapshot and replicating it over each night?

    Any suggestions for places to read up on this? I've looked at the MS marketing blurb and while its big on buzzwords its light on specifics. Like how it actually works and how you would actually configure it to do some of this and what the implications would be.

    Any advice?

     

    many thanks


    Steve

     

     

    Wednesday, March 22, 2006 3:38 PM

Answers

  • I stumbled across transactional replication while trawling through the other forums. Its mentioned in passing in a lot of the MS blurb but I wondered what the performance issues might be and also what kind of network issues might arise.

    I assume it acts like conventional replication in SQL Server with a publisher and a number of subscribers? But at a transactional level (a continuous stream of updates) as opposed to the scheduled updates flavour we currently use?

    The idea of a snapshot appeals to me a great deal as the finance bods like their data to be stated at the end of each month. Also all the report servers and cube servers could easily be driven from a slightly out of date snapshot.

    Because of the batch based nature of our front line systems our data processing takes place overnight and we load the data the following day. Because we don't have enough grnt in our existing system we reprocess the cubes overnight hence the current 2 day delay.

    A new arrangement of servers is planned to allow the cubes to be reprocessed while the users are still using them.

    I'm curious as to the mechanics of a snapshot. How does it take place, how much data gets moved etc...

    Can you point me at anything I can read that would enlighten me?

     

    many thanks

     

    Steve

    Friday, March 24, 2006 8:43 AM

All replies

  • Steve,

    You have several options. A database mirror supports the ability to create a point in time read only view called a database shapshot. This snapshot can be used for reporting. Alternatively, you can choose transactional replication or log shipping to populate a tier of read only servers. Replication is likely the best choice if you want near real time data access for multiple reporting servers.

     

    Matt Hollingsworth

    Thursday, March 23, 2006 6:07 PM
  • I stumbled across transactional replication while trawling through the other forums. Its mentioned in passing in a lot of the MS blurb but I wondered what the performance issues might be and also what kind of network issues might arise.

    I assume it acts like conventional replication in SQL Server with a publisher and a number of subscribers? But at a transactional level (a continuous stream of updates) as opposed to the scheduled updates flavour we currently use?

    The idea of a snapshot appeals to me a great deal as the finance bods like their data to be stated at the end of each month. Also all the report servers and cube servers could easily be driven from a slightly out of date snapshot.

    Because of the batch based nature of our front line systems our data processing takes place overnight and we load the data the following day. Because we don't have enough grnt in our existing system we reprocess the cubes overnight hence the current 2 day delay.

    A new arrangement of servers is planned to allow the cubes to be reprocessed while the users are still using them.

    I'm curious as to the mechanics of a snapshot. How does it take place, how much data gets moved etc...

    Can you point me at anything I can read that would enlighten me?

     

    many thanks

     

    Steve

    Friday, March 24, 2006 8:43 AM
  • There should be plenty of general information about transactional replication out there (web, Books OnLine, etc.). Nothing revolutionary has changed in SQL Server 2005 for transactional replication.

    As far as Database Snapshots go, there is detailed information in Books OnLine on how they work. Basically a database snapshot "shares" the unchanged data with the base database, and uses "Copy On Write" to store the changes. The amount of resources (memory, CPU) that a database snapshot uses is approximately the same as a normal database. i.e. Creating a Database Snapshot affects the server about as much as adding another database.

    Thanks,

    Mark

    Thursday, April 6, 2006 6:21 PM