In our experience, it is difficult to find an Access developer who hasn’t taken like a duck to water when working with SQL Server. Access and VBA are beautifully crafted products to work with and likewise, so is SQL Server. Although, if this is your first time working with SQL Server, just remember how long it takes to truly master Access, and don’t be surprised that it will take some effort to master SQL Server.
SQL Server offers you a familiar and easy to use GUI for undertaking design work, but it has one very significant advantage over Access: it also allows you to ignore the GUI and do everything with script files by using T-SQL. The SQL Server Management Studio has a clever feature, with which you can point at design objects in the GUI and with a quick right-click, you can get the tool to write the T-SQL script for you. Finding the balance between using the GUI and gradually using more T-SQL is how you more fully exploit the product’s power at your own pace.
This first section of the chapter discusses a number of important topics relating to SQL Server, but if this is your first time using it, you might want to quickly skim read this section and then return to this once you have had a chance to work with the product, in which case the section, “Getting Started with the SQL Server Management Studio,” , is a great point to get under way.
Programs vs. Services
Access is a program that can be run on your computer. The Access program is loaded into memory and then when the program needs to manipulate data that is on, for example, a shared directory, the data is loaded into your computer memory, as and when it is required, possibly passing over the network. Then it is filtered, manipulated, and displayed. This filtering of data can often mean that large parts of the database need to be transferred over the network to your computer memory. Access then uses a shared locking file to manage the updating of this shared data.
In as much as file–server-based databases such as Access are exceptionally good, as more users are added to the system and the amount of data in a system increases, performance can be reduced, and corruption can be more likely. One reason for this is that as you increase the demand on the data, you increase the network traffic, and you can have more copies of parts of the dataset in different computer’s memories. The question arises, what happens when a computer is inadvertently switched off when it is part way through an operation? The answer is that in most cases, it is the indexing that can become inconsistent, and you might need to compact and repair the database.
SQL Server is a client-server database. The database resides on a server. The server is dedicated to handling incoming requests for data, and then returning parts of the dataset to the client computers. If the client computers fail, the server can still guarantee the integrity of the data. Although you can think of SQL Server as a program, it is installed as a set of Windows Services, which are running in the server memory, listening for commands and taking appropriate actions. This client-server design has advantages when it comes to scaling up as more users and larger volumes of data are present. It also offers better security; because the server is normally not directly accessible, your database can’t be easily compromised.
When you run Access linked to SQL Server, you still have the Access program in memory, but Access no longer uses the shared file for managing updates to the data, and it is now forced to communicate through a special interface (normally ODBC or ADO, which you’ll read more about, shortly). This makes for a much better architecture on larger systems. But, there can still be problems with performance, and these problems relate to the volumes of data being sent from the server to the client.
Minimizing the amount of data requested to be displayed in a form
Access encourages developers to build forms bound to tables that can have thousands of rows and allow, for example, users to perform a Find operation to search for records. This approach works well for native Access Databases but is not a good idea in client-server; if you run a performance analyzing tool such as the SQL Server Profiler (discussed in Chapter, “Upsizing Access to SQL Server”), you will soon see how it generates very inefficient operations. In a well designed client-server system, you attempt to minimize the amount of data that you ask for. If your application uses filters and prompts users to make choices before then displaying more limited data volumes, you will significantly improve performance.
The second big part of an Access application is queries. Normally after switching to work with SQL Server, at least some of the queries will be slow to execute; developers tend to build very complex hierarchies of queries, many of which include very complex VBA or Access-specific functions.
Access can communicate with SQL Server by using linked tables from a standard Access database; these linked tables communicate via a technology called Open Database Connectivity (ODBC). This acts to translate requests from Access to a form that SQL Server can understand. This means that when you write an Access query, that query has to be passed through ODBC to SQL Server. But if that query uses specific Access features for which there is no easy translation through ODBC, then ODBC needs to request a lot more of the data and have Access perform local processing to use all those special features. This is why some of your queries might be running slowly. The good news is, with a little effort, they can normally be made to run a lot faster than they did in Access.
To improve performance, what we need to do is to convert the slow Access queries into a set of Views and stored procedures in SQL Server. This means that when converting the SQL, you might need to find equivalents to special Access and VBA operations by using T-SQL. Once converted, because everything executes in the server, you minimize the amount of data being sent to Access, and so resolve the performance issues. There are several other alternatives that can also help with improving performance, but the best solution is to move the complex query logic on to the server. One alternative that we will consider later in this book is to use a technology called ActiveX Data Objects (ADO), rather than Data Access Objects (DAO) model; this technology forces you to use SQL Server syntax in operations that avoids translation and can offer improved performance. Another alternative is to use Pass-Through queries, which will be described in Chapter 15. The third alternative is to build your application by using an Access Data Project (ADP). ADP brings you much closer to working with SQL Server and avoids using ODBC and instead uses ADO.
SQL Server Versions
The most recent versions of SQL Server are 2005, 2008, and 2008 R2 (and the corresponding SQLExpress versions). You can take backups from an earlier version and restore them into a database in a newer version, and you can alternatively detach an older version and attach it to a newer version and the databases will be upgraded. You cannot take a newer version and attach that to an older version of SQL Server. It is worth noting that SQL Server 2008 R2 is a newer version than SQL Server 2008, and you cannot attach a SQL Server 2008 R2 database to a SQL Server 2008, so if you detach a 2008 database, and then attach it to a 2008 R2 system, you cannot detach that and then re-attach it back to 2008, because it will have been automatically upgraded.
SQL Express and SQL Server Products
The SQL Server product range extends from the free SQL Express version to a truly enterprisewide product. If you’re getting started, then SQL Express is an ideal starting point. You will also find that as a developer, for minimum expense you can purchase a Developer Edition, with which you can more fully appreciate the different product features. You can then still use a SQL Express target because the databases are interchangeable between the different editions for a given product version.
At the time of this writing, the SQL Express download packages very much reflect the options that are available when performing a full product installation. You have the choice of downloading either the 32 or 64-bit versions of the product. If you have a 64-bit version of Windows, you can install either version, but the 64-bit version provides better performance.
You can download and install any one of the following:
- Database Only This installs only the database without any management tools, an unlikely choice for a developer computer, and a possible choice for a live environment.
- Management Tools (Only) This installs only the management tools, such as the SQL Server Management Studio; this is likely to appeal to you if you are working in an environment in which you have a server set up and you just want the tools on a workstation.
- Database with Management Tools The best choices to get started as you get the database and tools on your developer computer.
- Database with Advanced Services If you want to experiment with Report Server, this would be the choice to get everything that is available with SQL Express.
SQL Express is one of the greatest giveaways of the century. You get a fully functional SQL Server database, which allows databases up to 10 GB in size (with 2008 R2; 4 GB with older versions) and you get all the core features. Everything is also compatible if later on you migrate to a full product version.
Now considering things from the perspective of deploying the application to your client you might ask the following questions: what don’t you get and does it matter? Could SQL Express run a small business?
SQL Express is also limited to using one gigabyte of operating system memory and one CPU, which for many applications is not an issue. Apart from some of the more esoteric features, the one feature that you don’t get is the SQL Server Agent. The Agent is a powerful feature with which you can schedule maintenance plans, backups, and other operations.
So if you are using Express, you either need to find a way to run backups, or have some backup software that can back up a live running SQL Server. Just like Access needs a Compact and Repair, to maintain a SQL Server database in good condition, you need to run Maintenance Plans. The Maintenance Plans can perform integrity checking and backups, but more important, they rebuild indexing and create space for growth in the indexes. If you ignore this requirement then you can expect eventually to see a performance penalty.
INSIDE OUT The essence of a Maintenance Plan
We have included a sample script file called ExpressRebuild.SQL to help you with creating your own Maintenance Plan for SQL Express, this file uses more sophisticated techniques, which we will not cover in the book, but we believe it is useful to the more advanced programmer. In VBA, you can program with Recordsets, where you sequentially move through the records performing an operation. SQL Server has an equivalent mechanism called a cursor. Cursor programming is more difficult than working with a VBA Recordset because it is rather primitive and you need to take control of all the fields that you want to use. Having issued that disclaimer, cursors are very powerful, but sadly outside of the scope of this book. In the following code example, we have inserted a section of this code to give you a flavor of what is possible.
What follows is a nice example of cursor programming. It's a more complex example of T-SQL programming, so feel free to skip this until you have read the rest of this chapter:
-- find all the tables in the database
-- and rebuild all the indexes on each table
-- also run the database consistency checker on each table
DECLARE @TableSchema as Sysname
DECLARE @TableName AS sysname
DECLARE @Command AS VARCHAR(200)
-- Set the table fill factor
SET @FillFactor = 80
PRINT 'REBUILDING ALL INDEXES AND RUNNING CHECK ON TABLES'
DECLARE reb_cursor CURSOR
SELECT TABLE_SCHEMA,TABLE_NAME FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
FETCH NEXT FROM reb_cursor INTO @TableSchema, @TableName
WHILE @@FETCH_STATUS <> -1
IF @@FETCH_STATUS <> -2
PRINT @TableSchema + ' - ' + @TableName
SET @Command = 'ALTER INDEX ALL ON [' + @TableSchema +
'].[' + @TableName +
'] REBUILD WITH (FILLFACTOR =' +
CAST(@Fillfactor AS VARCHAR(10)) + ');'
SET @Command = 'DBCC CHECKTABLE ("[' + @TableSchema +
'].[' + @TableName + ']")'
FETCH NEXT FROM reb_cursor INTO @TableSchema, @TableName
Database File Locations
Once SQL Server is installed, by default, new databases are created in a specific folder. The folder name varies according to the main product version and whether it is a 32-bit or 64-bit version. It’s an easy process to change the folder path in the Database Files property when creating a new database, as shown below. When you enter the Database Name, this creates two logical file names <DatabaseName> and <DatabaseName>_Log. If you choose your own folders, then the only issue to watch out for is any required permissions that SQL Server needs on the folders.
The available pages and properties when creating a new database are different from those shown in Figure,once a database has been created.
In this case, the two files TestDatabase.mdf and TestDatabase_Log.ldf are located on the path C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2008R2MSSQLDATA.
Log Files and Recovery Models
After you create a database, right-click the database name in Object Explorer to display the properties. Configure the different settings for each database, as needed. Below it shows the Recovery Model set to Full.
If you are using a SQLExpress version, the Recovery Model defaults to Simple; when using the full SQL Server version, the default is Full.
An explanation of the differences in the Recovery Model relates to the Log files. In the previous section, you saw that when a database is created, you specify two physical files, one is the database file, and the other is the Log file.
When changes are made to the data in a database, these changes are recorded in the Log file. SQL Server uses the Log file for a number of purposes. First, it can roll back transactional work by using the Log file. It can also provide special views called INSERTED and DELETED, based around the data in the Log file. The Log files can also be shipped to other servers on a periodic basis by using a process called Log Shipping, in which they can be replayed to provide for backup Servers. SQL Server also supports mirror servers, including a hotstandby mode for server switching.
If you have a very large database, you can decide to back up the full database weekly, and only backup these Log files, recording the changes on a more regular basis. Once backed up, the Log file is then cleared out for re-use.
If you use a Full Recovery Model, the Log files continue to grow until you perform a backup on either the database or the Log files. In practice, over time the Log files can become very large if you are not regularly backing up the databases; they can even exceed the size of your database, swallowing up a lot of storage on your development computers. On a development computer, you probably want to choose the Simple Recovery Model. Using this setting, the Log files are continuously shortened, which keeps them under control.
This still allows you to be taking periodic backups of your database; it just means that you cannot use the more sophisticated features such as Log Shipping, which is found on production servers. In production environments, you might find that other people have responsibility for configuring the backup policy. If you are planning this, you could still adopt a Simple Recovery Model and rely on RAID and other technologies to secure your backups.Next, go to Database Properties | Options. Here, you will find two other options that you might wish to consider setting. First, set the Auto Close property to False (this is the default on SQL Server, but True on SQL Express), and set the Auto Shrink property to True (default is False).
If you end up with a large Log file and you cannot make it smaller, then investigate the DBCC SHRINKDATABASE command for help with this problem.
As part of the SQL Server installation process you have the choice to install your copy of SQL Server in either the default instance or use a named instance. Instances allow for multiple copies of SQL Server to be installed on the same computer; often these will be different versions of the product to support development for different versions, but it is also possible to install multiple versions of the same product. This could occur if you first install SQL Express and then later install a full version of the product. The name of your instance is by default your <computer_name> when installing the full product, and <computer_name>SQLExpress> when installing the express version of the product.
When you are defining connections to SQL Server, you specify the name of your instance as part of the connection information.
Management Studio can be used to work with multiple local, network, and remote connections to different SQL Servers at the same time, and it will also work with earlier versions of SQL Server. Below it shows Management Studio connected to a local instance of SQL Server 2008 R2 and SQL Server 2008. By right-clicking a server name, you can disconnect or connect to additional database servers. You can also click the Connect button (in the upper-left corner of the window) to display a drop-down menu, from which you can add other connections.
Use Object Explorer to connect to multiple database servers.
After you have installed SQL Server, go to Control Panel | Administrative Tools | Services. Here, you can configure a set of services for each SQL Server Instance.
After installing SQL Server, you can go to the Services panel to make changes to some of the Windows Services.
Most of the time, you won't need to make changes to these services, and the services that are available depends on whether you installed the full or express version, and whether you chose to also install some of the additional services.
On a developer computer, as a bare minimum, you want to have the main SQL Server and Browser services set to Automatic. You might choose to set some of the other services, such as the Agent, to Manual, and then switch them on if and when required; this saves your computer’s resources. If you have several SQL Server instances, you might decide to stop some instances that are only rarely required and perform a manual start from the Services panel (or by using Management Studio) if you need to run them.
Like Access, SQL Server installs some system tables. But it takes this experience further, because in addition to system tables, SQL Server has system databases, as shown below..
The system databases; master and tempdb are the most important.
Within your backup strategy, you might want to include the master database because it holds your server configuration. The following is a brief description of these databases:
- master This is the metadatabase. It records all the information on your databases and is key to running the SQL Server. Back it up as part of your regular backup routine.
- model This is an interesting but seldom-used concept, by which you put commonly used objects in model, and then whenever you create a new database, SQL Server clones the objects from model.
- msdb This is used for distributed transactions. msdb is a good example of a more sophisticated system database (but unfortunately, beyond the scope of this book).
- tempdb This is the working scratchpad for SQL Server. When SQL Server needs to create temporary objects, or when you need to create temporary objects, they go into tempdb; you don’t need to back up this database.
Unless you are diving deep into SQL Server, you can probably live your life without delving into the system tables; the same can be said for Access. Some developers find it essential to work with the MSys tables, and others ignore the whole topic. SQL Server has a similar scenario, except that it offers three approaches to this problem. First, if you want, for example, to get a list of the tables in your database, you can use the following:
SELECT * FROM sys.tables
But be aware that as with Access, if Microsoft decides to redesign these system tables, you are using what is effectively an unsupported feature. To insulate yourself from the system tables, you could use a built-in system stored procedure, as follows:
For a useful and instructive view into both how Microsoft writes T-SQL and how the system tables can be used, in the master database, go to Programmability | Stored Procedures | System Stored Procedures, right-click sys.sp_tables, and then select modify. You can see the Microsoft source code for the system stored procedure.
INSIDE OUT INFORMATION_SCHEMA Views
As an alternative to relying on SQL Server system objects or system stored procedures, if you look in your database (Views | System Views), you will see a set of ANSIcompliant Views, these are on the INFORMATION_SCHEMA schema. So, you can also use the following:
Suppose that you want to produce a documentation tool that can find a list of your tables. The best choice is the ANSI-compliant views because Microsoft needs to adhere to this; the second choice is the system stored procedures because Microsoft has maintained this level of insight for developers and is less likely to change these; and third comes the system tables, which do change. Although they give you maximum flexibility, you are likely in the future to have to make alterations if these system tables are significantly changed.