Server Design/Requirements - 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

General SQL Server Forums

New to SQL Server Programming

Server Design/Requirements

Author

Topic

znwpta
Starting Member

12 Posts

Posted - 2008-07-09 : 09:48:31

Hi,

I am new to the SQL Server, and I was hoping someone could help me with a couple of questions. Simple plan is to upgrade from 1 W2K3 Server , with Access Database to SQL Server.
We have a W2K3 Server now hosting IIS with an access database all in the same server (not good, but it was the only option at the time) now we need to make that important upgrade step.
My ideal plan was
1- One Server W2K3 Hosting SQL Server Express 2003.
2- Two Servers W2K3 Load Balancing IIS

I am unsure on the following.
1- Is that a good idea?
2- How do upsize from Access to SQL?
3- Is Express good enough for enteprises? Do I need to buy licenses or is it free?

We are a small company with at most 500 users using the Website, which basically manages inventory, shipping information, Transactions that kind of stuff.

Andre Furtado
andre@furtado.us

znwpta
Starting Member

12 Posts

Posted - 2008-07-09 : 09:51:52

Adding to the above.
Our database currently has over 1GB size and over 75 tables.
We do not use Access Stored Procedures.
we Use ASP Classic.

All queries are made in each ASP page. Access is only used for tables.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-07-09 : 10:26:31

I don’t think SQL Server Express is what you should use. It has a database size limit of 4 GB and your current DB is already 1 GB. Also, it does not include the SQL Server Agent job scheduling service, so it is extremely difficult to setup scheduled database backups. I would recommend going with SQL Server 2005 Standard Edition.

There is an Access Upsizing Wizard to create a SQL Server database from and Access database, but you can really only use that as a starting point. Access and SQL Server do not work the same, so you will probably have to make application changes to make it work well with SQL Server.

CODO ERGO SUM

znwpta
Starting Member

12 Posts

Posted - 2008-07-09 : 10:36:14

Mike,

Thanks for the quick reply. I agree, Standard does make more sense. I am thinking about the following for Hardware Requirements

SQL Server:
Hardware Raid 5 (200GB 15 K RPM x4)
CPU Intel QuadCore 2GZ x2
Memory 8GB

IIS Server x2 (For Load Balancing)
Hardware Raid 5 (100GB 15 K RPM x4)
CPU Intel QuadCore 2GZ x2
Memory 4GB

Also, Agreed since all our ASP pages have their own queries going on, there will be much code to be updated. We are using ADODB right now. I am thinking first upgrade to SQL and use a DSN connection to the SQL Server so that all queries still work. Then slowly upgrade to Stored Procedures.

What you think? Is there any advice you can give? I am hoping to run this system w/o touching our current system till the very last end.

Andre

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-07-09 : 11:09:05

If you get the SQL Server with 8 GB of memory, you will need to get Windows Enterprise Edition to use more than 4 GB of memory. I’m sure the dual quad core processors will be adequate for the application you described. Probably a single quad core processor would also be fine.

For a drive layout it is more typical to have a pair of mirrored drives (RAID1) for the OS, mirrored array (RAID1 or RAID10) for the SQL Server log files, and a RAID5 and RAID10 array for the SQL Server data files. You also need a location to put the SQL Server backup files and transaction log backups. It is better to put this on another array or on another server so that if you have a disk array failure on the data or log arrays, you still have the backups on disk. Your database is fairly small, so you can probably get by with the RAID5 array you described, but you should still have a different location for the backups.

IIS servers don’t usually need very much disk storage, so 300 GB may not really be necessary. Again, single quad core systems would probably be adequate for the IIS servers, especially with load balancing.

Without knowing a lot more about the application and database, it is hard to say how much work is required to do the conversion.

CODO ERGO SUM

znwpta
Starting Member

12 Posts

Posted - 2008-07-09 : 11:34:26

1- SQL Server:
a. Responsibilities
i. Database Server (SQL Standard)
b. Hardware Specifications
i. Disk Set-up
1. Raid 1 (OS)
2. Raid 1 (SQL Log Files)
3. Raid 5 (SQL Database)
ii. CPU Intel Quad Core 2GZ x2
iii. Memory 8GB
c. Software Specifications
i. Windows 2003 Server
ii. SQL 2003 Server Standard
d. Network Settings
i. TBD
2- IIS Server x2 (For Load Balancing)
a. Responsibilities
i. IIS (ASP Processing)
b. Hardware Specifications
i. Hardware Raid 5 (100GB 15 K RPM x4)
ii. CPU Intel Quad Core 2GZ x2
iii. Memory 4GB
c. Software Specifications
i. Windows 2003 Server
d. Network Settings
iii. TBD

would that be a correct description?

Do you know of any tips from going from Acess to SQL Server?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-07-09 : 11:53:32

You should use Windows Server 2003 Enterprise Edition for the DB server to be able to use more than 4 GB of memory. Windows Server 2003 Standard Edition supports only 4 GB of memory.

CODO ERGO SUM

znwpta
Starting Member

12 Posts

Posted - 2008-07-09 : 11:56:56

Thanks for all the help Mike. I believe now I have the info needed to buy the hardware/software necessary for this new project.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-09 : 22:02:01

quote:
Originally posted by Michael Valentine Jones

You should use Windows Server 2003 Enterprise Edition for the DB server to be able to use more than 4 GB of memory. Windows Server 2003 Standard Edition supports only 4 GB of memory.
CODO ERGO SUM

In 32-bit , it supports 4 GB of Ram whereas in 64-bit platform it supports 32 GB of Ram.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources