Having Problem with SQL Express 2005

I just started working with sql server 2005 and i have a windows-based application in C# with an attached sql server database file.Everything is ok but i don't know if i can create a password for that database.
there is no IDE for sql server 2005 like Enterprise Manager in SQL Server 2000 to do such things(Creating roles,passwords,...).In my computer I've installed VS 2005 Professional and SQL Express.I think i should do all thease thing with system stored procdures right? But it's really boring do all those things without an IDE.

Thanks in advance.

If you have a look at the following link you will find a download for SQL Server Management Studio Express, this is an IDE based of the full management studio (Replacement for Enterprise manager and Query tools) that can be used for the express editions.

http://msdn.microsoft.com/vstudio/express/sql/download/

|||Thank you

Having problem with my server

I just currently installed Microsoft studio express which includes SQL server and i get this error Server Error in '/WebSite1' Application. it also states "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding" I am wondering do i have to have IIS on to run SQL server 2005 express adition.

I am also having problem with SQL Express installation. After installing it, and configuring with SQL surface area for also remote access via tcp or pipe I still get the message:

>sqlcmd

HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

|||Make sure you have followed all of the steps at http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx in order to enable remote connectivity.

Having Different Versions of Crystal Reports (8,9) in the same web application

See, We have a web application which is developed using Webclass Designer and currently runs on IIS 4.0 and IIS 5.0. It also contains a Reports Menu thorugh which the end user sees the data. These reports are mainly displayed using Crystal Reports 8.0.0.371. There are around 110 reports. The client wants specifically only couple of these reports to be migrated to version 9.0 leaving the rest. The reports are mainly opened, passed with SQL query etc in a single class file. So this is common for all the reports. Just that the object of this class file is created in every instance. The reference of CRAXDRT.dll of version 8 is added as a reference in this class file so that objects can be created.



Now my question is , is it possible to have two different versions of Crystal Reports in the same application. It means that ideally the support files are different. Also , it will be not possible for the report’s class file present in VB to refer both 8 and 9 at the same time. This is what I feel but as usual the client needs some support docs if this is true. Hence I need ur expert opinion on this.


Any help in this issue ...Please share ur expert opinionsHi,
I wish to do the same, did you manage to solve this problem, or does any1 else have any input?

Have anyone come across this issue?

Faulting application mmc.exe, version 5.2.3790.0, faulting module stardds.dll, version 2000.80.760.0, fault address 0x00013767.
Have anyone come across this issue??
LystraDid this happen on your server or a client?|||On the Server!

Lystra

have a problem with pericision

I have been working on an application which require as much percision as we can get. We have defined all numeric values as (38,16) however when ever we divide a number by the result of a built in function it truncate the percision to 5 decimal place this cause us to have a small but enoying loss in the numbers being caculated we need a minimuim of 8 decimal places or better is there a work around or solution for this problem in sql server 2005. The more percision the better.

example: select tna/(select sum(tna) from taum) from taum where fund_id = 2345

the result is always 5 decimal places even though fund_id tna = 2569698.23 and sum(tna) =98745612325879.36 which should equal .00000002602341683313994 instead the result = .00000

Hi Caveman1,

I had try your issue using t_sample table:

CREATE TABLE t_sample ( a DECIMAL(38,16) NOT NULL, b DECIMAL(38,16) NOT NULL );

And I have received your same results to: SELECT a / b AS result FROM t_sample

I have changed precision and scale to my datatypes in t_sample table.

ALTER TABLE t_sample ALTER COLUMN a DECIMAL(18,10) NOT NULL, b DECIMAL(18,4) NOT NULL;

Great news: result = .00000002602341683313994324

Good coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||the problem appears to only happen when a you use a function like sum(b) or avg(b) as the denominator....|||

Nope!

Books online:

The operand expressions are denoted as expression e1, with precision p1 and scale s1, and expression e2, with precision p2 and scale s2. The precision and scale for any expression that is not decimal is the precision and scale defined for the data type of the expression.

Operation Result precision Result scale *

e1 + e2

max(s1, s2) + max(p1-s1, p2-s2) + 1

max(s1, s2)

e1 - e2

max(s1, s2) + max(p1-s1, p2-s2) + 1

max(s1, s2)

e1 * e2

p1 + p2 + 1

s1 + s2

e1 / e2

p1 - s1 + s2 + max(6, s1 + p2 + 1)

max(6, s1 + p2 + 1)

e1 { UNION | EXCEPT | INTERSECT } e2

max(s1, s2) + max(p1-s1, p2-s2)

max(s1, s2)

* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||

I don't understand the table above.

here is and example I hope will clearify the issue

if I have a table that is defined as such

create table sec ( secid as int,nasset as numeric(38,16))

If I write a query that no tryies to calcalulate the percentage of a security bassed on total holdings the result is truncated to five decimal places.

select nasset/(select sum(nasset) from sec) from sec where secid = 1 -- truncate the result to five decimal places

I need aleast 8 places of percision in the result what do I need to do.....?

I found a work around that suggest that if you use a variable instead of the subquery that it would solve the problem but that did not work.

example:

declare @.totolA as numeric(38,16)

select @.totalA = sum(nasset) from sec

select nasset/@.totalA from sec where secid = 1 --still truncates to 5 decimal places

|||

I have the same question. How SQL decide the number of decimal in division operator. the formulation in the online book does not make sense. I got 6 decimal when I used (28,18) /(28,18) but over 20 decimal when I used (28,18)/(18,4).

looks like there is no problem for addtion, subtraction, and multiplication operators.

can anyone help ? thx.

sql

have a problem with pericision

I have been working on an application which require as much percision as we can get. We have defined all numeric values as (38,16) however when ever we divide a number by the result of a built in function it truncate the percision to 5 decimal place this cause us to have a small but enoying loss in the numbers being caculated we need a minimuim of 8 decimal places or better is there a work around or solution for this problem in sql server 2005. The more percision the better.

example: select tna/(select sum(tna) from taum) from taum where fund_id = 2345

the result is always 5 decimal places even though fund_id tna = 2569698.23 and sum(tna) =98745612325879.36 which should equal .00000002602341683313994 instead the result = .00000

Hi Caveman1,

I had try your issue using t_sample table:

CREATE TABLE t_sample ( a DECIMAL(38,16) NOT NULL, b DECIMAL(38,16) NOT NULL );

And I have received your same results to: SELECT a / b AS result FROM t_sample

I have changed precision and scale to my datatypes in t_sample table.

ALTER TABLE t_sample ALTER COLUMN a DECIMAL(18,10) NOT NULL, b DECIMAL(18,4) NOT NULL;

Great news: result = .00000002602341683313994324

Good coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||the problem appears to only happen when a you use a function like sum(b) or avg(b) as the denominator....|||

Nope!

Books online:

The operand expressions are denoted as expression e1, with precision p1 and scale s1, and expression e2, with precision p2 and scale s2. The precision and scale for any expression that is not decimal is the precision and scale defined for the data type of the expression.

Operation

Result precision

Result scale *

e1 + e2

max(s1, s2) + max(p1-s1, p2-s2) + 1

max(s1, s2)

e1 - e2

max(s1, s2) + max(p1-s1, p2-s2) + 1

max(s1, s2)

e1 * e2

p1 + p2 + 1

s1 + s2

e1 / e2

p1 - s1 + s2 + max(6, s1 + p2 + 1)

max(6, s1 + p2 + 1)

e1 { UNION | EXCEPT | INTERSECT } e2

max(s1, s2) + max(p1-s1, p2-s2)

max(s1, s2)

* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||

I don't understand the table above.

here is and example I hope will clearify the issue

if I have a table that is defined as such

create table sec ( secid as int,nasset as numeric(38,16))

If I write a query that no tryies to calcalulate the percentage of a security bassed on total holdings the result is truncated to five decimal places.

select nasset/(select sum(nasset) from sec) from sec where secid = 1 -- truncate the result to five decimal places

I need aleast 8 places of percision in the result what do I need to do.....?

I found a work around that suggest that if you use a variable instead of the subquery that it would solve the problem but that did not work.

example:

declare @.totolA as numeric(38,16)

select @.totalA = sum(nasset) from sec

select nasset/@.totalA from sec where secid = 1 --still truncates to 5 decimal places

Has anyone noticed a size difference between 2.0 and 3.0?

I had an application that I had written in C# under the .NET Compact Framework that imported CSV files and wrote them to a newly created SDF. I usually ran the application under Emulation. The resulting SDF was usually about 1.6 Megabytes.

Now, under VS 2005 and SQL Mobile (AKA 3.0), the same application, compiled under the .NET Compact Framework 2.0, using the same CSV files generates an SDF of over 13 Megabytes.

Obviously, this was a bit unexpected and is having a rather negative impact on the HP iPaq rx1950 that the database was supposed to be running on.

The database is encrypted during the create call on both .NET CF's and SQL CE versions. I was finally able to run compact via the SQL CE tool under emulation connecting to the SDF on the desktop (running compact on the device or under emulation resulted in both running out of memory); however, it caused no change whatsoever in file size.

Has anyone seen something similar? Am I missing a new call or, since my code has not changed between versions, could I possibly be making a deprecated call?

Thanks,
Brian

Brian,

I ran this same test - generating a SQL CE/SQL mobile database using a simple CSV loader I wrote and I noticed that if the database contains an NTEXT field, the SQL Mobile database ended up much larger on device (by a factor of 2-3x) than the corresponding SQL CE database.

I sent the sample code to recreate this to the SQL Mobile team and will followup with them on this issue and post the results here.

If I removed the NTEXT fields from my tables (used nvarchar instead), the resulting SQL Mobile db was equitable in size.

-Darren

|||

Darren,

Thanks! That did it. I converted the one ntext column to an nvarchar and we're back to our original size. Since I had more than 4000 rows, I am guessing that SQL CE 3.0 was reserving a ton of additional space, just in case.

Regards,
Brian

|||

Hi,

SQL Mobile 3.0 reserves a data page for Long Value data when data length is more than 256 bytes. NTEXT and IMAGE are long value data types. So, if you have a table with NTEXT/IMAGE column, then SQL Mobile 3.0 creates a data page for each row where the data size is more than 256 bytes. And data page size is typically 4K . If you want to update the NTEXT/IMAGE column, the operation will be very fast and it is by design. Also, whenever there is a data length exceeding 4K (not really 4K but 4K minus some control data size), another data page is allocated. Even if your data value is just 4.1K, 8K is what reserved by SQL Mobile 3.0. Best practice here would be to align your data sizes on 4K boundary.

Note: Page size may not be 4k always. It varies!

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

Has anyone noticed a size difference between 2.0 and 3.0?

I had an application that I had written in C# under the .NET Compact Framework that imported CSV files and wrote them to a newly created SDF. I usually ran the application under Emulation. The resulting SDF was usually about 1.6 Megabytes.

Now, under VS 2005 and SQL Mobile (AKA 3.0), the same application, compiled under the .NET Compact Framework 2.0, using the same CSV files generates an SDF of over 13 Megabytes.

Obviously, this was a bit unexpected and is having a rather negative impact on the HP iPaq rx1950 that the database was supposed to be running on.

The database is encrypted during the create call on both .NET CF's and SQL CE versions. I was finally able to run compact via the SQL CE tool under emulation connecting to the SDF on the desktop (running compact on the device or under emulation resulted in both running out of memory); however, it caused no change whatsoever in file size.

Has anyone seen something similar? Am I missing a new call or, since my code has not changed between versions, could I possibly be making a deprecated call?

Thanks,
Brian

Brian,

I ran this same test - generating a SQL CE/SQL mobile database using a simple CSV loader I wrote and I noticed that if the database contains an NTEXT field, the SQL Mobile database ended up much larger on device (by a factor of 2-3x) than the corresponding SQL CE database.

I sent the sample code to recreate this to the SQL Mobile team and will followup with them on this issue and post the results here.

If I removed the NTEXT fields from my tables (used nvarchar instead), the resulting SQL Mobile db was equitable in size.

-Darren

|||

Darren,

Thanks! That did it. I converted the one ntext column to an nvarchar and we're back to our original size. Since I had more than 4000 rows, I am guessing that SQL CE 3.0 was reserving a ton of additional space, just in case.

Regards,
Brian

|||

Hi,

SQL Mobile 3.0 reserves a data page for Long Value data when data length is more than 256 bytes. NTEXT and IMAGE are long value data types. So, if you have a table with NTEXT/IMAGE column, then SQL Mobile 3.0 creates a data page for each row where the data size is more than 256 bytes. And data page size is typically 4K . If you want to update the NTEXT/IMAGE column, the operation will be very fast and it is by design. Also, whenever there is a data length exceeding 4K (not really 4K but 4K minus some control data size), another data page is allocated. Even if your data value is just 4.1K, 8K is what reserved by SQL Mobile 3.0. Best practice here would be to align your data sizes on 4K boundary.

Note: Page size may not be 4k always. It varies!

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

Hardware Suggestions please

We are planning to purchase a new server for our database application.
The server will be running Sql Server 2005 as the RDBMS. The
application is expected to be used by around 10-15 people (power
users). We are expecting some heavy importing(inserting) of data into
the database and also lots of reading. There would be many tables that
would be very huge (more than a million rows).
We have looked at the System requirements for Sql Server 2005 but that
is the lowest common denominator. We have therefore decided on the
following hardware configuration:
Intel Pentium IV
4 GB RAM
500 GB HARDDISK ( may be as two disks)
Any additions/corrections to the above list would be appreciated.
ThanksThere is an article by Kevin Kline called "Bare Metal Tuning" that
gives some great advice on what hardware to use for SQL Server; here's
a link to it, but you need to be a subscriber to SQL Server magazine
(which you should purchase anyway; great resource).
http://www.sqlmag.com/Article/Artic...rver_46492.html
Just off the top of my head, though, you need to really think about
drives and arrangement. A database server needs at a minimum two
physical disk cofigured; one for database files, and one for log files.
I recommend three (add another for tempdb). RAID 10 is the best
setup, but you can use RAID 5.
Others may have more advice, but that's what I could think off in the
short term.
Stu|||You didn't say how many CPU's. Since you're expecting a lot of querying,
going with just one isn't an option. Consider 2 dual-core CPU's. Also,
even though you may have enough storage capacity in your disks, performance
may not be up to the task. The more physical disks you have, the faster
things go - if you go with a RAID10 (striped and mirrored) configuration.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1146824123.095677.27230@.i39g2000cwa.googlegroups.com...
We are planning to purchase a new server for our database application.
The server will be running Sql Server 2005 as the RDBMS. The
application is expected to be used by around 10-15 people (power
users). We are expecting some heavy importing(inserting) of data into
the database and also lots of reading. There would be many tables that
would be very huge (more than a million rows).
We have looked at the System requirements for Sql Server 2005 but that
is the lowest common denominator. We have therefore decided on the
following hardware configuration:
Intel Pentium IV
4 GB RAM
500 GB HARDDISK ( may be as two disks)
Any additions/corrections to the above list would be appreciated.
Thanks

Hardware specification query

Hi
I am specifying some hardware for an application server. It will be running
two SQL applications - both applications will have about 20 users.
Throughput will be relatively low with regards to transactions. Both
software vendors would prefer their own server, but I'd rather keep both on
the same box. Both software vendors state specifically that they need a
seperate array for the transaction logs.
I'm thinking of the following:
Dual Xeon (dual core)
7GB RAM (1 GB for OS, 3GB for App1, 3 GB for App2)
RAID1(2 x 74GB15k SCSI) for OS, RAID5(3x146GB 15k SCSI) for App1 database,
RAID5(3x146GB 15k SCSI) for App2 database, RAID1(2x36GB15k SCSI) for App1
transaction logs, RAID1(2x36GB15k SCSI) for App2 transaction logs.
A couple of questions:
Could both transaction logs sit on the same RAID array without any real
decrease in performance?
Would I be better going RAID 10 for the database files as opposed to RAID 5?
Thanks in advance
Robbie
Robbie,
From your proposal, it looks like you are blessed with adequate resources.
In fact, for two applications, each with about 20 users, you have proposed
an incredibly 'robust' server.
I'd keep the Logs on separate RAID1 arrays for the optimal performance. If
the db size and storage requirements allow, the databases might be faster
and more 'robust' on the same RAID10 array (instead of separate RAID5
arrays).
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Robbie Niblock" <robbie@.nospam.com> wrote in message
news:OM95gc49GHA.924@.TK2MSFTNGP03.phx.gbl...
> Hi
> I am specifying some hardware for an application server. It will be
> running two SQL applications - both applications will have about 20 users.
> Throughput will be relatively low with regards to transactions. Both
> software vendors would prefer their own server, but I'd rather keep both
> on the same box. Both software vendors state specifically that they need a
> seperate array for the transaction logs.
> I'm thinking of the following:
> Dual Xeon (dual core)
> 7GB RAM (1 GB for OS, 3GB for App1, 3 GB for App2)
> RAID1(2 x 74GB15k SCSI) for OS, RAID5(3x146GB 15k SCSI) for App1 database,
> RAID5(3x146GB 15k SCSI) for App2 database, RAID1(2x36GB15k SCSI) for App1
> transaction logs, RAID1(2x36GB15k SCSI) for App2 transaction logs.
> A couple of questions:
> Could both transaction logs sit on the same RAID array without any real
> decrease in performance?
> Would I be better going RAID 10 for the database files as opposed to RAID
> 5?
> Thanks in advance
> Robbie
>

Hardware specification query

Hi
I am specifying some hardware for an application server. It will be running
two SQL applications - both applications will have about 20 users.
Throughput will be relatively low with regards to transactions. Both
software vendors would prefer their own server, but I'd rather keep both on
the same box. Both software vendors state specifically that they need a
seperate array for the transaction logs.
I'm thinking of the following:
Dual Xeon (dual core)
7GB RAM (1 GB for OS, 3GB for App1, 3 GB for App2)
RAID1(2 x 74GB15k SCSI) for OS, RAID5(3x146GB 15k SCSI) for App1 database,
RAID5(3x146GB 15k SCSI) for App2 database, RAID1(2x36GB15k SCSI) for App1
transaction logs, RAID1(2x36GB15k SCSI) for App2 transaction logs.
A couple of questions:
Could both transaction logs sit on the same RAID array without any real
decrease in performance?
Would I be better going RAID 10 for the database files as opposed to RAID 5?
Thanks in advance
RobbieRobbie,
From your proposal, it looks like you are blessed with adequate resources.
In fact, for two applications, each with about 20 users, you have proposed
an incredibly 'robust' server.
I'd keep the Logs on separate RAID1 arrays for the optimal performance. If
the db size and storage requirements allow, the databases might be faster
and more 'robust' on the same RAID10 array (instead of separate RAID5
arrays).
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Robbie Niblock" <robbie@.nospam.com> wrote in message
news:OM95gc49GHA.924@.TK2MSFTNGP03.phx.gbl...
> Hi
> I am specifying some hardware for an application server. It will be
> running two SQL applications - both applications will have about 20 users.
> Throughput will be relatively low with regards to transactions. Both
> software vendors would prefer their own server, but I'd rather keep both
> on the same box. Both software vendors state specifically that they need a
> seperate array for the transaction logs.
> I'm thinking of the following:
> Dual Xeon (dual core)
> 7GB RAM (1 GB for OS, 3GB for App1, 3 GB for App2)
> RAID1(2 x 74GB15k SCSI) for OS, RAID5(3x146GB 15k SCSI) for App1 database,
> RAID5(3x146GB 15k SCSI) for App2 database, RAID1(2x36GB15k SCSI) for App1
> transaction logs, RAID1(2x36GB15k SCSI) for App2 transaction logs.
> A couple of questions:
> Could both transaction logs sit on the same RAID array without any real
> decrease in performance?
> Would I be better going RAID 10 for the database files as opposed to RAID
> 5?
> Thanks in advance
> Robbie
>

Hardware specification query

Hi
I am specifying some hardware for an application server. It will be running
two SQL applications - both applications will have about 20 users.
Throughput will be relatively low with regards to transactions. Both
software vendors would prefer their own server, but I'd rather keep both on
the same box. Both software vendors state specifically that they need a
seperate array for the transaction logs.
I'm thinking of the following:
Dual Xeon (dual core)
7GB RAM (1 GB for OS, 3GB for App1, 3 GB for App2)
RAID1(2 x 74GB15k SCSI) for OS, RAID5(3x146GB 15k SCSI) for App1 database,
RAID5(3x146GB 15k SCSI) for App2 database, RAID1(2x36GB15k SCSI) for App1
transaction logs, RAID1(2x36GB15k SCSI) for App2 transaction logs.
A couple of questions:
Could both transaction logs sit on the same RAID array without any real
decrease in performance?
Would I be better going RAID 10 for the database files as opposed to RAID 5?
Thanks in advance
RobbieRobbie,
From your proposal, it looks like you are blessed with adequate resources.
In fact, for two applications, each with about 20 users, you have proposed
an incredibly 'robust' server.
I'd keep the Logs on separate RAID1 arrays for the optimal performance. If
the db size and storage requirements allow, the databases might be faster
and more 'robust' on the same RAID10 array (instead of separate RAID5
arrays).
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Robbie Niblock" <robbie@.nospam.com> wrote in message
news:OM95gc49GHA.924@.TK2MSFTNGP03.phx.gbl...
> Hi
> I am specifying some hardware for an application server. It will be
> running two SQL applications - both applications will have about 20 users.
> Throughput will be relatively low with regards to transactions. Both
> software vendors would prefer their own server, but I'd rather keep both
> on the same box. Both software vendors state specifically that they need a
> seperate array for the transaction logs.
> I'm thinking of the following:
> Dual Xeon (dual core)
> 7GB RAM (1 GB for OS, 3GB for App1, 3 GB for App2)
> RAID1(2 x 74GB15k SCSI) for OS, RAID5(3x146GB 15k SCSI) for App1 database,
> RAID5(3x146GB 15k SCSI) for App2 database, RAID1(2x36GB15k SCSI) for App1
> transaction logs, RAID1(2x36GB15k SCSI) for App2 transaction logs.
> A couple of questions:
> Could both transaction logs sit on the same RAID array without any real
> decrease in performance?
> Would I be better going RAID 10 for the database files as opposed to RAID
> 5?
> Thanks in advance
> Robbie
>

Hardware Setup

I am new to SQL and over the next year we will be implementing a SQL server
for an imaging application and a CRM package and I already have a few other
small packages using MSDE that could take advantage of a SQL server. Is it
best to have one large SQL
server for your network or have many different boxes. I realize that by onl
y having one box you put all of you eggs in one basket although if you have
several you need several different copies of the software and lots of hardwa
re which drives the cost up
. We are a growing 100 person company and we need to prepare for the future
. Is it not advisable to say build a quad processor box with lots of RAM an
d Disk and purchase the Processor version of either Standard or Enterprise a
nd let several apps take ad
vantage of the SQL server? Is it dependent on the apps whether or not you c
ould do this? Any pointers or information would be greatly appreciated....
ThanksMike,
There are pro's and con's to everything and this is no exception but in
general I feel that you will be better off going with one server vs.
several. This will simplify maintenance, lower costs and will be easier to
expand later on. This is especially true if none of the apps that will run
on the box require a huge amount of resources all the time. This way the
different apps can share the larger resource pool (memory, cpu's etc) more
efficiently and take advantage of their individual peaks without sacrificing
performance.
Andrew J. Kelly SQL MVP
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:371D39BD-DCF1-47B0-9E08-292128DA8EFE@.microsoft.com...
> I am new to SQL and over the next year we will be implementing a SQL
server for an imaging application and a CRM package and I already have a few
other small packages using MSDE that could take advantage of a SQL server.
Is it best to have one large SQL server for your network or have many
different boxes. I realize that by only having one box you put all of you
eggs in one basket although if you have several you need several different
copies of the software and lots of hardware which drives the cost up. We
are a growing 100 person company and we need to prepare for the future. Is
it not advisable to say build a quad processor box with lots of RAM and Disk
and purchase the Processor version of either Standard or Enterprise and let
several apps take advantage of the SQL server? Is it dependent on the apps
whether or not you could do this? Any pointers or information would be
greatly appreciated....
> Thanks

Hardware Setup

I am new to SQL and over the next year we will be implementing a SQL server for an imaging application and a CRM package and I already have a few other small packages using MSDE that could take advantage of a SQL server. Is it best to have one large SQL
server for your network or have many different boxes. I realize that by only having one box you put all of you eggs in one basket although if you have several you need several different copies of the software and lots of hardware which drives the cost up
. We are a growing 100 person company and we need to prepare for the future. Is it not advisable to say build a quad processor box with lots of RAM and Disk and purchase the Processor version of either Standard or Enterprise and let several apps take ad
vantage of the SQL server? Is it dependent on the apps whether or not you could do this? Any pointers or information would be greatly appreciated....
Thanks
Mike,
There are pro's and con's to everything and this is no exception but in
general I feel that you will be better off going with one server vs.
several. This will simplify maintenance, lower costs and will be easier to
expand later on. This is especially true if none of the apps that will run
on the box require a huge amount of resources all the time. This way the
different apps can share the larger resource pool (memory, cpu's etc) more
efficiently and take advantage of their individual peaks without sacrificing
performance.
Andrew J. Kelly SQL MVP
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:371D39BD-DCF1-47B0-9E08-292128DA8EFE@.microsoft.com...
> I am new to SQL and over the next year we will be implementing a SQL
server for an imaging application and a CRM package and I already have a few
other small packages using MSDE that could take advantage of a SQL server.
Is it best to have one large SQL server for your network or have many
different boxes. I realize that by only having one box you put all of you
eggs in one basket although if you have several you need several different
copies of the software and lots of hardware which drives the cost up. We
are a growing 100 person company and we need to prepare for the future. Is
it not advisable to say build a quad processor box with lots of RAM and Disk
and purchase the Processor version of either Standard or Enterprise and let
several apps take advantage of the SQL server? Is it dependent on the apps
whether or not you could do this? Any pointers or information would be
greatly appreciated....
> Thanks

Hardware Setup

I am new to SQL and over the next year we will be implementing a SQL server for an imaging application and a CRM package and I already have a few other small packages using MSDE that could take advantage of a SQL server. Is it best to have one large SQL server for your network or have many different boxes. I realize that by only having one box you put all of you eggs in one basket although if you have several you need several different copies of the software and lots of hardware which drives the cost up. We are a growing 100 person company and we need to prepare for the future. Is it not advisable to say build a quad processor box with lots of RAM and Disk and purchase the Processor version of either Standard or Enterprise and let several apps take advantage of the SQL server? Is it dependent on the apps whether or not you could do this? Any pointers or information would be greatly appreciated...
ThanksMike,
There are pro's and con's to everything and this is no exception but in
general I feel that you will be better off going with one server vs.
several. This will simplify maintenance, lower costs and will be easier to
expand later on. This is especially true if none of the apps that will run
on the box require a huge amount of resources all the time. This way the
different apps can share the larger resource pool (memory, cpu's etc) more
efficiently and take advantage of their individual peaks without sacrificing
performance.
--
Andrew J. Kelly SQL MVP
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:371D39BD-DCF1-47B0-9E08-292128DA8EFE@.microsoft.com...
> I am new to SQL and over the next year we will be implementing a SQL
server for an imaging application and a CRM package and I already have a few
other small packages using MSDE that could take advantage of a SQL server.
Is it best to have one large SQL server for your network or have many
different boxes. I realize that by only having one box you put all of you
eggs in one basket although if you have several you need several different
copies of the software and lots of hardware which drives the cost up. We
are a growing 100 person company and we need to prepare for the future. Is
it not advisable to say build a quad processor box with lots of RAM and Disk
and purchase the Processor version of either Standard or Enterprise and let
several apps take advantage of the SQL server? Is it dependent on the apps
whether or not you could do this? Any pointers or information would be
greatly appreciated....
> Thanks

Hardware question

I'm building a database server that's going to be running MS SQL 2000 server
and .net application. Should I go with SATA RAID or is U320 SCSI RAID
recommended? The latter is much more expensive, but is the performance
worth it?

Also, should I go with dual Xeon or should I go with AMD's Opteron? I read
a review from anandtech that basically tore the Xeon up. What are your
thoughts?

http://www.anandtech.com/IT/showdoc.aspx?i=1935&p=9"Shabam" <blislecp@.hotmail.com> wrote in message
news:bq6dnZkI_NzWiGbdRVn-ug@.adelphia.com...
> I'm building a database server that's going to be running MS SQL 2000
server
> and .net application. Should I go with SATA RAID or is U320 SCSI RAID
> recommended? The latter is much more expensive, but is the performance
> worth it?

It really depends on what you're doing.

> Also, should I go with dual Xeon or should I go with AMD's Opteron? I
read
> a review from anandtech that basically tore the Xeon up. What are your
> thoughts?

Again.. it depends.

I've run SQL databases on a Single CPU 500Mhz machine and it was adequate.

I have a database on Quad Xeon 700Mhz 2MB Cache machine with U160 RAIDs and
that's barely fast enough for 42 million lookups a day and 17 million writes
a day.

So... again.. what will you be doing.

(and there are books that will give you numbers on various things like I/Os
per disk, etc that let you calculate what you need.)

> http://www.anandtech.com/IT/showdoc.aspx?i=1935&p=9|||The whole SCSI vs. SATA debate comes down to nothing more than the
external interfaces for the drives. You need to look beyond that and
compare the drives themselves and then take a look at what you want to
do with them.

In simple terms SATA drives are not yet designed for 24x7 use with the
same MTBF as SCSI drives, if you take a close look at the numbers you'll
find that the external interface, SCSI or SATA, isn't the deciding
factor in the drives performance. SCSI drives are just built better in
very simple terms and do perform better, remember that performance isn't
all about rpm. If you need the extra performance and reliability then go
for SCSI, if money is tight then go for SATA.

HTH,
g.

http://www.sqlskunkworks.com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hardware Question

This is a business rather than a technical question.
We have a legacy application that uses a jBase database (www.jbase.com) and
now want to install a SQL Server application on the same hardware. Both
applications need to serve web pages. The box is a dual pentium with 2GB of
memory.
Is that full of risk or are we most likely to be OK. Money is tight and
getting a new server won't be easy, but of course we don't want the whole
thing to crash either.
Many thanksHi Kim
Without knowing more about your system it is hard to answer. Using your
database server for anything other than SQL Server will be a compromise. The
level of compromise will be dependent on the current load and resources on
the server and on the network.
http://www.sql-server-performance.c...re_planning.asp gives some
information on what you should be aiming for.
Before installing SQL Server check out the current performance of the
server, see if there is more than sufficient memory, as SQL Server will grab
as much as it can! Setting the maximum memory for SQL Server will probably b
e
required (which may require purhasing more). Check disc activity, if you can
put the SQL Server log and datafiles on their own spindles then that will be
better, but if all your discs are currently being heavily used then it may b
e
worth seeking an alternative.
If you do install SQL Server, re-benchmark to see if there has been a
significant drop in performance and if that drop is acceptable.
Ideally you would try this on a similar server and not on a production
environment first.
John
"Kim Barnes" wrote:

> This is a business rather than a technical question.
> We have a legacy application that uses a jBase database (www.jbase.com) an
d
> now want to install a SQL Server application on the same hardware. Both
> applications need to serve web pages. The box is a dual pentium with 2GB o
f
> memory.
> Is that full of risk or are we most likely to be OK. Money is tight and
> getting a new server won't be easy, but of course we don't want the whole
> thing to crash either.
> Many thanks|||Many thanks John, that's a great response.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Kim
> Without knowing more about your system it is hard to answer. Using your
> database server for anything other than SQL Server will be a compromise. T
he
> level of compromise will be dependent on the current load and resources on
> the server and on the network.
> http://www.sql-server-performance.c...re_planning.asp gives some
> information on what you should be aiming for.
> Before installing SQL Server check out the current performance of the
> server, see if there is more than sufficient memory, as SQL Server will gr
ab
> as much as it can! Setting the maximum memory for SQL Server will probably
be
> required (which may require purhasing more). Check disc activity, if you c
an
> put the SQL Server log and datafiles on their own spindles then that will
be
> better, but if all your discs are currently being heavily used then it may
be
> worth seeking an alternative.
> If you do install SQL Server, re-benchmark to see if there has been a
> significant drop in performance and if that drop is acceptable.
> Ideally you would try this on a similar server and not on a production
> environment first.
> John
> "Kim Barnes" wrote:
>

Hardware Question

This is a business rather than a technical question.
We have a legacy application that uses a jBase database (www.jbase.com) and
now want to install a SQL Server application on the same hardware. Both
applications need to serve web pages. The box is a dual pentium with 2GB of
memory.
Is that full of risk or are we most likely to be OK. Money is tight and
getting a new server won't be easy, but of course we don't want the whole
thing to crash either.
Many thanks
Hi Kim
Without knowing more about your system it is hard to answer. Using your
database server for anything other than SQL Server will be a compromise. The
level of compromise will be dependent on the current load and resources on
the server and on the network.
http://www.sql-server-performance.co...e_planning.asp gives some
information on what you should be aiming for.
Before installing SQL Server check out the current performance of the
server, see if there is more than sufficient memory, as SQL Server will grab
as much as it can! Setting the maximum memory for SQL Server will probably be
required (which may require purhasing more). Check disc activity, if you can
put the SQL Server log and datafiles on their own spindles then that will be
better, but if all your discs are currently being heavily used then it may be
worth seeking an alternative.
If you do install SQL Server, re-benchmark to see if there has been a
significant drop in performance and if that drop is acceptable.
Ideally you would try this on a similar server and not on a production
environment first.
John
"Kim Barnes" wrote:

> This is a business rather than a technical question.
> We have a legacy application that uses a jBase database (www.jbase.com) and
> now want to install a SQL Server application on the same hardware. Both
> applications need to serve web pages. The box is a dual pentium with 2GB of
> memory.
> Is that full of risk or are we most likely to be OK. Money is tight and
> getting a new server won't be easy, but of course we don't want the whole
> thing to crash either.
> Many thanks
|||Many thanks John, that's a great response.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Kim
> Without knowing more about your system it is hard to answer. Using your
> database server for anything other than SQL Server will be a compromise. The
> level of compromise will be dependent on the current load and resources on
> the server and on the network.
> http://www.sql-server-performance.co...e_planning.asp gives some
> information on what you should be aiming for.
> Before installing SQL Server check out the current performance of the
> server, see if there is more than sufficient memory, as SQL Server will grab
> as much as it can! Setting the maximum memory for SQL Server will probably be
> required (which may require purhasing more). Check disc activity, if you can
> put the SQL Server log and datafiles on their own spindles then that will be
> better, but if all your discs are currently being heavily used then it may be
> worth seeking an alternative.
> If you do install SQL Server, re-benchmark to see if there has been a
> significant drop in performance and if that drop is acceptable.
> Ideally you would try this on a similar server and not on a production
> environment first.
> John
> "Kim Barnes" wrote:

Hardware Question

This is a business rather than a technical question.
We have a legacy application that uses a jBase database (www.jbase.com) and
now want to install a SQL Server application on the same hardware. Both
applications need to serve web pages. The box is a dual pentium with 2GB of
memory.
Is that full of risk or are we most likely to be OK. Money is tight and
getting a new server won't be easy, but of course we don't want the whole
thing to crash either.
Many thanksHi Kim
Without knowing more about your system it is hard to answer. Using your
database server for anything other than SQL Server will be a compromise. The
level of compromise will be dependent on the current load and resources on
the server and on the network.
http://www.sql-server-performance.com/rc_hardware_planning.asp gives some
information on what you should be aiming for.
Before installing SQL Server check out the current performance of the
server, see if there is more than sufficient memory, as SQL Server will grab
as much as it can! Setting the maximum memory for SQL Server will probably be
required (which may require purhasing more). Check disc activity, if you can
put the SQL Server log and datafiles on their own spindles then that will be
better, but if all your discs are currently being heavily used then it may be
worth seeking an alternative.
If you do install SQL Server, re-benchmark to see if there has been a
significant drop in performance and if that drop is acceptable.
Ideally you would try this on a similar server and not on a production
environment first.
John
"Kim Barnes" wrote:
> This is a business rather than a technical question.
> We have a legacy application that uses a jBase database (www.jbase.com) and
> now want to install a SQL Server application on the same hardware. Both
> applications need to serve web pages. The box is a dual pentium with 2GB of
> memory.
> Is that full of risk or are we most likely to be OK. Money is tight and
> getting a new server won't be easy, but of course we don't want the whole
> thing to crash either.
> Many thanks|||Many thanks John, that's a great response.
"John Bell" wrote:
> Hi Kim
> Without knowing more about your system it is hard to answer. Using your
> database server for anything other than SQL Server will be a compromise. The
> level of compromise will be dependent on the current load and resources on
> the server and on the network.
> http://www.sql-server-performance.com/rc_hardware_planning.asp gives some
> information on what you should be aiming for.
> Before installing SQL Server check out the current performance of the
> server, see if there is more than sufficient memory, as SQL Server will grab
> as much as it can! Setting the maximum memory for SQL Server will probably be
> required (which may require purhasing more). Check disc activity, if you can
> put the SQL Server log and datafiles on their own spindles then that will be
> better, but if all your discs are currently being heavily used then it may be
> worth seeking an alternative.
> If you do install SQL Server, re-benchmark to see if there has been a
> significant drop in performance and if that drop is acceptable.
> Ideally you would try this on a similar server and not on a production
> environment first.
> John
> "Kim Barnes" wrote:
> > This is a business rather than a technical question.
> >
> > We have a legacy application that uses a jBase database (www.jbase.com) and
> > now want to install a SQL Server application on the same hardware. Both
> > applications need to serve web pages. The box is a dual pentium with 2GB of
> > memory.
> >
> > Is that full of risk or are we most likely to be OK. Money is tight and
> > getting a new server won't be easy, but of course we don't want the whole
> > thing to crash either.
> >
> > Many thanks

Hardware design suggestion

I'm implementing a brand new project that consists in a soft-realtime application that executes scheduled operations (tcp commands) stored on a SqlServer 2005 express db. DB and app are always on the same workstation. Let's say that the workload is about 40000 events per day, with periods of activity and more idle periods. The DB is filled by external applications that provides insert/update using ad-hoc sprocs and providing only the 'preedceding event'. The major performance problem I see is given by the fact that each time an event is inserted the sproc must rebuild all the scheduled times (I've build an optimization that uses service broker as to update asynchronously the scheduled times). The application does not requires any special calculation and is very lightweight... it only fetches the db and sends tcp commands. The only 'hard work' is to guarantee the exact time for sending the commands.

Now I have to design the required hardware and I expect some tip from you on how to proceed to build a test workload and how to translate it into 'minimum hardware specification'. Consider that safety is not importand since system are redundant in other ways.
My questions are

- Should I put OS, tempdb, database and logs each on a separate disk array ? My idea is to have a two-disk RAID0 for OS and logfiles, and a n-disk RAID0 for tempdb and appdb

- How to extimate the number of disks to have in the RAID ?

- Should I also consider processor ? I have the idea that processor is not very important while I should pay attention to bus and memory bottlenecks: so I've thought to use DDR2 RAM with a FSB of 1066 MHz and a single dual or quad core cpu, so discarding XEON multiprocessor systems that requires FBDIMM, that AFAIK are slower that DDR2.

- any other suggestion ?

The process you are describing sounds like it could be very intensive. You may want to have others take a very close look at your design to make specific feedback on ways you could make this more efficient.

Regardless, it is very difficult with the information provided to give you a hardware recommendation. One thing I can recommend is you avoid the use of RAID 0 for this. With RAID 0, a single disk failure will cause you to lose the array.

Good luck,
Bryan Smith

|||

I would not use RAID 0 for anything for a project like this. If you lose any disks in a RAID 0 array, you are down.

I would put OS and page file on RAID 1, TempDB on a separate RAID 1, AppDB on separate RAID 10 or 1, and Log files on a separate RAID 10 or 1.

The Intel Tulsa MP 3.4 GHz Xeon (with 16MB of L3 cache) performs very well on SQL Server 2005. If you don't need four CPU's, I would look at the newer, Core2 Duo based CPU's, like the Xeon 5160. Make sure you have enough RAM to ensure you don't have memory pressure (which will also reduce I/O pressure). I would suggest 4GB minimum.

|||I see that both of you have suggested to avoid RAID0, and I want to clarify a point: in our system performance is more important than security on a single machine since the system is fully redundant in the sense that we have more machine that runs in parallel, each one with the same scheduling and performs the same task, plus an 'arbiter' that selects the active machine. So If I loose a disk I have another machine that is fully mirrored to the first one... a sort of RAID1 between machines.

Anyway I'm building a test environment and I expect some sort of failsafe RAID on production machines.
|||

If you have that kind of money to spend, go with RAID 10 (1+0). Even with what you have stated above, I would not recommend RAID 0.

B.