By Susan Sales Harkins and Drew Wutka
An Access database often outgrows its original purpose. When that happens, you face applying band-aid technology or upgrading to a more powerful database system, such as SQL Server Express or even SQL Server. But before you toss Access out the window and start signing purchase orders for consultants, developers, licensing, and new hardware, consider one more option—turning your Access application into a Web-based application. Let's look at some reasons why this might make sense.
Shameless disclaimer: If you truly need a more powerful database system and can afford its trappings, spend and grow!
Client versus server
A server-side database, such as MySQL, SQL Server, and Oracle, evaluates requests on the server side (sent in the form of a SQL statement) and then returns data to the client. Jet, on the other hand, lets the client do all the work. Jet is the database engine behind Access. Even if the database (.mdb) is on a network server, the client still does all the work. The server simply responds to client file requests.
This arrangement retrieves more then just the data across the network. As a result, indexes and unused data clog the network and slow things down. An alternative is to place the Access database on your Web server's local drive and then build the interface on the Web server. Doing so creates an ad hoc server-side database that handles transactions on the server (using your code). Requests from the client are in Hyper Text Transfer Protocol (HTTP) format instead of SQL.
Recommendation: Put the Access database (the .mdb file) in a folder that isn't shared. That way, users won't have direct access to the database. Their only access will be via the Web server. Your code will serve as the layer that allows users to interact with the actual data.
No client installation
A Web-based front end minimizes installation issues. Users need only a browser. The database doesn't care whether the user is sending requests via a Windows PC, a Mac, or a machine running Linux.
Easy cross-platform usage
You're free to use your language of choice to create the Web interface and the code that the server users to interact with the database. Users get clean and standard HTML that almost all browsers can use.
Recommendation: Keep the Web interface simple to ensure that everyone can use it. If you need the advantages of client-side tools, such as client side scripting, Flash media, and so on, go for it. Just keep in mind that not every HTML feature works in every browser. The back end can be as complex as necessary because the Web server is the only one using it.
Simplified security
Storing the database in a non-shared folder (see #1) restricts access. Only the Web server's administrator has access to the database file. That leaves security to the Web server. Now, you might argue either way as to whether this method is more or less secure than a server-side database. However, someone with direct access to a machine with a server-side database could probably also gain direct access to that database.
In addition, a server-side database requires a network connection. An Access database on a Web server isn't directly available. You can access the Web server, but not the database. Only the Web server can access the database on the server's local drive. On the other hand, Access has a security system known as Access User Level security (this isn't available with Access 2007). Most server-side database security systems are more secure than Access User Level security.
Recommendation: Even though you impose an almost absolute-type form of security by placing a database on a Web server, it can't hurt to apply Access User Level security. The database is still an .mdb that you can copy and open on any machine that has Access installed. As a developer, you will probably have local copies of that .mdb (and copies on backup tapes for your Web server). Be on the safe side and keep honest people honest by putting a little extra security in place. Users via the Web interface won't even know the additional security is there.
Easy use of NT authentication
Using Visual Basic for Applications (VBA), you can determine the NT name of users logged into an Access database and thereby restrict which users can do what. However, this method isn't foolproof, and it doesn't truly authenticate users. Your Web interface (on an IIS Web server) can use Integrated Windows Security to authenticate user credentials to individual Web pages
Goodbye to corruption!
Most developers complain that Access is susceptible to corruption. Used incorrectly, it certainly is. With an Uninterruptible Power Source (UPS) and redundant drives, your Web-based database (.mdb file) won't suffer from corruption
No version problems
With the quick pace of upgrades, many of us have users spread across two and three versions of Access. Unfortunately, not all versions play well together. A Web interface eliminates version incompatibility issues because the Web server uses Jet. That means the Web server doesn't even need Access—it doesn't load Access. Your Web server doesn't care what version of Access the client uses.
Live, behind-the-scenes interface updates
To update an Access front end, you must copy or modify an .mdb file. Access won't let you make changes while people are using it. (Beginning with Access 2000, you can make some changes, but a few still require exclusive access to the database.) In contrast, you can change the Web interface files (.asp, .aspx, and so on) whenever you like. The changes are almost immediate.
Portability
Every Windows OS since Windows 98 has had personal Web server capabilities. That means you can develop and test a Web site using a laptop running Windows 98 (or later). Using an Access database as the data source has a few benefits:
• There's no need to install and run a heavy-duty server-side database on your laptop.
• There's no need to maintain a network connection to a live server.
• You can copy the live system and its database as just a bunch of files. You don't have to import, export, or attach database files. For example, you can build a Web site on your laptop or desktop and then move it to a Web server. To work on an update, simply copy the Access database file (.mdb) from the Web server to your laptop.
Recommendation: Jet allows many transaction type SQL statements. You can build and modify tables and views using SQL, along with the typical data reading and altering capabilities. Sometimes, if you put a system on a remote server where you no longer have the ability to get to the actual .mdb, it's pretty simple to whip up an .asp page that lets you run SQL on the fly against the database.
More users
By their very nature, Web interfaces are unbound. In other words, once a page is loaded, the interface is no longer connected to the database. But a bound Access front end maintains a connection to the source, and Jet limits you to 255 concurrent connections. Your Web application, unless you have 255 users hitting the database at the exact same moment (which would require approximately 30,000 users a minute at a transactions speed of .5 seconds) can have more concurrent users.
Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is Mastering Microsoft SQL Server 2005 Express, with Mike Gunderloy, published by Sybex. Other collaborations with Gunderloy are Automating Microsoft Access 2003 with VBA, Upgrader’s Guide to Microsoft Office System 2003, ICDL Exam Cram 2, and Absolute Beginner's Guide to Microsoft Access 2003, all published by Que. Currently, Susan volunteers as the Publications Director for Database Advisors. You can reach her at ssharkins@gmail.com.
Drew Wutka is a Microsoft Access/Visual Basic/Web developer for Marlow Industries, Inc. He also does independent contract development and has developed many free projects, such as the Microsoft Access MiniCalendar, the Dynamic FrontPage Navigation ASP Sitemap, and the Password Enabled Enigma Encryption VB program. You can reach Drew at Drew@wolfwares.com.
Wednesday, July 16, 2008
10+ tips for getting the best performance out of your SQL Server data types
By Susan Sales Harkins
Data integrity and performance are the driving force behind almost every decision you make during the design and development process. Defining appropriate data types is one of the easiest ways to let SQL Server help you help yourself.
Size matters
Always use the smallest data size that will accommodate the largest possible value. If a column is going to store values between 1 and 5, use tinyint instead of int. This rule also applies to character columns. The smaller the data size, the less there is to read, so performance, over all, benefits. In addition, smaller size reduces network traffic. With newer technology, this tip seems less relevant, but don't dismiss it out of hand. You'll won't regret being efficient from the get-go.
Bad primary keys
Don't use float, real, or datetime for primary keys. They add overhead that you just don't need, and given the nature of primary keys, you will probably feel the pinch.
Usurp SQL Server assumptions
When converting a value to a variable length data type using varchar, always specify the length. Otherwise, SQL Server assumes a default size of 30. Specify the smallest size possible (see #1).
Faster sorts
To speed up frequent sorts, use an int (or an integer-based) data type if possible. SQL Server sorts integer data faster than character data.
Efficient strings
The text data type accommodates a lot of data but at a cost. Unfortunately, I have seen developers use it by default. For those large columns, use varchar instead; it accommodates up to 8,000 characters and requires less overhead. Consequently, varchar performs better.
The varchar instead of char trade off
It's best to limit a text column, but knowing just how much can be difficult. If the data varies in length, it can be more efficient to use varchar than char. A fixed-length data type will waste space on smaller entries. In addition, sorts against a varchar column are usually faster. That's because SQL Server sorts the entire width of a char column.
Don't store NULL in fixed-length columns
Try not to allow NULL values in a fixed-length column. NULL consumes the same space an input value would. Those NULL values will add up quickly in a large column. If you must accommodate NULL values, use a variable-length column. They use less space for NULL.
Avoid bigint
SQL Server's bigint uses 8 bytes of memory. In comparison, int uses just 4. Don't use bigint unless the data forces you to.
Avoid sql_variant
Avoid using SQL Server's sql_variant data type. It's a memory hog and comes with limits that make it difficult to work with:
• Variants can't be part of a primary or foreign key.
• Variants can't be part of a computed column.
• Variants don't work with LIKE in a WHERE clause.
• OLE DB and ODBC providers automatically convert variants to nvarchar(4000) a huge waste almost 100% of the time!
When numbers are really text
It's common to store numeric values as text. For instance, you won't (mathematically) evaluate a ZIP Code or a phone number, so you might store them as text. However, numeric data types generally consume less overhead to store the same value as a character data type. You'll probably notice a difference between the two data types in a WHERE clause, a sort, or a join.
Data integrity and performance are the driving force behind almost every decision you make during the design and development process. Defining appropriate data types is one of the easiest ways to let SQL Server help you help yourself.
Size matters
Always use the smallest data size that will accommodate the largest possible value. If a column is going to store values between 1 and 5, use tinyint instead of int. This rule also applies to character columns. The smaller the data size, the less there is to read, so performance, over all, benefits. In addition, smaller size reduces network traffic. With newer technology, this tip seems less relevant, but don't dismiss it out of hand. You'll won't regret being efficient from the get-go.
Bad primary keys
Don't use float, real, or datetime for primary keys. They add overhead that you just don't need, and given the nature of primary keys, you will probably feel the pinch.
Usurp SQL Server assumptions
When converting a value to a variable length data type using varchar, always specify the length. Otherwise, SQL Server assumes a default size of 30. Specify the smallest size possible (see #1).
Faster sorts
To speed up frequent sorts, use an int (or an integer-based) data type if possible. SQL Server sorts integer data faster than character data.
Efficient strings
The text data type accommodates a lot of data but at a cost. Unfortunately, I have seen developers use it by default. For those large columns, use varchar instead; it accommodates up to 8,000 characters and requires less overhead. Consequently, varchar performs better.
The varchar instead of char trade off
It's best to limit a text column, but knowing just how much can be difficult. If the data varies in length, it can be more efficient to use varchar than char. A fixed-length data type will waste space on smaller entries. In addition, sorts against a varchar column are usually faster. That's because SQL Server sorts the entire width of a char column.
Don't store NULL in fixed-length columns
Try not to allow NULL values in a fixed-length column. NULL consumes the same space an input value would. Those NULL values will add up quickly in a large column. If you must accommodate NULL values, use a variable-length column. They use less space for NULL.
Avoid bigint
SQL Server's bigint uses 8 bytes of memory. In comparison, int uses just 4. Don't use bigint unless the data forces you to.
Avoid sql_variant
Avoid using SQL Server's sql_variant data type. It's a memory hog and comes with limits that make it difficult to work with:
• Variants can't be part of a primary or foreign key.
• Variants can't be part of a computed column.
• Variants don't work with LIKE in a WHERE clause.
• OLE DB and ODBC providers automatically convert variants to nvarchar(4000) a huge waste almost 100% of the time!
When numbers are really text
It's common to store numeric values as text. For instance, you won't (mathematically) evaluate a ZIP Code or a phone number, so you might store them as text. However, numeric data types generally consume less overhead to store the same value as a character data type. You'll probably notice a difference between the two data types in a WHERE clause, a sort, or a join.
Protecting Document with Password
You can protect your document by applying password so that unauthorized person can not display as well as modify your document. You can apply two types of passwords:
Password to open the document:
If it is applied then you have to give the correct password to open the document, otherwise you cannot open the document.
Password to modify the document:
If it is applied then you have to give the correct password to modify the document, otherwise your document is opened but you cannot modify the document. It means that your document becomes read-only.
To apply a password to document, follow these steps.
* Open Save As dialog box by selecting "Save As" command from File menu.
* Click "Tools" button of Save As dialog box and choose "General Options" from drop down menu, "Save" dialog box appears as shown in figure below.
* Enter first password in "Password to open" text box and second password in "Password to modify" text box (if required) and click "Ok" button of dialog box. Microsoft Word will open "Confirm Password" dialog box for the confirmation of passwords. The maximum length of password is 15 characters.
* Re-enter the password to open and password to modify and click "Ok" button of Confirm Password dialog boxes one by one.
* Click "Save" button of Save As dialog box.
Password to open the document:
If it is applied then you have to give the correct password to open the document, otherwise you cannot open the document.
Password to modify the document:
If it is applied then you have to give the correct password to modify the document, otherwise your document is opened but you cannot modify the document. It means that your document becomes read-only.
To apply a password to document, follow these steps.
* Open Save As dialog box by selecting "Save As" command from File menu.
* Click "Tools" button of Save As dialog box and choose "General Options" from drop down menu, "Save" dialog box appears as shown in figure below.
* Enter first password in "Password to open" text box and second password in "Password to modify" text box (if required) and click "Ok" button of dialog box. Microsoft Word will open "Confirm Password" dialog box for the confirmation of passwords. The maximum length of password is 15 characters.
* Re-enter the password to open and password to modify and click "Ok" button of Confirm Password dialog boxes one by one.
* Click "Save" button of Save As dialog box.
Converting from Rollback to Undo
From : http://www.shutdownabort.com
This procedure will drop the existing rollback segments and tablespace, and replace them with an auto-managed undo tablespace.
# 1. Identify the rollback segments and tablespaces to be removed:
select segment_name
, tablespace_name
, status
from dba_rollback_segs
where tablespace_name != 'SYSTEM'
/
#
# 2. Produce offline and drop statements for each rollback segment:
select 'alter rollback segment ' || segment_name || ' offline;'
from dba_rollback_segs
where tablespace_name = '&TS'
and tablespace_name != 'SYSTEM'
/
select 'drop rollback segment ' || segment_name || ';'
from dba_rollback_segs
where tablespace_name = '&TS'
and tablespace_name != 'SYSTEM'
/
Review the output and, if you are happy with it, cut and paste it back into sqlplus.
# 3. Make a note of the old tablespace's size and location, then drop it.
drop tablespace including contents and datafiles
/
# 4. Create a new 'undo' tablespace in place of the old one.
create undo tablespace undo
datafile '' size
extent management local
/
# 5. Update the initialisation parameters
If you are using a spfile (and you should be!) run the following commands:
alter system reset rollback_segments scope=spfile sid='*'
/
alter system set undo_management=auto scope=spfile
/
alter system set undo_tablespace=undo scope=spfile
/
If you are still using a pfile (init.ora) then do the following:
Remove the following settings:
rollback_segments=...
Add/alter these two lines:
undo_management=auto
undo_tablespace=undo
# 6. Restart the instance
Note the location of the alert log. If there is a problem during start-up, you will probably receive nothing more than a message saying 'instance aborted'. The alert log will contain a slightly more detailed explanation.
Restart the instance:
shutdown immediate
startup
Rerun the query from step 1. There will be a whole load of rollback segments with names like '_SYSSMU1$'. This is an indictaion that the instance is now using auto undo.
This procedure will drop the existing rollback segments and tablespace, and replace them with an auto-managed undo tablespace.
# 1. Identify the rollback segments and tablespaces to be removed:
select segment_name
, tablespace_name
, status
from dba_rollback_segs
where tablespace_name != 'SYSTEM'
/
#
# 2. Produce offline and drop statements for each rollback segment:
select 'alter rollback segment ' || segment_name || ' offline;'
from dba_rollback_segs
where tablespace_name = '&TS'
and tablespace_name != 'SYSTEM'
/
select 'drop rollback segment ' || segment_name || ';'
from dba_rollback_segs
where tablespace_name = '&TS'
and tablespace_name != 'SYSTEM'
/
Review the output and, if you are happy with it, cut and paste it back into sqlplus.
# 3. Make a note of the old tablespace's size and location, then drop it.
drop tablespace
/
# 4. Create a new 'undo' tablespace in place of the old one.
create undo tablespace undo
datafile '
extent management local
/
# 5. Update the initialisation parameters
If you are using a spfile (and you should be!) run the following commands:
alter system reset rollback_segments scope=spfile sid='*'
/
alter system set undo_management=auto scope=spfile
/
alter system set undo_tablespace=undo scope=spfile
/
If you are still using a pfile (init.ora) then do the following:
Remove the following settings:
rollback_segments=...
Add/alter these two lines:
undo_management=auto
undo_tablespace=undo
# 6. Restart the instance
Note the location of the alert log. If there is a problem during start-up, you will probably receive nothing more than a message saying 'instance aborted'. The alert log will contain a slightly more detailed explanation.
Restart the instance:
shutdown immediate
startup
Rerun the query from step 1. There will be a whole load of rollback segments with names like '_SYSSMU1$'. This is an indictaion that the instance is now using auto undo.
Manual upgrade from 9i to 10g Release 2
http://www.shutdownabort.com
This procedure describes the steps necessary to manually upgrade a database from 9.2.x to 10.2.x. It assumes that you have already installed 10g onto the server. Of course, it is essential that you take a backup of your database before attempting any upgrade.
* 1. Compile any invalid objects
@?/rdbms/admin/utlrp.sql
* 2. Create a sysaux tablespace
Having a sysaux tablespace is a requirement in 10g. So, if you haven't already got one, create one now.
create tablespace sysaux
datafile '' size 512M
extent management local
segment space management auto
/
* 3. Run utlu102i.sql
This utility script checks that the database is ready to be upgraded to 10g. It also identifies any actions that need to be taken. The script is located in the 10g oracle home, so you will need to specify the full path to it.
@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlu102i.sql
Review the output and make any necessary alterations. Make a note of how many invalid objects there are.
* 4. Shut the database down with either normal or immediate
shutdown immediate
* 5. Copy the spfile (or pfile) and the password file from the existing home to the 10g one.
cp ${ORACLE_HOME}/dbs/*${ORACLE_SID}*/dbs/
* 6. Edit oratab
Alter /etc/oratab (or /var/opt/oracle/oratab) to point to the10g home. Once done, rerun oraenv to bring the alteration into effect.
* 7. Upgrade the database
sqlplus "/ as sysdba"
startup upgrade
This next bit is the upgrade itself. It takes roughly half an hour to complete. Spool the output to a file so that you can review it afterward.
@?/rdbms/admin/catupgrd.sql
* 8. Recompile any invalid objects
@?/rdbms/admin/utlrp.sql
Compare the number of invalid objects with the number noted in step 3. It should hopefully be the same or less.
* 9. Then check the status of the upgrade
@?/rdbms/admin/utlu102s.sql
* 10. Alter or remove initialisation parameters
Temporarily creating a pfile is the easiest way.
create pfile from spfile;
shutdown immediate
vi ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora
Alter/remove parameters identified in step 9. Set compatible to 10.2.0.0.0
startup
create spfile from pfile;
shutdown immediate
startup
That's it!
This procedure describes the steps necessary to manually upgrade a database from 9.2.x to 10.2.x. It assumes that you have already installed 10g onto the server. Of course, it is essential that you take a backup of your database before attempting any upgrade.
* 1. Compile any invalid objects
@?/rdbms/admin/utlrp.sql
* 2. Create a sysaux tablespace
Having a sysaux tablespace is a requirement in 10g. So, if you haven't already got one, create one now.
create tablespace sysaux
datafile '
extent management local
segment space management auto
/
* 3. Run utlu102i.sql
This utility script checks that the database is ready to be upgraded to 10g. It also identifies any actions that need to be taken. The script is located in the 10g oracle home, so you will need to specify the full path to it.
@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlu102i.sql
Review the output and make any necessary alterations. Make a note of how many invalid objects there are.
* 4. Shut the database down with either normal or immediate
shutdown immediate
* 5. Copy the spfile (or pfile) and the password file from the existing home to the 10g one.
cp ${ORACLE_HOME}/dbs/*${ORACLE_SID}*
* 6. Edit oratab
Alter /etc/oratab (or /var/opt/oracle/oratab) to point to the10g home. Once done, rerun oraenv to bring the alteration into effect.
* 7. Upgrade the database
sqlplus "/ as sysdba"
startup upgrade
This next bit is the upgrade itself. It takes roughly half an hour to complete. Spool the output to a file so that you can review it afterward.
@?/rdbms/admin/catupgrd.sql
* 8. Recompile any invalid objects
@?/rdbms/admin/utlrp.sql
Compare the number of invalid objects with the number noted in step 3. It should hopefully be the same or less.
* 9. Then check the status of the upgrade
@?/rdbms/admin/utlu102s.sql
* 10. Alter or remove initialisation parameters
Temporarily creating a pfile is the easiest way.
create pfile from spfile;
shutdown immediate
vi ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora
Alter/remove parameters identified in step 9. Set compatible to 10.2.0.0.0
startup
create spfile from pfile;
shutdown immediate
startup
That's it!
Subscribe to:
Posts (Atom)