Transcript Armen Stein President J Street Technology, Inc. OFC201 Complete an evaluation on CommNet and enter to win!

Armen Stein
President
J Street Technology, Inc.
OFC201
Complete an
evaluation on
CommNet and
enter to win!
When Do You Need SQL Server?
Lots of users (>15-20 simultaneously active)
Lots of data (> 100,000 rows in any main table)
Extra security (physical access to data not
acceptable)
Lots of uptime (24x7 availability, hot backups)
Triggers (automatic code when data changes)
Web application usage
Slower or dodgy connections (WAN, Wireless)
The Problem
Many Access developers have a lot invested in
Access knowledge, standards and code.
Just linking tables to SQL Server, while it may work
for some areas, can cause severe performance
problems in others.
There are also other considerations like code
changes and security concerns.
A Solution
An Access application can be an excellent frontend client/server application to SQL Server, if you
follow some basic guidelines.
Use the knowledge and code you already have,
while gaining the benefits and power of
SQL Server.
SQL Server 2005 Express is a free download.
What is ‘Client/Server’?
The term “client/server” is thrown around a lot
According to SearchNetworking.com:
Client/server describes the relationship between two computer
programs in which one program, the client, makes a service
request from another program, the server, which fulfills the
request. Although the client/server idea can be used by
programs within a single computer, it is a more important idea
in a network.
The approach presented here is “client/server” because
the Access application requests information from SQL
Server, which does the work to fulfill the request. The
SQL Server is usually on a different computer.
Why Not Use ADPs?
Microsoft is not enhancing Access Data Projects
(ADPs) with the same enthusiasm as MDBs
ADPs require a learning curve and different
techniques from MDBs
ADPs do not allow direct linking to JET/ACE
tables (for local tables, work tables, legacy data)
ADPs are not necessary if you learn to use SQL
Server Management Studio, which professionals
should learn anyway
Migrating to SQL Server
You can use the built-in Upsizing Wizard or SQL
Server Migration Assistant (SSMA)
Or do it yourself, and you’ll learn more:
Build the tables in SQL Server with the data
types you want
Enforce relationships using DRI or triggers
Use Access Append queries to copy the data
into the new database
For large conversions, use macros to run a series
of queries
Add RowVersion?
You have a decision on concurrency checking:
Access normally compares every field in the
recordset to the last read value to see if anyone
else has changed the record. This can be slow
Worse, for floating point and Null Bit fields, the
comparison can fail and Access thinks the
record has been changed by “another user”
Two options: use RowVersion (aka Timestamp)
or don’t. Can be decided table by table
RowVersion Options
Use RowVersion
Do not Use RowVersion
Entire record will be included in
concurrency check
Partial row concurrency will check
only fields included in recordset
Float and Null Bit fields can be used
without concurrency problems
Float and Null Bit fields must be
avoided
Faster concurrency checking
Slower concurrency checking – use
fewest fields possible
Background processes and other
users will have more conflicts
Partial row concurrency will allow
different fields to be updated
concurrently
AutoNumber Key Retrieval Code
SQL Server does not generate Identity values until
after the record is committed.
If you are determining the new key this way:
rec.Add
lngNewCustomerKey = rec.CustomerKey
Then you’ll need to get it after the .Update:
rec.Update
rec.Bookmark = rec.LastModified
lngNewCustomerKey = rec.CustomerKey
This syntax works for JET/ACE tables too, so you
can make it your new standard
Find Performance Challenges
Common bottlenecks:
Updatable detail screens using large recordsets
(use a read-only index screen to open one record
at a time)
Combo boxes on large rowsources
Reports with complex queries
Index screens with complex queries
Data lookup functions (DLookup, DSum, DCount)
Use Read-Only “Index” Forms
Find the desired
record on the
‘Index’ form
Open the Detail
form for just the
selected record
Sorting and Selecting
Index forms need the power of passthrough
queries
Base the recordsource of the form on a
passthrough query that handles all the joins
Change the Where and Order By clauses in the
RecordSource based on user selections
Requery the form
For even more speed, change the SQL clauses in
the passthrough query itself
Using Passthrough Queries
To build: Query|SQL Specific|Passthrough
Very fast, but not updatable
Use native SQL Server syntax (% instead of *, etc.)
Can be altered in code using the QueryDef.SQL
property
Can be based on views or stored procedures for even
more speed and complexity
Passthrough queries don’t work for
subforms/subreports with master/child fields
Cheat! Use the Access or SQL Designer to get the
syntax, then paste it into the passthrough
Use NOLOCK
Even for non-updatable index forms, SQL Server
will place Read locks anyway. In high volumes
these can cause poor performance.
Add (NOLOCK) to your passthrough queries. You’ll
get uncommitted (“dirty”) reads, but this
doesn’t matter for most apps.
It goes right after each table in the FROM clause,
like this: Select * from Products (NOLOCK)
Lighten Up Your Detail Forms
Make sure your detail forms are not requesting
too much data
If you have tabbed forms, they are all being
loaded when the main form loads, which can
cause delays. Look at code to delay loading
them until each tab is clicked
Use fewer comboboxes (and reduce row counts
with selection criteria if possible)
Make sure your form’s recordsource does not
include unnecessary joins
Combo Boxes
To improve performance, base your “heavy”
comboboxes on passthrough queries
In the rowsource, specify
“qsptcboMyQueryName”
For “cascading” combos, change the
QueryDef.SQL property of the query the lower
combo box is based on, then requery it
Calculations in Queries
Calculations in Access queries can be slower than
SQL Server. Narrow it down by testing them
without the calculations. If you find a
performance issue, try recreating IIF and other
calculations in stored procedures (T-SQL).
or,
If you must use calculations in Access queries,
build a passthrough query to get the data you
need. Then base your Access query (with the
calcs) on the passthrough query.
Relinking to SQL Server
Linking to SQL Server is done two ways:
Linked tables and views for updatable forms and
subforms/subreports
Passthrough queries (with ODBC connection
parameters) for everything else
Search the Internet for DSN-Less ODBC
linking techniques
Which Kind of Security?
Windows Authentication
SQL Server Authentication
Enabled by default
Must be enabled in SQL Server
Properties
Works only inside the Windows
domain
Works across the Internet
Credentials more secure – not stored
in the app
Credentials less secure – stored in
the app (see next two slides)
User has credentials to work with
data outside the app
User doesn’t need to know the
credentials – and can’t work with
data outside the app
Mitigating SQL Authorization Issues 1
Username and password are stored in a table and
in passthrough queries – a concern
Password input mask is applied in the table
Hide the database window and turn off special
keys (e.g. F11) in the Startup Options
Clear your password before you send the
application to your customer
And just in case you forget, use a low privilege
SQL Server user in your application (not SA!)
Mitigating SQL Authorization Issues 2
For more security, turn off the AllowBypassKey
database property (but also provide a way to
turn it back on!)
The password can be cleared out automatically,
forcing user to log in each time (but this
eliminates the advantage of the user using the
app without knowing the password)
For more security, you could set up Access
security (but it was dropped in the latest 2007
ACCDB format)
Bonus Trick
Performance issue with complex passthroughs
Access forms and reports will evaluate the
recordset before the Open event, in order to get
the column definitions of the recordset. This
can cause a serious delay
To avoid this, set the Where clause of the
passthrough query to “WHERE 1 = 0” when the
form or report closes
The object will now open quickly. Reset to the
proper Where clause in the Open event
Armen Stein
J Street Technology, Inc.
www.JStreetTech.com
425-869-0797 x107
[email protected]
Resources
www.microsoft.com/teched
www.microsoft.com/learning
Sessions On-Demand & Community
Microsoft Certification & Training Resources
http://microsoft.com/technet
http://microsoft.com/msdn
Resources for IT Professionals
Resources for Developers
www.microsoft.com/learning
Microsoft Certification and Training Resources
© 2009 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.
The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should
not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS,
IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.