« April 2006 | Main | June 2006 »

May 31, 2006

Implementing SQL Express (My TechEd 2006 Session)

Day/Time: Wednesday, June 14 3:45 PM - 5:00 PM Room: 157 ABC
Speaker(s): William Vaughn
SQL Express is the version of SQL Server 2005 designed to replace MSDE. In some scenarios, SQL Express is a viable replacement for the JET/Access database engine - it's safer and more reliable. Because SQL Express does not behave like JET in most respects, it can be a challenge for JET developers to understand how to control SQL Express and leverage its power from their applications. This talk discusses the differences in SQL Express, MSDE, JET and the other SQL Server Editions - including the just-announced SQL Server Everywhere edition. See how SQL Server Everywhere (which builds on the proven codebase of SQL Mobile) fills a need for a lightweight DBMS where SQL Express might be overkill. The session includes an overview of the new SQL Server Management Studio Express toolset and code used to manage SQL Express (or any instance of SQL Server) including discovery, starting, stopping and pausing instances. We also demonstrate how Visual Studio leverages SQL Server Express functionality into the IDE and permits developers to create, configure and deploy SQL Server Express databases with their applications.
Track(s): Database Development and Administration
Session Type(s): Breakout Session
Session Level(s): 200

Posted by William Vaughn at 11:07 AM | | Comments (0)

Are Stored Procedures Faster than Ad-Hoc Queries?

Yet another question was posted in the public ADO.NET newsgroup and I decided to use this forum to answer this fairly common question: Are Stored Procedures (SPs) faster than ad-hoc queries? One misconception is that SPs are compiled into some binary-like code and simply loaded into memory on each invocation--kinda like a DLL. They aren't. In order to take advantage of the current data "statistics" (how the information is distributed in the tables being referenced), each query is recompiled (to some extent) at runtime.

No, not every statement is recompiled on each use. The mechanism works like this (somewhat simplified): When you execute a query (parameterized or not) it's syntax checked, compiled, placed into the common RAM cache and executed. The compiled query sits in the RAM cache until space is needed for something else. When you execute the same query (or one that's pretty close--perhaps with a different parameter value) there is no need to recompile the query--the cached query is simply rexecuted with the new values.

A stored procedure works the same way but the server can recognize it more quickly and knows if it has its query plan in cache--it does not have to be checked for correct syntax--that was done when it was first created. So, yes, there is a little performance benefit whenrunning a SP when compared to an ad-hoc query. While there is the additional (minor) benefit of the number of bytes sent to the system to execute the SP is far lower (just the string that has the name and parameter values)--this benefit is not that great.

What Are the Benefits of Stored Procedures?
A real benefit to SPs is in centralized management of the logic in the SP. SPs get the code OUT of the client applications so when the logic needs to be changed developers (and DBAs) can change it without redeploying the application (assuming the signature does not change). This is good. This means it's cheaper to deploy SP-based applications as it does not have to be done as often (hopefully only once). It also means that the client application need not have access to the base tables. Most corporate DBAs won't permit this. It's their job to protect the data so they limit rights to the base tables--granting rights to selected SPs and Views. When I ask a mob of people in one of my sessions how many are not using SPs, I get a few hands--the vast majority (over 95%) use them, insist on them and fully leverage the additional power they add to their applications. Consider that a SP is a program--it might be several hundred lines long. It means that a single invocation from a client application can handle a dozen (or more) operations and return information about each one to the client. Most serious application shops eventually find the need for SPs. They find that it's far too expensive to handle low-level table operations on the client and they never have to worry about SQL injection...

Consider that SPs are supported on all versions of SQL Server except SQL Everywhere--using the exact same syntax.

hth

Posted by William Vaughn at 10:23 AM | | Comments (0)

JET? Are developers still considering it?

As I have talked about many times here, in newsgroups and in my sessions, books and articles, IMHO JET/Access is not suitable in a business application. I've detailed the problems that include its physical IO over the network (which makes it prone to data loss), its inability to be backed up while any application is still connected, its need to be periodically compressed, it's lack of scalability (which means you have to start over when the customer outgrows it), it's (very) limited capacity and the number of users it can support. It should never be used as a web host unless you don't mind terrible performance--that's because it's a "client" engine--not a service designed to support parallel operations. One of the most serious problems is its inability to encrypt data in place and its fragile security system. This makes it "officially" unsuitable to manage HIPA-compliant data. The fact that you access JET over a very problematic MDAC stack is and always has been another problem. Each time the stack is changed, your application might or might not work. Jet is also accessed over a COM-based OLE DB interface--in unmanaged code.

The alternatives? There are several:

* SQL Server Express edition: This is a free DBMS engine that can be used as a server service visible to selected clients on the network--with or without a domain. It has a 4GB upper DBMS limit, can support hundreds of users and can execute the entire set of TSQL commands executable by the expensive versions. It requires no compression, can be backed up live, is undisturbed by power loss on the system and can support multiple simultaneous threads. It's fine to use this engine as an IIS-hosted DBMS. Unlike MSDE, SQL Server express has no "governor" and comes with (SP1) a set of developer tools that are very similar to those shipped with the other SKUs. SQL Server supports column encryption as well as a rich set of operators that are identical to those implemented on the upscale versions. This means applications written for SQL Express will be able to be easily ported to the upscale versions with virtually no conversion. SQL Express can be implemented as a client engine or as a service. The newly designed setup and deployment schemes make it far safer and easier to deploy on client systems (if necessary) or on a central “server” (which can simply be one of the client systems that’s always visible to the others).

* SQL Server Express--Advanced Services edition: This is SQL Server Express with additional functionality that includes Reporting Services and Full Text Search. While this increases the DBMS footprint, it makes it ideal for small-businesses that need a central reporting engine or want to create more sophisticated applications.

* SQL Server Everywhere: This is essentially the well-know SQL Mobile/SQLCE engine that was originally designed for PDAs and Smart Phones. Its strength is its ability to replicate to a SQL Server (Workgroup edition or better) server to keep data on the remote device in sync. It has a very small footprint and its database can be totally encrypted. While just cleared (license-wise) for use on Windows desktop systems, its architecture and DBMS engine was never designed for use on a website. It supports a subset of the T‑SQL used in the other editions (for example, it does not have stored procedures), but it’s ideal for many lightweight single-user situations where you simply need a fast, light data repository that can handle SELECT, JOIN and the other basic relational DBMS functions. I think this version has a lot of promise as a local cache engine, sophisticated business rule management schemes and many other uses.
All All of the SQL Server versions are closely integrated into the Visual Studio toolset. The MDF databases can be deployed with an application or installed separately. When it comes time to update the engine, it's included in the Windows Update list of applications to update.

Yes, many people still rely on JET. Sometimes it's because it's all they know how to use--it's what they have been telling their customers to use for a decade--they have a stake in this intellectual and business investment. Yes, customers think it's okay until their data is lost or compromised or they outgrow it. I’ve dealt with a flood of people who have found JET has infiltrated their businesses—making debacles like the VA analyst’s loss of 26.5 million records a reality.

No, any new design should not use JET. Existing designs should move to a safer, more scalable and better supported DBMS. Keep in mind that Microsoft is trying to help customers world-wide to move away from JET and get out of the hole they’ve dug for themselves. There are plenty of Microsoft and non-Microsoft alternatives that make more sense. Unfortunately, Visual Studio best supports SQL Server. If that's your development platform, it's best to stick with some version of SQL Server.

I'll be discussing these issues in my sessions at TechEd Boston and in my ADO.NET workshop at VSLive in Las Vegas. I hope to see you there.

Posted by William Vaughn at 09:43 AM | | Comments (0)

May 29, 2006

My Visual Studio.NEXT (Orcas) Wish List

Microsoft’s Orcas development team asked for some feedback on Visual Studio in a recent SDR. Since my opinions are not covered by the NDA, I’m free to share these ideas with the public. I also submitted a brief summary of this list to my readers at Processor Magazine where I encouraged them to provide additional suggestions and feedback.

The next release of Visual Studio .NET (that Microsoft has dubbed “Orcas”) is due out sometime in late 2007. Many folks tell me that they’re getting more and more push-back on this runaway train given all of the changes that keep rolling out of Microsoft’s new and improved (AKA “you should do things our way”) department. The suggestions shown here are those that help developers work with existing architectures—making them more productive by leveraging their existing skill sets and training.

My Visual Studio .NET Wish List:

The following bullets outline the features I would like to seem implemented in Visual Basic.

·         Better support for existing architectures, programming methodologies, data structures and applications. This means that more emphasis needs to be placed on fixing “won’t fix”, or “by-design” bugs and fixing the documentation for existing functionality before introducing new technology. I expect that many of the newly proposed features will be used by 0% of the developers when first launched. In contrast, improvements to the UI and the existing Visual Studio product will have immediate positive impact on everyone. Yes, after awhile, new features will gain ground as the doc catches up and third-party architects, analysts, authors and trainers figure out and show where these new features make sense.

Editor and UI

·         The Visual Basic editor needs a better way to deal with large code blocks. That is, the editor needs to be able to zoom (like Word or IE). Control wheel is the best way to deal with this.

·         The editor should have a built-in spell-check for all string literals. This way we can ensure that the words we put in front of the user are spelled correctly. This check might go so far as to correct grammar and (eventually) do so in the target language. The work is already done (in Microsoft Office)—it simply needs to be leveraged in Visual Studio.

·         The editor should make anything popped up translucent or moveable. That is, intellisense should not hide the code you’re trying to debug or write.

·         The debug window still needs a way to be cleared automatically on each run (or not) or at least timestamp headers should be added so we can tell which messages go with which execution.

·         When I click F1 after selecting an object, the UI should take me to the right help topic—at this point it works about 50% of the time. All too often it jumps out to a generic topic, a topic in an entirely irrelevant context or Cleveland.

·         When I drag code from one place to another, it would be nice if extra linefeeds were not added.

·         It would be great if I could copy in source code from a different language and have Visual Studio convert it to Visual Basic. Since most of the examples I find in Google are in C# when I’m working in Visual Basic .NET, (and vice versa), I would be great to be able to past to a “transmogrify” window what would convert the code to the host language. It would be even better if the UI could figure out what references need to be added and give me the option of adding Imports/Include statements.

·         Automatically add imports for unreferenced objects and suggest Add Reference DLL for objects.

General Features

·         Printing is vitally important. Where is the promised support? This needs to be in My and made to work. This includes enumerating printers, setting print properties, launching and canceling jobs.

·         Make it easier to implement secure applications. Visual Studio does not support developer personas. That is, developers often need medium-level rights to do their work—admin rights at some time and far more limited rights at other times. However, to test an application targeted at a specific user with limited rights, the developer must create a separate configuration (sometimes on a separate system) to test the application. I think Visual Studio needs the ability to let the developer execute the application “as” another user with specific rights.

·         Documentation still needs a lot of work. Working with many of the new classes like the ReportViewer control is painful. Too many of these help topics seem to be prototypes—just the basic property and what values it accepts. One of most common complaints is lack of useful documentation.

·         All resource finders including the Add Reference, Server Explorer (Tables, Stored Procedures) etc. need to have intelligent, filters to show only those items whose name meets the criteria. It might be a filter on name, date created, owner or other aspects.

Data Issues

·         Visual Studio and Visual Basic still does not support rights management for databases. It assumes that this is done by DBMS-specific applications (in SQL Server Management Studio for SQL Server and the equivalent tools in Oracle or JET). Managing rights is essential for data security. Relegating it to the vendor seems expedient, but there is no reason that the tools written for SQL Server could not be ported to Visual Studio. As I see it, vendors (including other Microsoft teams like SQL Server) need to be encouraged to add essential functionality into Visual Studio.

·         Visual Studio and Visual Basic does not support intellisense on T‑SQL. This language is more widely used in some companies than Visual Basic or C#. Since SQL Server Express and SQL Server Everywhere are gaining ground, it’s even more important to permit developers to create correctly coded T‑SQL queries—whether they are used in a stored procedure or imbedded in a prototype application. In addition, the T‑SQL editor in Visual Studio is not nearly as robust as that used in SQL Server Management Studio, but that product has dropped T‑SQL debugging. This means the T‑SQL must be developed in SQL Server Management Studio, but tested in Visual Studio.

·         For the same reasons, the query builder needs to be improved to incorporate T‑SQL intellisense as well as the ability to create more complex queries. As it is, the Query Builder fails to deal with multiple resultsets or more sophisticated queries. This tool also needs to be expanded to permit developers to build LINQ and other new-technology data access queries.

·         In the data area, ADO.NET must (simply must) have the ability to control how the query is executed. There is an entire class of legal T‑SQL operations that cannot be executed via sp_executesql. We need a pass-through property on the Connection or the Command that permits ADO.NET to simply execute the CommandText.

·         ADO.NET also needs to be patched to further prevent connection leaks. This can be as simple as adding CommandBehavior to the rest of the Command execute methods. We also need the ability to set the time a connection is held in the pool—while it looks like there is a ConnectionString key/value pair for this, it does not work as desired.

·         CLR executables for SQL Server need to have a more flexible built/deploy/debug model. Developers need to be able to test with or without first building or deploying. The reasons for this have to do with issues involving dependencies that don’t need to be torn down in all situations. We also need the ability to set/reset the “it’s okay to alter this assembly” switch in SQL Server to leverage this feature.

·         The Visual Studio create Data Source dialog should eliminate the “checkbox” on the Tables list (that adds all visible data tables to the TableAdapter). Don’t let developers choose “all tables”. This is just wrong.

·         The mechanism to alter a design-generated TableAdapter when the database schema changes needs to be simple and obvious.

·         As it is, the way Visual Studio propagates SQL Server Express .MDB files has gotten out of hand. There are scenarios that can create 6 or more copies (in various states) of a database simply by creating a database in a project, choosing the (wrong) option, creating a deployment config, deploying and using User Instance=True. This seems wrong and needs to be reexamined

·         One of the best ideas we suggested is template-based data generation. This approach would let us modify the base template used for code generation. This way we can build our own plumbing into the TableAdapter and other generated classes to implement business rules and constraints.

·         Stored procedures form today’s implementation of N-tiered designs. Visual Studio needs to make design, development, protection, execution and debugging stored procedures a priority. This approach should be a must-have for any new technology as well as a fundamental goal of any new Visual Studio version. That is, the new version should find ways to improve this common development paradigm. The 2005 version of Visual Studio handles stored procedures better than ever, but still discards critical syntax error information in the editor. It also does not permit developers to set runtime rights or manage object rights. Visual Studio also does not provide a clean mechanism to manage stored procedure versioning.

·         Developers write millions of lines of code to implement business constraints and logic. We still don’t have a standard way to implement business rules in the UI, middle-tier or on the database. The mechanisms to do this were added to SQL Server in the 2000 timeframe. The data teams promised to link to these properties (Extended Properties) but it never came to pass. We need a standard way to write a class that implements business rule constraints on a column-by-column and rowset basis. That is, tests that validate each column as well as those that validate the entire row before it’s passed to the server in an UPDATE or INSERT. These same constraints should be managed on the client and server with the same structures. I think if the generated code included a standard (user-definable) interface, we could reduce the code to deal with this issue.

I Want Your Feedback

If you have comments on these suggestions or have others you want to propose, post them here. Sure, the site asks you to validate your email address—I have to do that to prevent the spammers from sending everyone ads for other “enhancement” products. Be sure to describe your development shop in the response so we can better understand the issues you’re facing. Remember, I’m trying to act as your voice up on the other hill overlooking Redmond. Let me know what you’re wishing for—besides a higher Microsoft stock price.

 

Posted by William Vaughn at 02:46 PM | | Comments (1)

VA Offers $.02/Record To Recover Stolen Data

I strongly encourage all veterans to visit the announcement page issued by the Veteran’s Administration that explains how a VA data analyst lost 26.5 million veteran records to a common thief. What the article does not explain is why the data was not encrypted. Today, it's common practice to ensure that data is encrypted on any system that can be stolen (a laptop or an external or removable drive).

As identity theft is one of the fastest growing criminal industries, I also encourage veterans to freeze any banking and credit accounts that can be compromised by someone obtaining this information. By federal law everyone is entitled to free annual credit checks (more often in some states) and the ability to freeze our accounts at no cost in cases such as this. That’s the upside—we now have a legitimate excuse to freeze our accounts. See the aforementioned announcement for details.
According to the article "It is possible that they remain unaware of the information which they possess or of how to make use of it. " This is laughable. Do not assume that this situation is too obscure for the thief to understand the importance of the data. The VA and press have made it perfectly clear that they have stolen a very valuable hard drive--and they gave him (or her) a 3-week head start and put a $50,000 price on their head. That works out to about $.02 per record lost. I guess the VA only thinks that's what our security is worth.

Posted by William Vaughn at 01:51 PM | | Comments (0)

May 27, 2006

TEchEd 2006 ADO.NET Gathering

Jonathan asked me to post this... I plan to go.

Hi All, we are putting together a gathering of ADO.NET, and data connectivity lumaries for an informal gathering during TechEd 2006. This will be an opportunity for everyone who lives and breaths data connectivity, ADO.NET and who is looking to get a first hand feel of everything on the horizon for ADO.NET, LINQ and .NET and meet some of the key players involved.
The date ? Meet up is on June 13, 2006
The time ? Between 4pm and 6.30pm
The where? Seaport Hotel in Boston.
If you think you are the kind of person who would be interested in this, either comment below, or shoot me an email! Of course there will be food and drink for all, so sign up fast as space is limited!
-Jonathan Bruce .NET & XQuery Program Manager
DataDirect Technologies
Jonathan Bruce [jonathan.m.bruce@gmail.com]

Posted by William Vaughn at 10:18 AM | | Comments (0)

May 25, 2006

Proliferating Project-based Databases

Ok, so you heard that it’s possible to use Visual Studio to create a new SQL Server database and add it to your project. As a matter of fact, you can add any number of SQL Server databases to your project and Visual Studio will be happy to comply. How happy you are with debug performance might be another matter but I’m getting ahead of myself. This practice seems to be important enough for Microsoft to include the functionality in the IDE so if
this is something you do or plan to use as an easy database deployment technique read on—especially if you’re targeting Smart Client/Windows Forms applications. I understand how this can be appealing. All you have to do is add the database file to your project and Visual Studio automatically includes it in your deployment binaries.



Getting Started: Adding a Database to the Project

When you add a new SQL Database item to your project, Visual Studio asks the local instance of SQL Server Express (which must be present) to create the new .MDF file. It then (for some reason) prompts you for the tables you want to use in your strongly typed Dataset. Ah, what tables? A new database does not (generally) have tables—unless you’ve populated the model database beforehand. Let’s ignore that issue for now.

Of course, the easiest way to create a database is to steal (er, “leverage”) it. That is, take an existing database and extract the schema to a script and run the script against the new database. One can also backup an existing database and restore it to another or use the detach/attach approach. I discuss these (more difficult) techniques in my new book “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”.

It turns out that Visual Studio can make this a lot easier. If you already have a database file (for example .MDF for SQL Server), you can simply add an “existing” item to your project and point to the .MDF file. Again, the Visual Studio IDE prompts you for table(s), views, stored procedures or functions from which to build a DataSet. Now this makes (more) sense.

Populating the Database Schema

We’re ready to take the next step—adding tables and whatever views, stored procedures and data to your new database. In your Visual Studio Database or Server Explorer search for the newly added .MDF Data Connection. The explorer can be used to define the tables and most of the other SQL Server schema you’ll need. I won’t walk you through that as it’s discussed elsewhere (and in the book). For this exercise I added a single table by selecting the Table icon, right-clicking and selecting Add New Table. This opens a (very lightweight) table designer that depends on a companion property page dialog to set additional column attributes. Click the “save” ICON to commit the table to the database. Your database is now set to the minimum size so don’t measure the performance or disk footprint of the deployed application by this value. My book’s example Biblio database is about 500mb which (frankly) is not that big as typical business databases.

Adding Data to the Database

Create a Data Source for the project that addresses the newly created table and drag it to the form. This builds a simple UI that permits you to test the database and add data thereto. Start a debug session (click on the “run” button), enter some data in your new table, click “Save” in the tool strip (the disc icon) and end the debug session. Start another debug session and look for your data—ah, it’s missing. That can be very frustrating if you spent considerable time loading data into your database.

The problem here is that you weren’t working with your project database (the first instance of the .MDF file), but with the copy of the .MDF file that was created when you started the debug session. The 2nd instance is written to the project\bin\debug directory. If you create a “production” build (and you will eventually), that creates yet another copy of the original (1st instance) database. These “base” .MDF files are only about 2.2mb in size so this won’t exactly fill up your 80GB hard drive but a “real” database can make each test run take longer as the .MDF is copied to the target directory. But we’re not done yet.

Publish the Database-Laden Project

Right-click on the Visual Studio project and step through the Publish wizard. For this exercise I published the project to a directory on my local hard drive. When the wizard’s done you have (you guessed it) another copy—we’re up to the 3rd instance of the .MDF file (ExampleProjectDatabase.mdf.deploy).

Setup and Test the Project

Click Setup in the deployed application directory. Since Setup installs (decompresses the setup deployment cabs) and runs the application, two more copies of the database are created in:

C:\Documents and Settings\billva\Local Settings\Apps\2.0\Data\VP34KLZ1.C42\G7V79PAG.JB1\coun..tion_a936baec72ccee19_0001.0000_7fa3d49e679e09d3\Data

And in

C:\Documents and Settings\billva\Local Settings\Apps\2.0 \X7KAKD1X.A7H\2CM1EY17.WTM\coun...exe_a936baec72ccee19_0001.0000_none_8ca8a7b93cbb0b16

The approach we’re taking has created the 5th and 6th instances. Fortunately, the data saved in the last instance is not overlaid when the application is restarted. That would be very bad. Consider that the default behavior for Visual Studio is to use the new (and unproven) “User Instance” paradigm that makes an additional copy of the database and places it in the user’s disk space. You saw evidence of this above.

A Better Solution

Actually, there are several other approaches that you can take to avoid the proliferation of (useless) copies of the .MDF files on your and your customer’s system. You can see how if the database contained proprietary data it’s important that loose copies not be created at every step in the development, testing, deployment and execution process.

Go back to your original project. Here we can make a simple change (from the default behavior). If you insist on adding a MDF database to your project, you can at least set the Copy to Output Directory property to “Do not copy”. This prevents the database from being overlaid each time you start a debug session. Nope, this won’t work if you don’t manually copy the .MDF file from the project directory to the bin\debug directory. Remember to copy the Debug version of the database back to the project directory before deploying as that’s the database instance that’s used to build the deployment CABs.

This approach helps when you need to pre-populate the database with data before deploying it but it also means you’re still creating additional databases all along the way.

The Best Solution

Frankly, I’m not a fan of imbedding the database file in a project. I think SQL Server databases should be attached to the appropriate instance of SQL Server during the development process, detached to a standalone MDB file, shipped with the application and attached to the local instance of SQL Server at runtime with your own code. Consider that this approach means you have at most two copies of the database file to keep safe.

As I said, this topic is beaten into submission in my new book due on the shelves later this year… Questions? Just ask.

Posted by William Vaughn at 02:04 PM | | Comments (0)

Database Rabbitesque Proliferation







Using Project-based Databases






Proliferating Project-based Databases


Ok, so you heard that it’s possible to use Visual Studio to
create a new SQL Server database and add it to your project. As a matter of
fact, you can add any number of SQL Server databases to your project and Visual
Studio will be happy to comply. How happy you are with debug performance might
be another matter but I’m getting ahead of myself. This practice seems to be
important enough for Microsoft to include the functionality in the IDE so if
this is something you do or plan to use as an easy database deployment
technique read on—especially if you’re targeting Smart Client/Windows Forms
applications. I understand how this can be appealing. All you have to do is add
the database file to your project and Visual Studio automatically includes it
in your deployment binaries.

Getting Started: Adding a Database to the Project

When you add a new SQL Database item to your project, Visual Studio asks the local instance of SQL Server Express (which must be present) to create the new .MDF file. It then (for some reason) prompts you for the tables you want to use in your strongly typed Dataset. Ah, what tables? A new database does not (generally) have tables—unless you’ve populated the model database beforehand. Let’s ignore that issue for now.

Of course, the easiest way to create a database is to steal (er, “leverage”) it. That is, take an existing database and extract the schema to a script and run the script against the new database. One can also backup an existing database and restore it to another or use the detach/attach approach. I discuss these (more difficult) techniques in my new book “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”.

It turns out that Visual Studio can make this a lot easier. If you already have a database file (for example .MDF for SQL Server), you can simply add an “existing” item to your project and point to the .MDF file. Again, the Visual Studio IDE prompts you for table(s), views, stored procedures or functions from which to build a DataSet. Now this makes (more) sense.

Populating the Database Schema

We’re ready to take the next step—adding tables and whatever views, stored procedures and data to your new database. In your Visual Studio Database or Server Explorer search for the newly added .MDF Data Connection. The explorer can be used to define the tables and most of the other SQL Server schema you’ll need. I won’t walk you through that as it’s discussed elsewhere (and in the book). For this exercise I added a single table by selecting the Table icon, right-clicking and selecting Add New Table. This opens a (very lightweight) table designer that depends on a companion property page dialog to set additional column attributes. Click the “save” ICON to commit the table to the database. Your database is now set to the minimum size so don’t measure the performance or disk footprint of the deployed application by this value. My book’s example Biblio database is about 500mb which (frankly) is not that big as typical business databases.

Adding Data to the Database

Create a Data Source for the project that addresses the newly created table and drag it to the form. This builds a simple UI that permits you to test the database and add data thereto. Start a debug session (click on the “run” button), enter some data in your new table, click “Save” in the tool strip (the disc icon) and end the debug session. Start another debug session and look for your data—ah, it’s missing. That can be very frustrating if you spent considerable time loading data into your database.

The problem here is that you weren’t working with your project database (the first instance of the .MDF file), but with the copy of the .MDF file that was created when you started the debug session. The 2nd instance is written to the project\bin\debug directory. If you create a “production” build (and you will eventually), that creates yet another copy of the original (1st instance) database. These “base” .MDF files are only about 2.2mb in size so this won’t exactly fill up your 80GB hard drive but a “real” database can make each test run take longer as the .MDF is copied to the target directory. But we’re not done yet.

Publish the Database-Laden Project

Right-click on the Visual Studio project and step through the Publish wizard. For this exercise I published the project to a directory on my local hard drive. When the wizard’s done you have (you guessed it) another copy—we’re up to the 3rd instance of the .MDF file (ExampleProjectDatabase.mdf.deploy).

Setup and Test the Project

Click Setup in the deployed application directory. Since Setup installs (decompresses the setup deployment cabs) and runs the application, two more copies of the database are created in:

C:\Documents and Settings\billva\Local Settings\Apps\2.0\Data\VP34KLZ1.C42\G7V79PAG.JB1\coun..tion_a936baec72ccee19_0001.0000_7fa3d49e679e09d3\Data

And in

C:\Documents and Settings\billva\Local Settings\Apps\2.0\X7KAKD1X.A7H\2CM1EY17.WTM\coun...exe_a936baec72ccee19_0001.0000_none_8ca8a7b93cbb0b16

The approach we’re taking has created the 5th and 6th instances. Fortunately, the data saved in the last instance is not overlaid when the application is restarted. That would be very bad. Consider that the default behavior for Visual Studio is to use the new (and unproven) “User Instance” paradigm that makes an additional copy of the database and places it in the user’s disk space. You saw evidence of this above.

A Better Solution

Actually, there are several other approaches that you can take to avoid the proliferation of (useless) copies of the .MDF files on your and your customer’s system. You can see how if the database contained proprietary data it’s important that loose copies not be created at every step in the development, testing, deployment and execution process.

Go back to your original project. Here we can make a simple change (from the default behavior). If you insist on adding a MDF database to your project, you can at least set the Copy to Output Directory property to “Do not copy”. This prevents the database from being overlaid each time you start a debug session. Nope, this won’t work if you don’t manually copy the .MDF file from the project directory to the bin\debug directory. Remember to copy the Debug version of the database back to the project directory before deploying as that’s the database instance that’s used to build the deployment CABs.

This approach helps when you need to pre-populate the database with data before deploying it but it also means you’re still creating additional databases all along the way.

The Best Solution

Frankly, I’m not a fan of imbedding the database file in a project. I think SQL Server databases should be attached to the appropriate instance of SQL Server during the development process, detached to a standalone MDB file, shipped with the application and attached to the local instance of SQL Server at runtime with your own code. Consider that this approach means you have at most two copies of the database file to keep safe.

As I said, this topic is beaten into submission in my new book due on the shelves later this year… Questions? Just ask.

Posted by William Vaughn at 02:04 PM | | Comments (0)

May 24, 2006

Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)--It's Done

The latest edition in the Hitchhiker’s Guide series is finally done—at least my part. The book is in edit (and has been for awhile). This means we can expect to see it on shelves in about four months (hopefully less). Clearly, this is not a good way to get current information into your hands. As it is, the book is over 800 (Microsoft Office Word) pages. That means it’ll push 900 pages on the shelf if we keep all of the content. Yes, it will also include a DVD and a slew of examples, sample databases and possibly some Camtasia™ videos (if I find the time). I plan to post the outline here before long.

I have already outlined a half-dozen Ebooks to supplement the HHG. I’m still looking for a publisher that knows how to best expose these to the web without giving them away. Since my readers are some of the brightest people in the world, they also have the skills to defeat any simple electronic format. Anyone with a suggestion as to an IP rights management system should drop me a line or comment here.

Posted by William Vaughn at 05:38 PM | | Comments (0)

TechEd 2006 -- I'll Be There

I’ll be speaking at TechEd 2006 (US) this year in Boston. Sure, I’ll be doing a session—it’s on SQL Server Express Edition that’ll make it clear where it fits and what it replaces. And of course, I’ll be helping those addicted to JET find ways to kick the habit and move to better alternatives—without a 12-step program. I’ll also be talking (briefly) about the newly rebranded SQL Server Everywhere edition. You once knew this edition as SQL Server Mobile and SQL Server CE before that.

They tell me that I might (just might) be able to give a chalk-talk on the new Visual Studio ReportViewer control. I just finished writing my new book “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)” so I’ll be providing insights on all of the new features of Visual Studio and those that aren’t so new but a bit tougher to use.

Posted by William Vaughn at 05:24 PM | | Comments (0)

Orcas Preview and Elephants in the Room

I spent the last couple of days in the Visual Basic.NET SDR up on the Microsoft campus. It was an enlightening experience and while much of what we we were told is NDA stuff, I learned quite a bit about Microsoft’s approach to making VB.NET better. They are working hard to bring as many VB6 developers over to .NET as possible. Hopefully, this means VB.NET but I don’t think it makes a snit of difference whether they use VB.NET or C#. Sure, I think VB.NET has (and will continue to have) a lot more appeal to VB6 developers (and their managers) especially having seen some of the new features being planned for the “Orcas” release. However, if developers pay the price to transition to .NET, most of their battles will be won.

The Rest of the Story...

No, I won’t steal Microsoft’s thunder as they want to make a number of Orcas announcements at TechEd to try to sell them to the developer community. Just a word of advice here--based on past experience, the Microsoft marketing team and evangilists have been known to go over the top in their enthusiasm of each upcoming release.

As to VB6 conversion support, it’s clear that the VB.NET team takes this challenge seriously. They have come to the conclusion (based on reports from the trenches) that COM interop makes a lot of sense for those transitioning large VB6 applications or app suites. They suggested that more emphasis should be made on using VB6 to interact with .NET forms and they plan to help developers do that. I think it’s safe to say that there were a number of very cool new features that VB6 folks have been asking to see for a long time incorporated in the plans—these are bound to make a number of VB6 customers more inclined to convert.

I was disappointed to hear that more progress had not been made on the documentation issues. Last year’s SDR was laden with feedback about the search engine (which still has not changed), the “un-topics” (topics with no meaningful content) and the screwed up help links. While I’m sure the doc folks are working hard on this, I don’t see much progress here. All of the gurus and pundits brought in for the SDR agreed that Google was used far more widely than the sad little search engine used by Visual Studio help. Unfortunately, when one hits Google, we often find content from every Visual Basic release since Leif Erikson discovered the east coast of America (and doubtless found native Americans standing there waving back). But I digress. I plan to get back up on campus and help the doc team get back on track if I can. IMHO, they have a lot of work to do so they need to build up a lot of steam to get over the mountain of topics that need help (so to speak). I think they should start by focusing more on the basic topics like the ReportViewer control, but others think that these are adequately covered by third-party authors (like me). I’ve read a lot of the third-party content and found it’s okay, but still lacks much of the depth that serious developers need. Others at the SDR felt that Microsoft should focus just on topics that don’t show up in Google… Perhaps that’s right, but I also think that it would help if Microsoft knew what topics were most requested and make these the top priority. As to the search engine, we told them to buy Google a long time ago. It’s clear (based on the feedback they got on the MSN search engine’s appeal (or lack of it) in the developer community that they need another viable solution. It seems to me that Google will let applications use their search engine from within an application… humm.

One subject that kept coming up as we were shown one new data access feature after another, was business rules. Since the dawn of the first data access interface, little accommodation has been made to deal with the problems associated with data integrity. Sure the new strongly type DataSet and TableAdapter classes include specific types for columns, but no support what-so-ever for making sure that value is in the right range, has the right mask pattern or set to the current default value. Much of the code we write in forms-over-data applications is to implement data validation—I (and many of the other SDR attendees) feel that this job should be easier and automated. For example, if there was a standard way to set the min, max, default, acceptable values (like 10, 20, 25), length, mask, prompt, currency type and a bevy of other attributes, we could write business-rule validation routines far more easily. As it is, if these rules are hard-coded, they have to be recompiled and redeployed with each change. IMHO, Microsoft would be better served (and more respected) if they spent (far) more time on this missing functionality that virtually every application could use before creating a revolutionary new objects approach to data access. Unfortunately, this elephant in the room was quickly covered with a big tarp—and Microsoft went on with the sales pitch for the new Entity Data Model architecture.

Posted by William Vaughn at 12:39 PM | | Comments (1)

May 11, 2006

re: Recommending @@Identity? I Woudn't.

Not without a careful loook at the code... It does sound like @@Identity is being referenced (it should not be).

Posted by William Vaughn at 12:45 PM | | Comments (0)

re: Recommending @@Identity? I Woudn't.

We are using SCOPE_IDENTITY and we are still getting these unexpected results. The scenario is as follows: Insert into Table A (which has an identity column) which fires a trigger adding a row to Table B which has an identity column. On subsequent inserts or updates the identity from Table B is used instead of the identity from Table A. This is happening in an application written in VB 6 using SQL Server 2000. Any suggestions?

Posted by William Vaughn at 09:19 AM | | Comments (0)

May 08, 2006

re: WARNING: Don't Try to Fix Your Daughter's Laptop after 8PM

Only low-level formats (rewriting the sectors) will completely wipe things out, as far as I know. Any other type of reformat is recoverable.

Posted by William Vaughn at 12:08 PM | | Comments (0)

May 07, 2006

re: The Writing is Done--Now the Hard Part

Neat. I'm looking forward to your book.

I read your last version that you handed to me at the MVP summit last year. You know so much, that it is impossible to stuff it in 800 pages. Hopefully I'll be as smart as you one day.

Posted by William Vaughn at 11:55 PM | | Comments (0)

WARNING: Don't Try to Fix Your Daughter's Laptop after 8PM

I was working on my daughter's (George's) laptop the other night and made two mistakes. First, I was working on hardware after 8PM--this is a mortal sin as far as I am concerned. Next, I thought her drive had two partitions. It had one. I formatted both partitions. I shouldn't have. Thankfully, I discovered a program “Recover My Files” which after a considerable length of time (over 24 hours) was able to “unformat” the NTFS partitions. I, frankly was very grateful but sorta shocked as I assumed (incorrectly) that dropping a partition, creating a new partition and doing a full format would wipe the data. It does not. Each and every file on the drive was recovered (at least it looks like they were).

(Update: No, the Recover My Files program did not actually recover the data--at least it has not done so at this point. While it does see the files, when saved the files are corrupted.)

Another little issue: IBM (whose service is highly rated by Consumer Reports) sent George a “Used but Serviceable” replacement hard drive. It lasted about 18 hours before it failed. They're sending another. Perhaps it will last long enough to build a backup... This assumes that I won't be so tired that I delete the backup before getting a chance to install it. I wonder what data the replacement drive contains? Hummm, should I peek?

bv

 

Posted by William Vaughn at 08:58 PM | | Comments (1)

The Writing is Done--Now the Hard Part

I've made a decision to stop adding new content to my new book “Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)”. I finally came to the realization that I can't keep up with Microsoft and their constant churn. No, this does not mean I won't be publishing the book--hardly. It means that I'm going to trim back to 800 or so pages and publish the new stuff as EBook. This more closely follows the pattern used by Microsoft. That is, ship a product and then ship updates for the next couple of years (before it's made obsolete). ;)

To this end, the book's manuscript is now in the hands of the editors and reviewers. I hope to get it on the shelves this summer. The new support web site is up www.hitchhikerguides.net but not really ready for comments quite yet. Don't be surprised if the colors and layout change from minute to minute--I'm still tuning.

Let me end on this final note. This book would have been a lot harder without the help of Peter Blackburn. While he did not write a lot of content this time, he has been there during the entire process (over two years) keeping me on track, keeping my systems functional and keeping my eye on the ball when it comes to security issues. Thanks bud.

 

Posted by William Vaughn at 08:49 PM | | Comments (1)