Shared SQL Server

  • Ninja's_RGR'us (2/8/2011)


    Svs2010 (2/8/2011)


    @Ninja's- Yes we are thinking of getting the latest version only and have to make sure all other SQL servers can be upgraded and the applications supports them as well. so whole lot of work on each of the application.

    You have no idea. Even moving only 1 application is a tone of work. Moving many PLUS upgrades is a monster.

    @ninja's_RGR'us - yes I know it is going to be a huge project with monstrous work involved. we are at very initial stage and gathering the requirements to proceed further. (sometimes feeling like is it really feasible but have to present my findings even before deciding further).

    I am open for any suggestions and recommendations in this direction. Thanks.

  • One important thing I didn't see mentioned here as I breezed through was Disk I/O and SAN organization.

    For example, your TempDB is about to be shared to multiple reporting applications, which will use it for some sorting events. It's about to become much more heavily used. I would recommend looking into best RAID practices for this common system, including the difference between physical spindle I/O vs. shared physical disks with more spindles on logical LUNs.

    My apologies if I did miss it in all the advice given above.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Svs2010 (2/8/2011)


    @Oliiii - I will start analyzing these factors.

    so far we are manually analyzing these factors using SQL profiler, do you suggest any other tool to perform it automatically and get me the reports on daily basis?

    Actualy that's prety easy to do automaticaly.

    Set perfmon traces on all your machines to monitor the CPU, Disks and Memory (search the articles on this site to know which counter to monitor) and instead of saving it in a file, save it all to the same DB.

    You can then use sql server reporting services to make daily/weekly charts so you can compare prety much anything.

    That kind of data can take a lot of room, so i suggest you put it on a sql server 2008 enterprise (if you have one) and use page compression, and come up with a script to cleanup and only keep some averages.

    That way you can keep month worth of data while still keeping a small DB size.

    It's important to keep the data so you can compare your consolidated server load vs the standalone load, that might help you spot some migration problems.

  • The previous technical points are all valid, having supported several shared instances in the past. What has worked well was to have some ground rules identified for all of the applications that are targeted to be hosted within the instance. It provided for:

    - How planned outages were communicated an approved by all concerned (did everyone have to approve, or was notification enough? Was e-mail sufficient? etc.)

    - What about those unplanned or out-of-hours outages?

    - How do you handle 'rogue' application databases that suddenly grab all the resources? Is there a banishment process in place in case an application suddenly doesn't perform as advertised/misbehaves (and therefore gets throttled back?)? Is such a threshold set for everyone?

    - How was DR handled for all of the databases? Was there a specific order for recovery, or was everything supposed to be done all at once (how likely is that?) While you might get the instance back up, if any of the databases are sufficiently sized, you don't want to be recovering multiple terabyte databases simultaneously. Let the business sort this one out, and then get it in writing in your DR plan.

    - Absolutely advocate permissions granted by AD groups rather than individual grants. This gets messy REALLY quickly with shared instances, and should be a best practise in any case.

    With ground rules in place, the business knew what it was buying into by the 'saving' of money for individual hardware.

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply