Advice needed: Best way to deploy/manage SQL db website

[Bobby Edward]

Here's the situation....

1. Local dev environment is VS2005 and VS2008 / using SQL Express databases
for data
2. Hosting company ONLY supports MS SQL Server 2005 databases

Here are the questions...

1. Should I develop using SQL Express, or would my life be easier if I had
my local dev machine connected to a local SQL Server db? Any advantages or
disadvantages?
2. After I'm done developing the website I want to deploy it in such a way
that if I have to change the database schema (add fields, tables, etc...) in
the future it's easy to do. Any recommendations?

I guess I'm not quite sure as to what is the "proper" way to make db changes
to a db already in production (changes to schema, fields, tables, etc...)?
I'm kind of lost.

What is the best way to do this?

 

[sloan]

If your hosting company ONLY does non-express databases, then I wouldn't
stick with Express.
Spend the $50 or so for developers editon of Sql Server 2005.

...

My advice. Code EVERYTHING in TSQL.

Create Table
Constraints
Baseline Data

Alter Table
Create Procedure

...

It makes a remote deployment much easier in my opinion.

The other things by going to "tsql only" mode is that you don't LOSE work
you do in the GUI.

Let's say you need to make a not null column...nullable.
Yeah, you can GUI go in and uncheck or check a box. But that work is "lost"
because if you ever need to repeat it....you need to re-GUI it.

Write a good ALTER TABLE script...and you have that (forever) to fall back
on.

That's my take. I have people at my own company who disagree with me..so
you know.........

But when it comes time to DEPLOY a real version of the database......I can
do it the fastest everytime.

..............

Here is another trick.

lets say you have some folders:

/Scripts/
/Scripts/usp/
/Scripts/udf/
/Scripts/ddl/

I put a .bat file in /Scripts/ to "merge" all my files.

/Scripts/usp/uspEmployeeGetByKey.sql
/Scripts/usp/uspEmployeeGetAll.sql
/Scripts/usp//uspEmployeeUpdate.sql
/Scripts/udf/udfEmployeeIsFullTime.sql

then in my .bat file...

/Scripts/tsql_merged.bat

and it contains

COPY ./usp/*.sql + ./udf/*.sql MERGED_TSQL.sql

that's a little trick....

The ddl is a little tricker, because it has to be in the correct order.

this "bat" tip....
that's a minor thing compared to the overall "use TSQL" tip above..but I
like it.

I keep every usp, every udf in a different file.
This helps maintenance and helps a group environment.

But even personal "just me" projects, I use the same format.

Good luck. Opinions might vary on this one.

 

[Peter Bromberg [C# MVP]]

There is NO DIFFERENCE between a SQLEXPRESS database and a SQL Server 2005
database. The only issue you may run into is the fact that SQLEXPRESS can use
the "User Instance" connection string which web hosting companies do not
support, as it is only supported for localhost operation. Download the Sql
Server "Database Publishing Wizard" (free from Microsoft) to create a SQL
script that allows you to migrate your local database to your hoster. Take
the time to "RTFM".
-- Peter
Site: http://www.eggheadcafe.com
UnBlog: htp://petesbloggerama.blogspot.com
Short Urls & more: http://ittyurl.net

 

[Bobby Edward]

Thanks. I will look at the DB Publishing Wizard. I just create mostly
small websites with few tables and I just wanted to know the best "standard"
way to design and deploy a db-enabled website (with the ability to easily
make future db changes).

 

[sloan]

Yeah, I should have at least mentioned that any code developed for Express
will work on non Express databases.
So that is definately an option ... to dev on Express.

It'd be really nice if hosters got more with it and offered Express. But
that doesn't help their $$$ I think.

I have a personal project that I deploy many times (for friends
mainly)...that I use with Access, just because of this very reason.
I get like 3 sql server db's with my hoster ( without paying extra
$$$ )...but I have more than 3 friends. (YES thats right .. I have FOUR
friends (ha ha)).

If you're doing lighter weight stuff...than the Database Publishing Wizard
would definately be a good choice.

I think my system .... is something I came up with before they had a thing
like Database Publishing Wizard...and I deal in multi dev environments.

....

 

[Bobby Edward]

Great responses Sloan. Thanks.

Is the membership/personalization stuff hard to do with Access? I know VS
uses SQL Express by default for this. What have you found to be the case
with you?

Thanks again!

 

[sloan]

Sample Access Providers
http://msdn2.microsoft.com/en-us/asp.net/aa336558.aspx

The good thing, since it was designed as an Abstract Class (Factory or
Provider Model)...somebody had probably already done it for you.

Just google "MembershipProvider" and YOUR_RDBMS

Although I wouldn't consider Access/(Jet database) a RDBMS...since its just
a file sitting on a drive somewhere.

But there you go.

There is a README with the Access about what it cannot do. Make sure you
look at that.

............

Let me give you one more piece of advice.

Use IDataReaders when talking to your Access/Jet database. Again...use
IDataReaders?
Or use Strong DataSets. The issue with Access and strong DataSets is that
you can't populate multiple tables in the database automatically.

While this is a little more work....I would either do one of the following:

1. Use IDataReaders to populate custom objects (per my blog)
2a. If yo ONLY need single tabled datasets, use the LoadDataSet method.
2b. If you need N (multiple) tables in your DataSets..... Use IDataReaders
and populate strong datasets. You'll have to do this manually...and is a
little more work.
You'll also have to hit the mdb N number of (seperate times) to do
this....but that's the crapper with Access....
(PS Always .Close() your datareaders......with "using" or in a finally
block....or you'll screw yourself as well)

However, the price you pay now will save you in the non-access future.



Why?

If you ever decide to NOT use Access, its alot easier to retool your
project.

Take a STRONG look at my blog, and the "1.1 Custom Objects" thing.


Try to think of Access in terms of using simple sql queries to get at the
data. Dont rely on Access specific syntax. That's is what will shoot you
in the foot later.

.............


how to manually populate a strong dataset with an IDataReader?

EmployeeDS ds = new EmployeeDS();

IDataReader sourceReader = null;

try
{

sourcReader = //something.ExecuteReader(); //""Select EmpID, LastName,
FirstName from Employee";

while ( sourceReader.Read() )
{

EmployeeDS.EmployeeRow row = ds.NewEmployeeRow();

row.EmployeeID = sourceReader.GetInt32(0);
row.LastName = sourceReader.GetString(1);
row.FirstName = sourceReader.GetString(2);

ds.Employee.AddNewEmployeeRow(row);




}
finally
{

if (null!=sourceReader)
{
sourceReader.Close();///ohhhh so VERY important
}
}

Console.WriteLine(ds.GetXml());


Again, thats a short sample..use LoadDataSet for 1 table in a strong
dataset.
Use the above method for N tables in a strong dataset.

It sounds stupid....BUT I've personally written a project that will support
both Access and Sql Server. ... and I like the method.


http://groups.google.com/groups/search?hl=en&q=EmployeeSqlServerData

http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/c5a90751a2a122be

http://groups.google.com/group/microsoft.public.dotnet.languages.csharp/msg/19c9b0924188effa

Read those for how I used the Factory design pattern to handle that.


Good luck.


PS

On on web server based mini project, I don't fear using Access. Don't get
me wrong either. I dont love it, and the day my provider supports sql
express, I'll abandon Access in a heartbeat.
If you do alot of reading, and you wisely "get it and get OUT as fast as you
can" which is a part of the IDataReader thinking...you can give it the ole'
college try in Access.

Obviously, you'll have to monitor if its good enough.

But plan now, and you'll be able to switch out in the future.

 

[Bobby Edward]

Thanks Sloan!!!

Sample Access Providers
http://msdn2.microsoft.com/en-us/asp.net/aa336558.aspx

The good thing, since it was designed as an Abstract Class (Factory or
Provider Model)...somebody had probably already done it for you.

Just google "MembershipProvider" and YOUR_RDBMS

Although I wouldn't consider Access/(Jet database) a RDBMS...since its
just a file sitting on a drive somewhere.

But there you go.

There is a README with the Access about what it cannot do. Make sure you
look at that.

...........

Let me give you one more piece of advice.

Use IDataReaders when talking to your Access/Jet database. Again...use
IDataReaders?
Or use Strong DataSets. The issue with Access and strong DataSets is that
you can't populate multiple tables in the database automatically.

While this is a little more work....I would either do one of the
following:

1. Use IDataReaders to populate custom objects (per my blog)
2a. If yo ONLY need single tabled datasets, use the LoadDataSet method.
2b. If you need N (multiple) tables in your DataSets..... Use
IDataReaders and populate strong datasets. You'll have to do this
manually...and is a little more work.
You'll also have to hit the mdb N number of (seperate times) to do
this....but that's the crapper with Access....
(PS Always .Close() your datareaders......with "using" or in a finally
block....or you'll screw yourself as well)

However, the price you pay now will save you in the non-access future.



Why?

If you ever decide to NOT use Access, its alot easier to retool your
project.

Take a STRONG look at my blog, and the "1.1 Custom Objects" thing.


Try to think of Access in terms of using simple sql queries to get at the
data. Dont rely on Access specific syntax. That's is what will shoot you
in the foot later.

............


how to manually populate a strong dataset with an IDataReader?

EmployeeDS ds = new EmployeeDS();

IDataReader sourceReader = null;

try
{

sourcReader = //something.ExecuteReader(); //""Select EmpID, LastName,
FirstName from Employee";

while ( sourceReader.Read() )
{

EmployeeDS.EmployeeRow row = ds.NewEmployeeRow();

row.EmployeeID = sourceReader.GetInt32(0);
row.LastName = sourceReader.GetString(1);
row.FirstName = sourceReader.GetString(2);

ds.Employee.AddNewEmployeeRow(row);




}
finally
{

if (null!=sourceReader)
{
sourceReader.Close();///ohhhh so VERY important
}
}

Console.WriteLine(ds.GetXml());


Again, thats a short sample..use LoadDataSet for 1 table in a strong
dataset.
Use the above method for N tables in a strong dataset.

It sounds stupid....BUT I've personally written a project that will
support both Access and Sql Server. ... and I like the method.


http://groups.google.com/groups/search?hl=en&q=EmployeeSqlServerData

http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/c5a90751a2a122be

http://groups.google.com/group/microsoft.public.dotnet.languages.csharp/msg/19c9b0924188effa

Read those for how I used the Factory design pattern to handle that.


Good luck.


PS

On on web server based mini project, I don't fear using Access. Don't get
me wrong either. I dont love it, and the day my provider supports sql
express, I'll abandon Access in a heartbeat.
If you do alot of reading, and you wisely "get it and get OUT as fast as
you can" which is a part of the IDataReader thinking...you can give it the
ole' college try in Access.

Obviously, you'll have to monitor if its good enough.

But plan now, and you'll be able to switch out in the future.