Best Practice-working with SQL Express Database

Hi,

I am new to working with Database using .net C#. i will really appreciate if someone can point me to a link where i can find best practices of working with sql express database with my c# web service applications.

preferably i would like to have answers for:

- to have one db or to have one for testing and one real db?

- db security

- use windows authentication or db user authentication

etc.

Best practices for SqlExpress will depend somewhat on your environment/requirements. Since you are developing a web service, I suggest asking the question in one of the asp.net forums (forums.asp.net).

SqlExpress has it's own forum here on the MSDN forums. I'll move the thread over there for more general info on Express best practices.sql

Benchmark for SQL Server 2005

How cheap (best free) database benchmark do you recommend me for SQL Server 2005(Express Edition)?I don't understand what you want, can you explain a bit more clearly or give an example of what you'd like?

-PatP|||I'd like measure time of response server for sql query. For more user.

Beginners

I have just started learning SQL server. Where from I start. I have downloaded SQL Management Studio & SQL Express Edition.

Now What should I do now ?

hi,

hard to say...

at http://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00df-4220-b133-29c1e0b6585f&DisplayLang=en you can find the downloadable version of Books OnLine, the on line help for SQL Server... it both contains "insides" about the engine and architecture, as SQL dialect tutorials as well.. it really is a valuable (the best) source of information about SQL Server..

but perhaps the understanding of what a DBMS is, is not included as these are "pre-requisites" to all the fun..

so here all depends on your actual skill and knowledge.. perhaps you need a beginners book about database, perhaps you require something more, like a real inside about what DBMS are and what are they fore, as long as the whole theory above them, or perhaps you just need to understand the differences about, say, Oracle and SQL Server as you already are familiar with the big picture..

regards

|||

HI,

Well, I have downloaded the book now. I have some knowledge about databases. as I used to work with MS Access already

Thanks & regards

Beginner to SQL server 2005 Express Edition

Hi,

I am fairly new to Visual Studio 2005 (using C#) and am writing an application for the company I work in. I have got a really nice functioning form working and am now planning what to do about the massive database I need to create (I don't think it will ever get to the 4GB limitation of SQL Server 2005 Express Edition).

Can someone please help with some simple questions please?

1: I am decoding a specific file format and extracting the information for the main database. Fortunately, each record in the data file I am decoding has unique identity codes (so this will be great for the ID of each entry). Because the software has to do this by itself (the encoded file contains more than 1,120,000 records which will shrink to about 30,000 records, all different sizes once decoded). Can each record in a SQL server database contain a different number of elements? If so, what is this known as (I have read a little about jagged arrays - is there a similar thing in SQL server databases)?

2: As the program will decode the file and assemble the database, can I make my program create the database from scratch and manipulate the database rather than me having to define a database for my program to fill? If so, where can I find some info on doing this?

3: The database file will be stored on our office network and has to be accessible to more than one person at a time (in this case, a maximum of 6 users - well within the 25 users my book says is possible with SSE2005). Is it more complex to develop multi-user access to the database?

Just in case you wonder what books I have got, they are "Introducing Microsoft SQL Server 2005 for Developers" and "Getting Started with SQL Server 2005 Express Edition".

Thank you!

1. Yes and No. You can store the information for the missing columns as NULL, but your code will have to handle that, and the columns will need to be nullable. If the file is in a standard format (eg a spreadsheet, or a csv file), you can use DTS (or SSIS in 2005) to load the file. That would be easier than writing a custom application to load the file.

2. You can build a dynamic sql statement based on the columns and column types of the tables you want to build. However, it would be easier and safer to define the database beforehand. Building tables is easy enough, just make sure you define the relationships in the correct order, and then load the data in that order aswell. You may want to leave defining relationships until after the data is loaded, just make sure the data is concurrent.

3. I am not sure, I don't know too much about this. The database schema itself would not have to be changed, you would just have to create SQL server user accounts for each user, or use windows integrated security which is much easier to set up. Then ensure they can connect to the server which the SQL server instance is based upon.

As this is a new product, you may learn more useful and up-to-date information from the books online. Microsoft are still offering a free e-learning course on 2005:

https://www.microsoftelearning.com/sqlserver2005/default.aspx

I have used parts of it and found it useful. Books tend to be overly wordy, whereas the e-learning courses get to the point quicker.

|||

Thank you,

Your reply will help me decide where to go next.

The file I am importing is not a straight-forward data file. It is a custom file format used by the British railway industry and some code is necessary to read in the data, join some elements to form a list, then the list will be placed into the database.

The database will have some fixed elements to start with, but will then be followed by my decoded list. The decoded list will contain a different number of entries. Some lists may contain around 10 entries. Some may contain as many as 200 entries.

Sean.

beginner question - instances VS. separate databases?

Hi, I hope this is the right place to ask this question.
I'm trying to get started programming databases against SQL Server Express. I'm pretty confident in understanding the techniques of how to access a database, a specific table, retrieve rows, etc. However, I’m having endless frustration in understanding the very rudimentary basics of how the SQL system even works. I know I don’t need this information to program against it on a simple level, but if I want to develop robust applications that I can distribute properly I need to know this, and it just seems so much more complicated than when I used MSDE.

One of the books I have describes how SQL Management Studio can log on to different server instances. Apparently, one can install SQL Server multiple times on the same system, and each one will create a new instance. And under that, each server instance can have multiple databases. But why would someone do that? Why have multiple instances? Why not just have one instance with multiple databases?

This issue leads into my confusion about the hierarchy of objects and the relevant security levels. For instance, it seems to me that I need a username and password to log onto the general server instance, and then another access permission to log onto the specific database. Isn’t this just a lot of unnecessary confusion, or am I misunderstanding something here?

Some clarification would me most appreciated.

One of the primary reasons to install SQL Server multiple times (instances) is to separate out security or other server-wide settings from each other. You can set one SA account on an instance to a particular Windows user, and the SA account on another instance to be a completely different account. You can also change server-wide settings to have different values on different instances.

As far as understanding the multiple features and architectures for SQL Server, you can check out this site. It will lead you through understanding the whole system from end to end.

http://www.informit.com/guides/guide.asp?g=sqlserver

Best of luck - Buck

|||I prefer to minimize number of instances and only break out over several instances when I have to. Reasons for doing that can be think like: Databases requires different collations for system databases Some database requires SQL Server logins, and other can do with Windows logins Other security aspects, making the databases sharing master database not suitable Memory and/or CPU allocation per instance Basic security isn't that difficult once you get used to terminology: Each person need a login. This can be a "SQL Server login" with a login name and password. Or a Windows login, where you grant a user or group in Windows to login. You also need grant a login "permissions" to use a database. You do this by creating a user name in the database for the login. Then you grant things like SELECT permissions to that user. The person doesn't really have to know what user name he has in the database (most likely the same name as the login name), the person will have to know the SQL Server login or Windows login. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ wrote in message news:82c3738e-a9e3-472d-9af9-a89f0acc60f7@.discussions.microsoft.com... Hi, I hope this is the right place to ask this question. I'm trying to get started programming databases against SQL Server Express. I'm pretty confident in understanding the techniques of how to access a database, a specific table, retrieve rows, etc. However, I’m having endless frustration in understanding the very rudimentary basics of how the SQL system even works. I know I don’t need this information to program against it on a simple level, but if I want to develop robust applications that I can distribute properly I need to know this, and it just seems so much more complicated than when I used MSDE. One of the books I have describes how SQL Management Studio can log on to different server instances. Apparently, one can install SQL Server multiple times on the same system, and each one will create a new instance. And under that, each server instance can have multiple databases. But why would someone do that? Why have multiple instances? Why not just have one instance with multiple databases? This issue leads into my confusion about the hierarchy of objects and the relevant security levels. For instance, it seems to me that I need a username and password to log onto the general server instance, and then another access permission to log onto the specific database. Isn’t this just a lot of unnecessary confusion, or am I misunderstanding something here? Some clarification would me most appreciated.|||

To be sure, instances are not the only way to control security. They merely provide a *server* level of separation. If you don't need that, don't use instances.

Production server instances are also used to control service pack levels. One application might dictate a certain service pack level; another might need a different one. Only a separate installation on a different system or an instance.

Buck

Beginner- Need Help With SQL Server 2005 Express

I am an absolute beginner with SQL Server, and I have now developed an app that needs to store, read, and search for saved data. Basically, my program will have to search through the databse to find a particular URL match to a string I supply, and then retrieve the Username and Password data that is associated with that URL, storing those to strings.

My problem is, I've learned how to create a database and tables in the IDE, but all of the examples on the web that I've found only talk about using SQL Server with Visual Basic 2005 in uses such as databinding to controls and displaying the database in datagrids and such on the form. I need help with how to interact with the database in code, seeing as the user of the program will likely never actually see the database contents- the data will be for the program's use only.

So, I was wondering if anyone could provide/guide me to some helpful information on interacting with SQL databases through code in VB Express 2005.

Thanks for any help.

In your VB code, define variables, and then instead of assigning data to control.text, assign data to the variables.

|||OK, but then how do I search through all of the fields in 1 column, and then if I find the right one, extract the adjacent columns for that field and save them as strings?

Begin and Rollback in SQL Server 2005 Express

Does SQL Server 2005 Express support rollbacks? For example:

BEGIN;

UPDATE Customer
SET LastName = 'Jones';

ROLLBACK;

The ROLLBACK will undo/reverse the changes made by the UPDATE befor a COMMIT is executed.

Yeah, SQL server 2005 express supports transactions, I have used them on some of my projects.

Maybe your code should begin with

BEGIN TRANSACTION

|||

Thanks. I'll give that a try. I'm moving from the PostgreSQL sql syntax to MS SQL Server sql syntax. In PostgreSQL, I can give the command:

BEGIN;
...
COMMIT;

to complete the transaction. I guess in MS SQL Server the syntax needs to include 'BEGIN TRANSACTION'. Thanks again for the help.

|||

A good practice is to use Try..CATCH statements with transactions. Check here

http://technet.microsoft.com/en-us/library/ms175976.aspx

|||

Thanks for the TRY CATCH suggestion!