SQL Server Considerations
-
Hi all,
I was about to start a SQL Server setup in my company. I have few questions that I know some one here would be able to answer. The environments:
1.I would host about 16 Databases that will be accessed through Intranet and Internet.
2.There will be more that 150 clients logging into the servers.
Questions:
1) Can I host all the SQL Servers on one powerful machine under windows 2000?
2) Do I need to consider any specific management software to manage the server?
3) How many clients can log on to SQL Server at one time if we have one "per-processor license" also keeping speed in mind?
4.Is it a big deal for a SQL Server to host more than one Database that are being used for retail product order entry as well as data analysis reporting OR Is it just common to do that?
5. Any other advice or suggestions will also be appreciated.
I would be grateful for any help.
Thanks in advance.
-
quote:
1) Can I host all the SQL Servers on one powerful machine under windows 2000?
It depends, but generally I'd say yes. If one or more of the databases is huge or has a huge amount of activity, then it may impact performance for other databases. But, I've looked after SQL Servers that have hundreds of databases serving as back ends for individual applications or hosted web sites, and performance has rarely been an issue.
quote:
2) Do I need to consider any specific management software to manage the server?
Probably not at this stage. An important item is backup hardware and software. Once you use SQL Server's built-in facilities to backup your databases to disk, an important thing is to backup those disk backups to tape or other offline media.
quote:
3) How many clients can log on to SQL Server at one time if we have one "per-processor license" also keeping speed in mind?
Whenever I offer my interpretation of Microsoft's licensing I seem to get it wrong, so I won't offer an opinion
. MS should introduce a certification for understanding their licensing. It once took me 6 hours to track down the rules on licensing a standby server.quote:
4.Is it a big deal for a SQL Server to host more than one Database that are being used for retail product order entry as well as data analysis reporting OR Is it just common to do that?
A few years ago when there were more $$$ around, companies were often using separate servers for those tasks, but the trend appears to be (from where I sit) to combine these functions on the one server. It shouldn't have a great impact. Try it and see.
quote:
5. Any other advice or suggestions will also be appreciated.
I hardly know where to start.
From an administration point of view, make sure you have scheduled backups (with proper failure notification) and then take them to an offline medium. Test the full process of restoring databases to another server.
From a security point of view, get your security strategy worked out ahead. Don't let developers twist your arm to allow production applications to connect as sa or a db_owner id, just because that's the way it was done in development.
Maybe engage an experienced DBA for a day or so to audit your setup and make any recommendations, and give advice for ongoing admin.
Cheers,
- Mark -
This explains a lot. Thanks mccork.
-
quote:
Can I host all the SQL Servers on one powerful machine under windows 2000?
Yes but consider the traffic going to and from the server. I've found that the problem isn't the machine its the pipeline into the machine.
This is a particular problem if you have some analysis software that sends complex requests to your server.
Ideally I would buy two servers of similar spec and have half the databases live on each but have regularly updated copies of the other half available. That way if a machine blows up (a pretty rare occurence) you can simply switch to the other machine whilst repairs are carried out.
quote:
2) Do I need to consider any specific management software to manage the server?
Initially I would say no. As time goes on you need to think about back-up softare.
quote:
3) How many clients can log on to SQL Server at one time if we have one "per-processor license" also keeping speed in mind?
Ditto the certification exam for Microsoft licensing
That said, the literature that I have available says that for use in an intranet/extranet environment licensing is on a per processor basis. This makes sense because if you were running a SQL Server driven public web-site then how do you know how many people access the server?If your server is non intranet/extranet then it gets very complicated. Basically any device accessing the SQL Server counts as a connection even if it goes through some form of multiplexing system. Oh, and the server itself counts as one of the five connecting machines in the default five license set up.
quote:
Is it a big deal for a SQL Server to host more than one Database that are being used for retail product order entry as well as data analysis reporting OR Is it just common to do that?
I think you need to ask what the affect would be on your business if the order taking system went down or suffered degraded performance.
Your business could probably wait for a report but wouldn't want to stop taking orders.
You can manage both on a decent server, but you may have to have a strict schedule that restricts reporting hours to times outside the hours that your retail operation is active.
AOB
RAM is cheap and doesn't affect your licensing costs. The amount that SQL Server supports depends on the version of SQL Server and the version of W2K.
Do you have a development SQL server? I've got an old PII that I use as a personal development server so costs needn't be astronomic.
I went on the MS SQL6.5 courses and this really gave my knowledge a jump start. From my perspective I gained enough knowledge to begin to ask the right questions. In addition whilst on the courses the other people on the course had a wide variety of experience and industry viewpoints so I gained from them as well.
I know courses are expensive but good ones will give you a huge boost.
-
Dear pro2003,
My first question is - what're we gonna call you in 2004?
ok - here's one guy's humble opinion:
#1: listen to mccork on hardware - very important - get a REAL server...
#2: ram on pII machines is cheap, ram on REAL servers is not - you might have to fight the management tooth-and-nail on this - get at least 3GB and you'll be set for a while
#3: reporting on the data is equally important as the apps using the db's - don't skimp - here's an idea: get 2 servers, run all the online apps on one, use the other as a failover AND as the box you run reports from - run the reports as a job and output them to disk in crystal in xml format - that way your users don't need individual seat licenses for the reports - it's what we do and the best way to keep the licensing down while still providing dynamic querying of data via the xml...
#4: listen to david.poole - get your backups in order and your replication strategy to your failover box...
#5: if you need help w/development of reports, by all means, contact me - i'm looking for some contract work!
so, why don't you share the licensing wisdom with the group? i'm always looking for as much advice on that subject as possible.
good luck!
eric.
-
Also consider placement, security and firewall(s).
Do you realy want your Internet service accessing your sql server on your internal domain and your internal DB's?
We have internal sql server, another in DMZ and separate Internet sql server (which never connects to others) and I transfer data (DTS) from internal to DMZ, DMZ to internet.
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks all, this is really a great source of information.
1.As far as what you will call me in 2004,it will be pro2003 always saying when I joined this forum.
2. For the reports, I was looking for help a few weeks ago, apparently Crystal Reports doesnt work very well with MSDE and I really had to work hard with that, but now if any one has problems with MSDE and Crystal Reports I can probably help.
3. Internet would be a medium to connect to the server using an RDP Client, can that also be a security hazard for SQL Server where as the SQL Server Communication is practically happennig at the server itsself and not over the internet.
4. I have a test server that I am using to simulate the production enviornments. Is there a SOFTWARE THAT WILL HELP ME SIMULATE HUGE AMOUNT OF ACTIVITY?
5.Hardware: What is preferred a tape drive, External Firewire etc or back up on a hard drive or any other device.Personally speaking I have used Tape and External USB, External Firewire, works good for me.
6. Training, I am preparing for my 70-228.
Thanks all for the help I am greatful, now and for the answers to come.
-
Visual Studio has a tool called "Application Performance Explorer". I have to admit that I've never used it, but it is there.
I've heard of a tool called SQL HAMMER, not to be confused with SQL SLAMMER
For internet/extranet work we use an old package from Microsoft which I think was called WAZ. It's regarded as old hat now but it still works.
I've seen a package called LodeRunner when I was working at a client's site. The comment that was made about it was that it was brilliant, but it had so many parameters that it was difficult to set up correctly.
-
I haven't used it yet, but I found 231619 at support.microsoft.com that talks about how to use the SQLIOStress Utility to simulate I/O on your server.
Michelle
Michelle
. MS should introduce a certification for understanding their licensing. It once took me 6 hours to track down the rules on licensing a standby server.
That said, the literature that I have available says that for use in an intranet/extranet environment licensing is on a per processor basis. This makes sense because if you were running a SQL Server driven public web-site then how do you know how many people access the server?Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply