Help in choosing SQL Server edition

My company has a website that connects to a sql server (on a different box). I am trying to convince them to get sql server 2005. However, I do not know if SQL Server 2005 Workgroup edition is okay for our needs. Can someone please tell me if it is.

Basically, our setup is the following:

The SQL Server will only have one/two clients - the web server

The SQL Server will have a number of different user accounts though

The SQL Server will have its databases replicated to another SQL Server - both will be 2005

The SQL Server will not have more than 4Gb of RAM

The SQL Server will only have one processor - possible Xeon, and/or Dual Core

The database will not have complicated analysis done on them. The most that will be done are complicated selects which indexing should be able to handle.

We use crystal reports.

Thank you for any help.

Jagdip.

One other big consideration to make in your analysis is the volume of traffic you're going to have to the web server. If your web server is incredibly active and is going to be hammering the SQL server with requests, that will definitely affect your decision.

This page illustrates the differences between the editions:

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Off the bat, it sounds like one feature that the Workgroup edition might lack that you need is Mirroring. Other than that, from what you described, I would think that the Workgroup edition would be fine for you. If you can find a way around the requirement for Mirroring (some other replication scheme--several exist), then you might be able to stick with the Workgroup rather than going with Standard.

Ryan

|||

Hi ya,

You have got a difficult choice. Well if you don't want to have that much and can replicate using your own logic then Sql Server express, the only limit for Database size which is a 4 GB. Workgroup would be better but you have got RAM limit of 3 GB although no limit on actual DB itself.

If you want to be cost effective then use SQL Express, if you can okay with small cost then workgroup

You can read the whole comparision about it from thislink

Hope that helps

Cheers

|||

Thank you both for your replies.

SQL Express is not an option as some of the databases are already over 4Gb.

The volume of traffic is not going to be great. If things go the way they are, we are looking at about 10 users per hour !!!

Thank you for the links as well. I had read these and was still unsure. Database Mirroring sounds good - but we are implementing a failover solution on the web server instead. If the primary web server/database fails over, then the secondaries will become active (using either Neverfail or Doubletake software). We are planning on using merge replication, so this should not be a problem.

The one thing I am worried about is the number of clients that can access the sql server simultaneously. Will the sql server restrict the number of users logged at once?

|||

I can't find anything anywhere about a limit on concurrent users for Workgroup Edition. If any of you SQL guys out there know of one, please chime in. It sounds like you're looking at VERY low usage though, so you should be fine. Were it not for your other requirements, SQL Express would be the perfect solution here. It sounds like you have a good plan in place and like you've got a good idea of which product is the right one for you. The nice thing about SQL is that it's easy to upgrade down the road as well, so if your requirements change in the future you should remain flexible.

|||

Hi ya,

If sql express is out of the question still workgroup would be more then enough as there is no hard core limit on concurrent no of users that can attach to a Sql Server. However it does depend on the actual machine performance which you already explained would be having 2-3 GB.

Hope that helps

Cheers

|||

Brilliant.

I just found out that we do not need to spend ￡300 for a server license of visual sourcesafe - so the money will hopefully go towards a new sql server.

|||Awesome. That worked out great. I'm glad you were able to find the right product for your needs.

help creating complicated database...

Hi,

for my website im going to be selling rims, and i need to have available a search by vehicle

SKU: 8438743
CAT: RIM
BRAND: ADVAN
MODEL: KREUZER_SERIES_V
DIAMETER: 17
WIDTH: 8
OFFSET: 45
LUGS: 5
LUG SPACING: 114.3
FINISH: GMETAL

that would be the determing factors of the results, heres what would define for example a

2002 Accord V6

LUGS = 5
LUG SPACING = 114.3
55 > OFFSET > 40
DIAMETER < 20
WIDTH < 9

so for the accord
the required attributes are a 5X114.3 lug pattern
the offset needs to be 40-55
max diameter is 19"
max width = 8"

My question is this, how many databases do i need to make? and what catagories do I use in each database? Also is it possible to do less than, greater than, etc, in databases? I think thats everything, thanks!I think you need two tables.

table Rim
RimID
Sku
Cat
Brand
Model
Diameter
width
Offset
lugs
lugspacing
finish

table CarRimRequirements
CarID
CarModel
Lugs
Lugspacing
MaxDiameter
MaxWidth
OffsetMin
OffsetMax

You can't really put a range in one column, use a minumum and maximum column like I did for the offset.

Hope that helps!
KJ|||This is just another suggestion.

tblRim

RimID
RimDescription
SKU
Brand
Model
Finish

tblAttribute

AttributeID
AttributeName

tblRimAttributes

RimAttributeID
RimID
AttributeID
Value

tblCar

CarID
CarModel

tblCarAttributes

CarAttributeID
CarID
AttributeID
Requirement – Allow this field to have null.

This is how it relates to your example.

tblRim

RimID RimDescription SKU Brand Model Finish
1Rim 8438743 ADVAN KREUZER GMETAL

tblAttribute

AttributeID AttributeName
1Diameter
2Width
3Offset
4MaxOffset
5MinOffset
6Lugs
7LugSpacing

tblRimAttributes

RimAttributeID RimID AttributeID Value
1 1 1 17
2 1 2 8
3 1 3 45
4 1 6 5
5 1 7 114.3

tblCar

CarID CarModel
1 2002 Accord V6

tblCarAttributes

CarAttributeID CarID AttributeID Requirment
1 1 1 19
2 1 2 8
3 1 4 55
4 1 5 40
5 1 6 5
6 1 7 114.3

If you are using Stored Procedures first you could get all the requirements for the selected car.

Then you could pass these values as parameters to another Stored Procedure, which would return all the Rims which meet these requirements.

Help : "error: 40 - Could not open a connection to SQL Server"

I'm using VS2005 and SQL2000. I created a database name "myDatabase" inSQL Server 2000 and built a website that connet to this database. WhenI'm running this website in VS2005 IDE (press F5 or Ctrl-F5),everything is OK. I publiced this site. When I connect to database onmy computer, everything is OK also, but when I connect to database onanother computer, an error occurred like this :
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005,
this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)"
Now, I don't know how to fix it. Pls help me as soon as possible.
Regards.
By default, SQL Server 2005 doesn't have TCP/IP connectivity enabled after installation. To enable TCP/IP connectivity, go to SQL Server Configuration Manager, select the Network Configuration Node, select TCP/IP and set it to Enabled. After enabling TCP/IP connectivity, you should be able to connect.|||

I'm getting the same error but when I try to access my SQL Database on my Web Hosts server. How do I enable TCP/IP on their server if I don't have access to it?

|||Is the username, password, database name, and address of the SQL server correct?

Help - Update Field in SQL Server

I am trying to write a password change page for my website. I want logged in users to be able to change their password which is stored in a SQL table. I was able to get the following code to work

Dim myCommand As New SqlCommand( _
"cmdChangePassword @.Email='"+request("email")+"', @.OldPassword='"+TextBox1.text+"',@.NewPassword='"+TextBox3.text+"'", myConnection)

This information is passed to a stored procedure which updates the table. However, I want to have a return value that shows that the password was changed. I changed the code as follows:

Dim myCommand As SqlCommand = New SqlCommand("cmdChangePassword", myConnection)
myCommand.CommandType = CommandType.StoredProcedure

myCommand.Parameters.Add("@.email", SqlDbType.VarChar, 50).Value = request("email")

Dim myParm1 As SqlParameter = myCommand.Parameters.Add("@.OldPassword", SqlDbType.VarChar, 20)
myParm1.Direction = ParameterDirection.Input
myParm1.Value = "+TextBox1.text+"

Dim myParm2 As SqlParameter = myCommand.Parameters.Add("@.NewPassword", SqlDbType.VarChar, 20)
myParm2.Direction = ParameterDirection.Input
myParm2.Value = "+TextBox3.text+"

With this new code the password is not being changed. However, I am not receiving any errors.

BDyou can check your db if the pwd is being changed, to see if the sp is working properly.

(1) in your SP, you can return an integer 0/1 for success or failure and appropriately throw a msgbox saying pswd has been changed.
and modify your code slightly and have an output parameter.

or
(2) query the db again with username=@.username and pwd=@.newpswd and see if you get any records. to make sure you get the xact record you can add more conditions. so if you do get a record, then the db has been updated with the new pswd.

apparently, this one requires an xtra trip to the db.

HTH|||The SP is set to return the integer. I haven't added that code yet. I have tested the SP and know that it works. However, the password is not being changed. The email address, old password and new password need to be passed to the SP. As long as the email address and old password match, the password will be changed. I am assuming there is something wrong with my code and that those items are not being passed successfully.|||heres an example of the code that i am using

Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim myParameter As SqlParameter
Dim myDataReader As SqlDataReader
myConnection = New SqlConnection("server=local;database=Northwind;Integrated Security=SSPI ")
myCommand = New SqlCommand()
myCommand.Connection = myConnection
myCommand.CommandText = "finalize_movein"
myCommand.CommandType = CommandType.StoredProcedure
'input parameter
myParameter = myCommand.CreateParameter()
myParameter.ParameterName = "@.userid"
myParameter.Direction = ParameterDirection.Input
myParameter.SqlDbType = SqlDbType.int
myParameter.Value = id
myCommand.Parameters.Add(myParameter)
'output parameter:
myParameter = myCommand.CreateParameter()
myParameter.ParameterName = "@.finalcnum"
myParameter.Direction = ParameterDirection.Output
myParameter.SqlDbType = SqlDbType.int
myCommand.Parameters.Add(myParameter)
' Open the connection to the SQL Server
myConnection.Open()
myCommand.ExecuteNonQuery()
return convert.toint32((myCommand.Parameters("@.finalcnum").Value))
myconnection.close

and have your SP defined like this :

CREATE PROCEDURE finalize_movein (@.userid int ,@.finalcnum bigint OUTPUT) as
begin...

HTH|||BD - please wrap your code in CODE tags to make it distinguishable from your comments.

It looks to me that your problem is in the way you are setting your parameter values. For some reason you have surrounded them with double quotes(") and plus signs(+). Remove those and you should have better luck. I changed the way you were setting up your @.email parameter to make it more consistent with the rest of your code, plus I added a ReturnValue parameter:

Dim myCommand As SqlCommand = New SqlCommand("cmdChangePassword", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
Dim myParm0 As SqlParameter = myCommand.Parameters.Add("@.ReturnValue", SqlDbType.Integer)
myParm0.Direction = ParameterDirection.ReturnValue
Dim myParm1 As SqlParameter = myCommand.Parameters.Add("@.email", SqlDbType.VarChar, 50)
myParm1.Direction = ParameterDirection.Input
myParm1.Value = request("email")
Dim myParm2 As SqlParameter = myCommand.Parameters.Add("@.OldPassword", SqlDbType.VarChar, 20)
myParm2.Direction = ParameterDirection.Input
myParm2.Value = TextBox1.text
Dim myParm3 As SqlParameter = myCommand.Parameters.Add("@.NewPassword", SqlDbType.VarChar, 20)
myParm3.Direction = ParameterDirection.Input
myParm3.Value = TextBox3.text

|||That worked! Thanks for your help!

Brian|||Sorry, I have one more question. I am now able to change the password. However, I want to display a message to the user that the password has been changed. I have the following variable defined.

Code

Dim Result As SqlParameter = myCommand.Parameters.Add("@.ReturnValue", SqlDbType.Int)

Result.Direction = ParameterDirection.ReturnValue

myCommand.Connection.Open()
myCommand.ExecuteNonQuery()

if Result.Value>0 then MSG.text= "Your password has been changed"

myCommand.Connection.Close()

End Code

In my html I have the following:

<asp:Label id="MSG" runat="server"></asp:Label
Any ideas what I am missing?

Brian|||Normally, the ReturnValue will be 0 if there are no errors. I don't know what your stored procedure looks like, but assuming it follows normal practice, this line:

if Result.Value>0 then MSG.text= "Your password has been changed"

should be this:

if myCommand.Parameters("@.ReturnValue").Value=0 then MSG.text= "Your password has been changed"

Terri|||That's perfect. Thanks again for everybody's help.

Brian

Help - Error: Unable to open the physical file

Hi, I've just finished creating my first ASP.NET website. I created it on my laptop and it works perfectly, but I'm having some problems deploying it to my web server (PC).
The site works ok, but when I try to log in to my client area, I get this error:

"Unable to open the physical file "c:\inetpub\wwwroot\ONeillStrata\App_Data\aspnetdb.mdf". Operating system error 5: "5(Access is denied.)".
An attempt to attach an auto-named database for file c:\inetpub\wwwroot\ONeillStrata\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."

I've searched far and wide for a solution and have read many articles, but none seem to be able to fix my problem. I've tried using that SEUtil tool, but that didn't work, I've made sure the App_Data directory on the web server isn't read only (the read-only checkbox isn't ticked, but it DOES have that little green square which I can't get rid of), I've assigned the ASPNET user as a db owner and that didn't work and I've manually attached the database to the web server's instance of SQL Express 2005.
When I launch the solution in VS2005 on the web server, I can browse through the database and see the tables and data etc, but when I try to run the application, I get that message above...

Surely it shouldn't be this hard to get it going?
Any help would be massively appreciated.

Hi,

It seems that the physical file location of the aspnetdb.mdf is wrong.

Please open your IIS, right click on your default web site,and click on the properties, change to the ASP.NET tab, edit configurations.Then you can see the LocalSqlServer string listed there, change the string into the following:data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true.

Thanks.