upscaling or upgrading? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

upscaling or upgrading?

Author

Topic

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-26 : 23:12:30

Guys I need your help and to tell you honestly I have no idea of what to look for.

Management informed me that we are expecting growth in the very very near future and that means more data to store and retrieve.

My problem is what kind of setup is ideal for 50GB + database(s), budget should not be an issue, just give them to me straight

Here are my feeble attempts
1. keep the existing server setup, add some SAN for storage only or higher capacity drives
- should I worry about performance deterioration?

2. buy a complete set of server solution (which i have no idea)
- this should be able to support more than 1 50GB+ databases or do you suggest to dedicate one database per server? (my thinking is that this is not practical and will be a pain to maintain, if let's say I have more than 5 databases = 5 servers?)

pretty please?

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2005-10-27 : 01:25:24

How many users? 50GB with one connected user is easy

... however, if the databases are going to get hammered you might want one server / database.

I can't help you at this scale/size, but my view would be that if this is, say, a 2x jump in size (and more importantly User Activity) and your server is below 50% CPU then adding more disk space is fine.

If its a bigger jump, and you don't have PLENTY of CPU & RAM to spare, then new servers are better, and you are then probably heading for one server / DB ...

Where are you going to back up to? Local disks then to tape / copy to other network drive?

If so you need some serious local disk storage for that - how long are you going to keep the backups online? Assuming 130% for the Logs, a reindex is probably going to fill that - plus what ever normal daily activity will generate in the logs - say a couple of days "online" ... that's looking like 150 - 250GB for TLog backups, plus 2 x 50GB daily backups, plus 50GB MDF plus 75GB LDF file - that looks like 400GB "elbow room" for 2 days backups and database for each 50GB database.

But I'm just thinking out loud ...

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-27 : 01:48:20

Thanks Kristen,

actually, your estimates are very close...
and i plan to have atleast 5 days of backup files

and users are going to increase from 50-100

backup is local then tape

so if the database size increases still from 50GB+ plus and i have other databases in the same server too, am i just looking for a really big storage device?

Do I look for SAN or just add additional drives locally?
Do I need to transfer/re-distribute the db files to those new drives,
if SAN, have you experienced any drawbacks, network problems and the likes?

any kick-ass hardware you can recommend?

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2005-10-27 : 02:14:20

"i plan to have atleast 5 days of backup files"

We use once-a-week FULLs and Daily DIFFs to allow us to store more (elapsed time) backups online (before having to resort to restore-from-Tape). You'd need to keep the FULL online until the DIFFs are worthless, so you might be able to do

Full-1
Diff-2
Diff-3
Full-4
Diff-5
Diff-6
Full-7 & delete Diff-2
Diff-8 & delete Diff-3 & Full-1
Diff-9 & delete Diff-5
...

Or Fulls once a week, but always have two and delete Diffs when more than 5 days old

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-27 : 02:31:01

the backups will need to be full + tlogs

and done daily

from experience this type of setup has saved me a lot of headaches
and overtimes

i am thinking however of only backing up "transactional" tables regularly and periodically backup "setup" tables using filegroups, which will save me 75% of space, but that's for the backup...

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2005-10-27 : 02:31:57

Add LiteSpeed - or have you got that already?

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-27 : 02:38:39

no LiteSpeed, not yet, I'll take a look later on if the usual backup routines start to fail

my major concern now is to identify specs for this "new server"
so far, users informed me that performance is still ok,
but i don't want to sit around and wait for the disaster to happen knowing that i could have prevented it in the first place

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2005-10-27 : 02:43:03

"if the usual backup routines start to fail"

I was thinking more of whether it would save you some disk space

"identify specs for this "new server""

You'll have to wait for the others to wake up! for advice on that - too big for me to know anything useful I'm afraid.

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-27 : 03:18:45

your ideas on the specs were already helpful enough

for the backup, if i can get the new setup going, i may not need the 3rd party tool (i'm kinda paranoid with regards to 3rd party tools

)

i was glad someone is awake already to give me some ideas, and how come you're awake so early or so late perhaps?

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2005-10-27 : 03:24:12

"how come you're awake so early"

I like to get a few hours work done before the day starts ...
... this morning I have to make some changes to an order transfer system.

There is no test rig, so this has to be run on their live Oracle system (Yeah yeah, I know, but what can I do?!)

So I'm running the old process to clear anything in the queu, updating the SProc, placing an order, running the process, and then putting the old sproc back - in the hope that at 5am no-one is placing any real orders in the "window of opportunity" I'm creating!

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-27 : 03:39:56

Your title suits you then TEST

[whip on]
set @whipcount=0
while @whipcount!=50
begin
whip 'Kristen'
set @whipcount=@whipcount+1
end
[/whip off]

quote:
Originally posted by Kristen

There is no test rig, so this has to be run on their live Oracle system (Yeah yeah, I know, but what can I do?!)

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2005-10-27 : 04:32:06

Its a bit early for that sort of thing Jen!

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-27 : 04:40:05

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2005-10-27 : 14:19:49

Are the rest of them STILL not awake?

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-27 : 14:59:34

What is your current config? How many CPUs, how fast, how much RAM, database size, how many concurrent users, etc...? Outside of the hardware, what in the config is changing? Such as concurrent users, database size, width of tables, etc...?

What is the current performance on the server? Average CPU, average free memory, buffer cache hit ratio, and lots of other userful perfmon counters...?

The size of the database is not what will constitute the size of the server. Just having the database size will only tell us to tell you to increase your storage size, regardless if it's local drives or SAN already.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources