Transcript SQL Server 2005

IS4401
Project Technology Issues
Introduction
This seminar covers

Databases



When to use a Database
What Database to use
Development Tools

Visual Studio 2005
Databases
When to use a database
 What database to use

When to use a database
Reasons for using a database







Large amount of data
Complex data
Customer requires it
Data must be shared with other applications
Data integrity is an issue
Data are easily organised into tabular form
Saves you the bother of designing your own data
storage mechanism
When to use a database
Reasons not to use a database




Application not dependent on data storage
Very small amount of data
Unstructured or unusually structured data
Proof of concept with limited data storage
requirements

But see slide on hosted databases if it’s a website
Alternatives to databases



Text files
Structured (CSV) text files
Serialised object files


Can be implemented in Java, C# and VB.NET
You can define complex and unusual data structures (eg video)
I need a database:
Now what?
Database Management Systems
(DBMSs)


Provide environment to create, operate and manage
databases
Choices typically open to 4th years are:





Also consider:


Oracle 10g
SQL Server 2000
SQL Server 2005 Express (NEW this year)
MS Access
MySQL 5.0
Each DBMS has its own advantages and
disadvantages

You need to consider these before making your choice
Database Management
Systems (DBMSs)
How to choose a DBMS

Depends on



Project Type
Customer’s Environment
Proof of Concept projects

Main decision here is if you need a DBMS at all


Depends entirely on project
If you decide to use a DBMS, then Oracle 10g, SQL Server
2000 or SQL Server 2005 are the preferred choices




Available on BIS network
Industry standard
Plenty of documentation
Provide better integrity and robustness than MS Access
Database Management
Systems (DBMSs)
How to choose a DBMS

Full System Implementations


Application installed at customer site
Does the customer already have a DBMS?

YES:



Then use the customer’s DBMS if possible, especially if it’s Oracle or SQL
Server.
Use MS Access if the customer insists and if you are satisfied that MS
Access will fulfil the data integrity and performance requirements of
your project.
NO:




Customer is unlikely to want to spend money on a DBMS.
In this case, MySQL or SQL Server 2005 Express may be your best
options because they’re free.
MS Access may also be an option if the customer is willing to buy it and
they want a clear upgrade path to SQL Server.
Do NOT assume customer has MS Access if they have MS Office!
Check!
Database Management
Systems (DBMSs)
How to choose a DBMS

Full System Implementations


Web sites
Where is the website to be hosted?



It should not be on Corvus when the system goes live!
Hosting services provide database as well as web hosting
Find out what your intended hosting service provides



Likely to be Oracle, SQL Server or MySQL
Use the same DBMS for your development system
Note!


Need to identify hosting requirements early in project
Need to plan for migration from development system to hosted
system
DBMS Choices:
The Pros and Cons
Oracle 10g and SQL Server 2000

Pros:



Industry standard DBMSs with lots of advanced features
Either will deliver excellent performance and data integrity
Choice really depends on





Personal preference
Programming knowledge
Customer environment
Hosting environment
Cons:



Expensive
Time-consuming to install
Customer unlikely to buy one of these DBMSs for your
project if they don’t have one already
DBMS Choices:
The Pros and Cons
MS Access

Pros:






It’s almost everywhere – bundled with some versions of MS
Office
Reasonably easy to use
Familiar API
Well integrated with MS Office
Clear upgrade path to SQL Server
Cons:




Not suitable for large projects
Not suitable for web applications
Limits on database size and number of users
Prone to error if performance limits approached
DBMS Choices:
Access or SQL Server?
Microsoft issues with Access









2Gb maximum size
File sharing architecture will not handle very large
queries
Unsuitable for web applications
Large databases may become corrupt
Can’t recover/rollback from network problems
Theoretically max 255 users, in practice 20
Imposes heavy load on client computer
Does not scale well on multi-CPU platforms
Databases frequently badly designed
DBMS Choices:
Access or SQL Server?
Table taken from:
When to Migrate
from Microsoft
Access to Microsoft
SQL Server
SQL Server Technical
Article
Writers: Luke Chung and
Dan Haught
Published: February 2005
Access
SQL Server
Description
Database development
environment that
supports tables, queries,
forms, reports, and
programming logic.
Scalable, reliable, and
more secure client/server
database engine.
Maximum database size
2 gigabytes (GB)
1 terabyte
Maximum concurrent
users
Up to 20 concurrent
editors.
Unlimited
Up to 100 concurrent
reports being run.
Security
File access-based
security.
Enterprise-level security.
Performance
Limited by file share
model.
Limited only by hardware
and application design.
Reliability
Adequate for individuals
and small team usage.
High reliability. SQL
Server is a missioncritical database.
Recovery from network
failures cannot be rolled
back.
Backup and
administration tools
available.
DBMS Choices:
Pros and Cons
MySQL 5.0

Pros






Enterprise-level DBMS
Very fast
Free
Well documented
Easy to install
Cons



Not currently installed on BIS network
Installation of appropriate drivers and patches can be
confusing and must be carefully considered
Need to be careful with security setup
DBMS Choices:
MySQL 5.0
Why consider MySQL?







Free
Fast
Scaleable
Enterprise-grade
Runs on all platforms
Much more robust than MS Access
Very comprehensively documented
So what’s the catch?


Does things a little differently from Oracle and SQL
Server
But these differences are also well documented
DBMS Choices:
MySQL 5.0
What is MySQL?



Originally designed to work with medium
size databases (10-100 million rows, or
about 100MB per table) on small
computer systems.
Today MySQL Server handles terabytesize databases
The code can also be compiled in a
reduced version suitable for hand-held
and embedded devices.
DBMS Choices:
MySQL 5.0
Differences with other DBMSs





Lots of small differences
SQL implementation is slightly different
Philosophy is to standardise MySQL as
much as possible without sacrificing
performance
MySQL has extensions to SQL that
enhance performance
Differences not likely to matter in a 4th
year project context
DBMS Choices:
SQL Server 2005 Express



Bundled with Visual Studio 2005
Can also be downloaded separately
Advantages





Alternative to Access
Almost the same as SQL Server 2005
Scaleable
Clear upgrade path
Disadvantages

Size limited – this is not likely to be an issue
with smaller businesses
Free database downloads

The following DBMSes can be
downloaded FREE:

Oracle 10g Express Edition
http://www.oracle.com/technology/software/products/database/xe/index.html

MySQL 5.0 Community Edition
http://dev.mysql.com/downloads/mysql/5.0.html

SQL Server 2005 Express
http://msdn.microsoft.com/vstudio/express/sql/download/
Free database downloads

A final caveat on licenses



As a rule, these databases are free for noncommercial use
Commercial use may require purchase of a
license
Check the license terms carefully!
Development Tools
Visual Studio 2005
 NetBeans 5.5

Visual Studio 2005

Languages Supported
VB.NET
 C#
 J#


Technologies
.NET 2.0, ASP.NET 2.0, ADO.NET 2.0
 .NET for mobile devices

Features of Visual Studio
2005
Integrated Development Server
 Deployment Tools
 Partial Classes
 Designer Code

For Windows Forms
 For Web Pages


And lots more
Visual Studio 2005 Layout
Practical Intro to Visual
Studio 2005

Getting started
Configuring for use with C#
 Saving to the H: drive

Create and Build a Web Page
 Deploy Web page to an external
hosting service
