Index Reorganization and Rebuilding for Success

On January 20, I presented an end-to-end process for SQL Server Index rebuilding/reorganization to NUFW (.Net Users of Fort Wayne). For people that manage small databases with less than a few million records per table, there are few risks and considerations. For those with large or VLDB databases such as ERP/MRP databases with hundreds of millions of records and Terabyte data file sizes, the process becomes more critical and time consuming. That is the situation for which this process was designed.

Index Reorganization and Rebuilding for Success from Dean Willson

SQL Server Daily Health Check page and Powershell script update

I recently created a new page for my SQL Server Daily Health Check Powershell script download and documentation. I updated the script to version v0.8.2 which refactored the original script into a function to eliminate repeating code and make it easier to customize with additional checks. I will add additional documentation and instructions as I get time.

Automating SQL Server Daily Health Checks with PowerShell

Last night I presented this PowerPoint presentation and Powershell script Demo to fwPASS members.

Automating sql server daily health checks from Dean Willson

Introduction to Powershell presentation

Powershell is a powerful scripting shell to do any number of administrative tasks. Below is the introductory level presentation I gave to fwPASS, the Fort Wayne chapter of the Professional Association for SQL Server on 10/23/2012.

Intro to Powershell from Dean Willson

Uninstalling a Failed SQL Server 2008 Installation

Last night at a SQL Server User Group meeting, one of the attendees was describing the trouble he was having uninstalling a failed SQL Server installation. There was some kind of problem that caused it to fail on the database engine installation. That sounded exactly like a problem I had installing SQL Server 2008R2 as a named instance alongside SQL Server 2005 just a few weeks before.

My installation was failing because I was attempting to install SQL Server 2008 (not R2) from an actual Microsoft DVD (most of the time I am installing from an MSDN iso download). After much of the installation was complete, a dialog popped up prompted for media containing a path to a SQL Server 2008 R2 executable. I wasn’t trying to install R2, so I’m not sure why the prompt. I ignored the prompt and continued the installation which failed (no surprise there). After the error, I decided I would just uninstall the non-R2 instance and install SQL Server 2008 R2, but the uninstall wizard would not work.

For posterity sake, the blog post that I used to uninstall the failed installation was Fun with software : uninstalling SQL Server 2008 R2 Evaluation Edition by Aaron Bertrand. The article is about uninstalling an eval edition, but it worked for me on an RTM or SP1 copy.

 

photo credit: peterhess / CC BY 2.0

Error 2337 Installing SQL Server 2008R2 in Virtual PC 2007

Issue

Last year I was preparing for an fwPASS presentation on Report Viewer 3.0 by installing SQL Server 2008R2 in a virtual machine using Virtual PC 2007. I kept getting an error 2337 indicating that the disk may have been corrupt or damaged. I was installing it from a .iso image I downloaded from my MSDN Ultimate subscription. I used the Virtual PC “capture iso image” functionality to mount the iso as a disk. I downloaded another copy of the iso image and repeated the process with the same results. Due to time constraints, I reverted back to SQL Server 2005 and Report Viewer 2.0 for that presentation, highlighting features new to 3.0 using screenshots.

Physical and Hyper-V Success

Since then I have successfully installed SQL Server 2008R2 successfully multiple times on bare metal physical servers and in VM’s using Hyper-V Server 2008.

VPC Still an Issue

Fast forward to this week. I was setting up a development sandbox in a VM. Again I was installing SS2008R2 by mounting the image in VPC 2007 and was faced with the same Error 2337. Seriously? This issue hasn’t been resolved yet? A quick search on the error resulted in this helpful post Getting error 2337 installing SQL Server on a VPC on the SQL Skills blog.

Solution

I didn’t have any blank DVD’s on hand for burning the image, so I decided I would use MagicDisc to mount the iso file on my host machine, instead of using VPC’s capture iso functionality. That solved the problem.

SQL Server Compatibility Level Testing

I stumbled on interesting version compatibility issues while working with a database in SQL Server 2008R2. The database was originally developed in SQL Server 2000. It was upgraded to MSSQL 2008R2 as a part of a virtualization initiative, however, the Compatibility Level was still set to 80 (MSSQL 2000).

After seeing columns of type VARCHAR(MAX), it got me wondering about compatibility issues since there was no VARCHAR(MAX) in 2000.

Anomaly #1

Doing some research I found this post with a script for Testing all procs before upgrading to Compatibility Level 90. I ran it on the database (while still set to Compatibility Level 80) and it reported a stored procedure that had a syntax error that was not caught by the syntax checker in MSSQL 2000, 2005, or 2008R2. The SQL is below:

SELECT     ChangeLogID

    , CourseID

    , RecordID

    , RecordTable

    , ChangeType

    , ModifiedDateTime

    , ModifiedByEmpNo

    , CASE RecordTable 

        WHEN 'Course' THEN

        ( SELECT CourseName 

          FROM Course 

          WHERE CourseID = cl.CourseID)

        WHEN 'Lesson' THEN

        ( SELECT LessonName 

          FROM Lesson 

          WHERE LessonID = cl.LessonID)

         WHEN 'Content' THEN

        ( SELECT ContentName 

          FROM Content 

          WHERE ContentID = cl.ContentID)

        ELSE "None"

    END AS RecordName

FROM         ChangeLog cl

WHERE     (CourseID = @CourseID)

The error message was: Invalid column name 'None'. Note the quotation marks around “None” after the ELSE. Strangely, the stored procedure executes without error. It only detects the syntax error on CREATE, not when I ALTER the stored procedure.

Anomaly #2

Using the same database described above, the compatibility error detection script I used (while still having a Compatibility Level of 80) did not detect the error:

Msg 8127, Level 16, State 1, Procedure GetNextLessonForLessonNo, Line 49
Column "Lesson.LessonNo" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

The SQL generating this error is:

SELECT     TOP 1 MIN(L.LessonNo) AS LessonNo

    , L.LessonID

    , L.CourseID

    , L.LessonName

    , L.LessonDesc

    , COUNT(Content.ContentID) AS NContentPages

FROM     Course C 

         INNER JOIN Lesson L 

            ON C.CourseID = L.CourseID

         LEFT OUTER JOIN Content 

            ON L.LessonID = Content.LessonID

WHERE     (L.LessonNo > @LessonNo 

       AND L.CourseID = @CourseID)

GROUP BY L.LessonID

    , L.CourseID

    , L.LessonName

    , L.LessonDesc

ORDER BY L.LessonNo

This error is not detected in SSMS (in a query window, or by running the detection script) until the Database Compatibility Level is changed from 80 (MSSQL 2000) to 100 (MSSQL 2008). The issue can be resolved in SQL Server 2008 by adding L.LessonNo to the GROUP BY clause.

photo credit: somegeekintn / CC BY 2.0 

EF “Code-First” Table Mappings for Existing Database

If you are using the MvcScaffolding package to scaffold the tables from an existing database and it throws an exception on the following line of the index.cshtml page. (The exception would show up in a different location on other data access pages).

@foreach (var item in Model) {

If the exception is of type System.Data.EntityCommandExecutionException where the inner exception is something to the effect of “Invalid object name ‘dbo.Ranks” where the actual database table name is the singular form like dbo.Rank, you probably need to explicitly define the table schema mappings in the OnModelCreating event of the context class because the existing database table names don’t match the default Entity Framework Convention over Configuration settings.

namespace LPM.Models

{

    public class LPMContext : DbContext

    {

        public DbSet<LPM.Status> Status { get; set; }

        public DbSet<LPM.Rank> Ranks { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)

        {

            modelBuilder.Entity<Rank>().ToTable("Rank");

        }

    }

}

If you use SQL Profiler to trace the T-SQL statement issued by the application, you can see it was attempting to retrieve data from the [dbo].[Ranks] table which doesn’t exist. In my database it is [dbo].[Rank].

SELECT
[Extent1].[ID] AS [ID],
[Extent1].[RankNo] AS [RankNo],
[Extent1].[RankDescription] AS [RankDescription]
FROM [dbo].[Ranks] AS [Extent1]

This stumped me initially because the first table I used as a test was the “Status” table which by convention has the same name in the singular and plural form. So no exceptions where thrown and I could list, insert, update, and delete the records. After I updated the model from the database and added the Rank table, exceptions started flying until I added the modelBuilder.Entity<Rank>().ToTable(“Rank”) line in the OnModelCreating event.

Scott Guthrie’s Entity Framework 4 “Code-First”: Custom Database Schema Mapping has additional examples with more complex mapping scenarios.

photo credit: aaron13251 / CC BY 2.0

MvcScaffolding uses SQL Express by Default

The fact that the MvcScaffolding package uses SQL Express by default can be both good and deceiving at the same time.

Using an Existing Database (not Code-First)

I used the package on a MVC3 project to scaffold the repository models and controllers for an existing SQL Server Standard database (not SQL Express) that had an existing Ado.Net Entity Framework model. The EF connection string obviously pointed to the existing database. However when running the application, the Index pages didn’t list any of the existing data from my database.

If this looks like the same problem you are having, feel free to skip to the bottom for the solution (convention over configuration).

Where’s the data?

I could add records, but they didn’t show up in the existing database. There was no Express database .mdf file in the application’s App_Data folder. There were no additional  connection strings (other than the EF connection to the existing database) in the web.config files or any of the dbcontext class files. I profiled the existing database with SQL Profiler and the existing database was completely untouched. I even added a <remove name=”LPMEntities” /> line before the EF connection string to make sure any default connection from the server root or machine.config was taken out of the inheritance tree (similar to what you would do for the aspnetdb membership database if you are using a full SQL Server instance).

The new data was being stored somewhere, but where? In a newly created SQL Express database.

How do I See the Data?

Open your SQL Server Management Studio (SSMS) or the version of SSMS for SQL Express. In the Object Explorer window, click Connect. Use .\sqlexpress as the server to connect to using Windows Authentication and voila the new mysterious “hidden” database.

I hope this helps to clarify for other people attempting to use the MvcScaffoling in a Database-First scenario.

The Solution – Understand the Convention over Configuration

It took me a while to find a small subtle, yet crucial, detail in Scott Guthrie’s Using EF “Code First” with an Existing Database.

The following note is stated at the end of Step 5 – Configuring our Database Connection String:

EF “code first” uses a convention where context classes by default look for a connection-string that has the same name as the context class.  Because our context class is called “Northwind” it by default looks for a “Northwind” connection-string to use.  Above our Northwind connection-string is configured to use a local SQL Express database.  You can alternatively point it at a remote SQL Server.

In my case the Entity Framework connection string in the web.config was named “LPMEntities”. The class implementing the DbContext is named LPMContext as shown below.

namespace LPM.Models

{

    public class LPMContext : DbContext

    {

        public DbSet<LPM.Type> Types { get; set; }

        additional stuff here...

    }

}

Since LPMEntities is not the context class name, the EF generates a local SQL Express database.

I just added a connection string to the web.config with a name of LPMContext (same name as the context class) and my MVC forms are populated from the existing database.

Victory!

photo credit: Si1very / CC BY-SA 2.0

Database Primary Key Naming Conventions

This post specifically deals with the two naming conventions of a primary key for example “Id” or “<tablename>Id".

Using the convention including the tablename prepended to “Id” makes creating and reading SQL query joins more intuitively readable as in:

SELECT a.AuthorId

    , a.AuthorName

    , b.BookId

    , b.BookName 

FROM Author a

   INNER JOIN Book b 

   ON b.AuthorId = a.AuthorId

Versus the alternative using a standard key name, such as “Id" for all tables, as in the following:

SELECT a.Id AS AuthorId

    , a.AuthorName

    , b.Id AS BookId

    , b.BookName 

FROM Author a

   INNER JOIN Book b 

   ON b.AuthorId = a.Id

However it adds additional abstraction/mapping complexity when using a generic Repository pattern* that uses a standard primary key property name for the Repository base classes and interfaces.

*The design is possibly more correctly labeled as a DAO pattern depending on the specific implementation.

photo courtesy: amboo who? / CC BY-SA 2.0

Creating a Windows Phone 7 app using WCF, and SQL Azure

Last night I gave a presentation with demos for fwPASS creating a Windows 7 app displaying a list of gymnasts from a SQL Azure database using WCF (Windows Communication Foundation). The WCF service used the Entity Framework and the .Net Membership provider.

Windows phone 7, WCF, and SQL Azure

View more presentations from Dean Willson

The application was surprisingly easy to create despite the fact that were a lot of moving pieces with integration between all the different components.

The presentation covered the following tools:

• Windows Phone Marketplace App Hub 
• Visual Studio IDE
• SQL Server Management Studio (SSMS)
• Windows Azure Platform Management Portal
• SQL Azure Migration Wizard
• Windows Phone 7 Emulator

 

All that in 70 minutes. Yes, I talk fast. This was a first time presentation. Below is the demo script I used:

Marketplace

1. Preview App Hub http://create.msdn.com

· Show app details text, iconography

Database

1. SSMS – show local ChalkChimp database

· Show script for creating additional users?

2. http://windows.azure.com SQL management interface

· No query designer

3. SQL Azure Migration tool

· Scripts out db

· Shows unsupported features

· deploys

4. Azure limitations in SSMS

· No query designer

· Admin account (other users don’t have access to the master database)

WCF

1. Create new project WCF Application

· Highlight Framework version 3.5

· Change binding to basicHTTPBinding

2. New Item à ADO.Net Entity

· Azure connection

3. Switch to finished project

4. Modify Iservice.cs, modify Service.cs implementation

5. Show .Net membership bits in web.config

6. View in browser (copy url)

WP7 client

1. Open Visual Studio (another)

2. New Project à Windows Phone 7 application

3. New item à Phone portrait page

4. Add Service reference

· Show service methods

· Advanced show generate async methods

5. Switch to finished project

· Show xaml bindings

· Show async methods and completed event handlers

· switch device to emulator

6. Run it

Optional

· Show property pages and iconography

 

Technorati Tags: Windows Phone 7,WP7,WCF,Silverlight,SQL Azure

Do You Have Control of Your Database?

I prepared the following talking points to facilitate a round table discussion on Database Source Control titled “Do you have [source] control of your database” with a local chapter of Professional Association for SQL Server.

Most software developers use some form of source control for their software code, but it seems less common to take the same precautions with their databases.

Database Source Control

View more presentations from Dean Willson

SQL Saturday #67 Chicago

Most of the sessions I attended involved improving performance. Whether it was proactive (design for performance) or reactive (troubleshooting), most sessions included some coverage of indexes. Surprisingly there very little duplication of content by the different speakers. Because of the quality and relevance of the all sessions offered, it was difficult to decide which sessions to attend. Below is a brief recap of the ones I chose.

Sessions

Performance Tuning Made Easy with Thomas LaRock

Being a certified Six Sigma Black Belt, I appreciated the way Tom (blog|twitter) described a simple systematic process for performance tuning based on the Six Sigma DMAIC (Design, Measure, Analyze, Improve, Control) approach.

The Optimizer, Statistics, and Indexes with Grant Fritchey

Grant (blog|twitter) gave a great primer to the internals of the Relational Engine, the components of which have really cool names like Query Parser, Algebrizer, and Optimizer. He also spent more time discussing the Statistics histogram, which appeals to my six sigma senses, than I generally see in an hour long presentation.

WIT (Women in Technology) Panel: Energizing the Next Generation

This session seemed to have a balanced number of men and women in attendance. It was nice to hear the perspective of multiple women with different backgrounds sharing their experiences in the field. I’m really happy that most of their experiences sounded positive even in the face of adversity.

Refactoring SQL with Jeremiah Peschka

Jeremiah (blog|twitter) really highlighted the importance of using consistent naming conventions and common alias names. He touched on SQL unit testing and some open source test frameworks. He also showed refactoring examples such as, re-writing where clauses to move functions from the left side of the operator to the right side for performance gains.

Database Design Contentious Issues with Karen Lopez

This was one of my favorite sessions. It was interactive as the participants had to walk to the front of the room and anonymously vote (via post-it note) on a scale of 1 to 5 on our opinion/view of two issues which people often passionately argue about, such as, surrogate keys vs. natural keys. People that admitted to voting on the extremes (one or five) were asked to explain why they voted the way they did and open the topic for group discussion. Karen liberally made effective use of Canadian SWAG (like ketchup flavored Pringles) to persuade the edge-case voters to plead their case in the face of possible peer criticism. Karen (blog|twitter) expertly moderated the discussion and provided great pro/con examples on both sides even where it conflicted with her own views. It was both fun and educational.

Index Internals for Mere Mortals with Michelle Ufford

At a lightning quick pace, Michelle (blog|twitter) brought a great deal of insight to index internals. She showed actual representations of index pages and pointed out key elements of it, including the “uniqueifier”. She covered a great deal of information in the short one hour, but it was very clear, understandable, and educational.

Lunch

The lunch was provided by Meaty Balls. It was plenty of tasty meat and mushrooms for the price.

Check-in

The organizers offered a “Speed Pass” option where I printed my name tag, lunch ticket, and raffle tickets off in advance which meant I could sleep in a little before heading to Chicago.

Venue

The conference was hosted at the DeVry University Addison Campus which had great classroom spaces. There were ample power outlets available on the table tops for each attendee to plug in, if desired. I wish I had remembered to bring the AC adapter for my GPS for the trip home since my car lighter adapter stopped working before the trip.

Networking

I was within a SWAG keychain throw of several SQL Celebrities like Kevin Kline (blog | twitter) and Brent Ozar (blog|twitter). Brent was sitting behind me in three out of five sessions I attended - and no, I’m not a SQL Stalker.

I finally got to meet Wendy Pastrick face-to-face after several email and twitter conversations over the last year.

Credit-worthy

I’ve been running the fwPASS User Group for a few years and to do just an average job takes a surprising amount of time and effort. See my case study on continuous improvement in a professional association for more information. The organizers did an exceptional job that deserves more thanks than I can provide. I’m not sure if this is inclusive of all the organizers (which I copied from Doug Lane’s blog), but THANK YOU:

Wendy Pastrick (blog|twitter)
Ted Kruger (blog|twitter)
Norman Kelm (blog|twitter)
Jes Borland (blog|twitter)
Bob Pusateri (blog|twitter)
Aaron Lowe (blog|twitter)
Bill Lescher (twitter)
Rich Rousseau (twitter)

Introduction to MSSQL Management Data Warehouse (MDW) presentation

Last night I presented an intro to SQL Server 2008’s MDW to the fwPASS Professional Association. The subject is primarily of interest to DBA’s rather than Developers since it is best leveraged by monitoring and managing an environment of multiple SQL Servers.

Introduction to SQL Server 2008 Management Data Warehouse (MDW)

View more presentations from Dean Willson.

SQL Server Management Data Warehouse

The Management Data Warehouse introduced in SQL Server 2008 is a comprehensive performance monitoring and reporting system. It is a handy way to consolidate performance monitoring and data for all your servers on a single system.

A couple of minor gotcha’s to consider when setting up the data collector. The setup is so easy it is tempting to do it on auto-pilot. Don’t.

1. This is an obvious one, but I’m pretty sure it happens more than you would expect. Make sure to create a blank database on your monitoring server where you will store the aggregated data collection sets. Don’t accidentally pick one of your production databases or it will add all the tables, views, stored procedures, functions, types, schemas, and other logging objects into that database (there are a lot of them). It will also mark that database as a data collection warehouse which will show up in the database list when you configure data collection on your other servers.
2. Make sure to create the temp folder that will be used to cache the performance data until the periodic upload to the Management Data Warehouse. If the folder is not created, the upload jobs will fail.

photo credit: Nick Saltmarsh / CC BY 2.0

ReportViewer Woes with Tight Integration between Visual Studio and SQL Server and No Backward Compatibility

Lately I find myself creating some quick client reports (ProcessingMode=local) using the Visual Studio 2010 ReportViewer control and the Reporting Services Tablix control. The issue is that my customer is on SQL Server 2000, not 2008 as is officially supported by Visual Studio 2010 and the rdlc Reporting Build Provider. How do I know?

Mainly because of the following compile error:

“The report definition is not valid.  Details: The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' which cannot be upgraded.”   

The lack of backward compatibility was not immediately obvious, but I still wanted the ability to use the newer Tablix functionality in the VS 2010 IDE and have the out-of-the-box export functionality of the Reporting Services client reports. I’m not a fan of workarounds as I have written about before, but occasionally under special circumstances with the pros and cons heavily considered, I entertain the idea. In this case, a SQL 2008 upgrade is on the schedule in the next few months.

Workaround for Visual Studio 2010

Visual Studio 2010 cannot use any of the SQL Datasource Wizards or any other database related wizards with a pre-2005 database, which is disappointing for anyone developing “big corporate” applications as they are often late adopters. To work around this limitation in development, I backed up my SQL Server 2000 database and restored it as a SQL Server 2005 database so I could use the IDE for the Create Report wizard. Obviously, prior to deploying to production SQL 2000 servers, the database DDL and DML changes need to be scripted back out (in SQL 80 compatibility mode) and tested against SQL 2000 instances. Please don’t skip this step!

Workaround for SQL Server 2000 RDLC format

This is my least favorite part of the work around because it can be easy to forget to undue before deployment.

The application I am working with is a website project, not a web application. The compile error occurs when I try to “Build” the project from the IDE “Build” menu. If instead, I just navigate to the web page the report is hosted in (hosting in IIS, not the IDE integrated web server), the page and its report render correctly with no errors. In order to use the IDE’s build functionality, I need to “exclude” the webpage and rdlc file it is using while working on other pages of the project.

To exclude a file from the project, right click the filename from the Solution Explorer window and click “Exclude from Project”. Excluding a file in VS 2010 renames the file to [filename].[ext].exclude so you may get a Visual Source Safe or Team Foundation prompt if your files are under source control (recommended).

When I am ready to deploy or test the pages with ReportViewer controls, it is necessary to un-“exclude” the report aspx pages and .rdlc report definitions.

photo credit: Andy Welsh / CC BY 2.0

Five Reasons to Use a Staging Server

If you are interested in avoiding common and difficult to reverse issues when deploying new or updated software to a production environment, use a staging server – at a minimum. Your staging server should be configured as closely as possible to the production server. With virtualization, the process of cloning a server becomes a relatively trivial task.

A staging server provides a safe vehicle to “discover” and prevent commonly overlooked deployment issues:

1. missing assembly references or resources. This is a frequent occurrence if you use third party tools like Infragistics, Telerik, or another. When you install the development tools, they often add the assemblies to the GAC on your development box and scripts and images to “special” virtual directories. If you blindly deploy an application to production without making sure you have the assemblies either in the GAC or referenced properly in your application, it can frequently lead to run time errors and the yellow screen of death. Even items you might think are part of the .Net Framework that are pre-loaded in your Visual Studio toolbox, such as the ReportViewer control require the installation of a ReportViewer Redistributable executable.
2. dependencies on physical hardware. Depending on how the application is configured, your application may unwittingly be relying on a writeable F drive where on the server the F drive may not exist or may be a CD-ROM drive. Or require a 64 bit processor on a 32 bit server. It is better to catch these issues before deploying to production.
3. require elevated file system permissions. If your application is writing XML files or storing media, the application upload path will require Write permissions for the accessing account. Permissions requirements should be part of the installation/upgrade process, not left to post installation troubleshooting.
4. require least privileges on database permissions. You may be using the sa account (not recommended)on your development box, but your production DBA and/or company policies won’t appreciate that in production. This is an opportunity to make sure your application is running under an account or group with the least privileges necessary to run the application and make sure if your installation process is dropping/re-adding stored procedures or tables, you are also adding the appropriate grant select, insert, update, delete, execute, bulk insert, or truncate permissions to avoid unexpected SQL errors.
5. require configuration changes to interface with other services, such as IIS version differences, SMTP, or Message Queues. On your development box, for simplicity, you might be using a local IIS SMTP service, or file system implementation to test emailing functionality, but if the production environment only allows mail to relay through a Microsoft Exchange endpoint, you have some additional configuration to do. Making sure all the appropriate accounts are understood and configured properly often takes coordination with other IT specialists. They’ll appreciate it if you coordinate it at their convenience on a staging server instead of elevating the issue to a production emergency.

This isn’t intended to be an all inclusive list, but hopefully if you are not using one today, it will inspire you to consider using one in the future. Prevention is the best medicine for software implementation.

The staging process provides an opportunity to perfect the installation process for a problem free installation. Additionally, having the installation process consolidated to a single series of installation and configurations steps in a single package with all the necessary files and scripts can simplify recreating software versions for disaster recovery purposes or for replicating to additional installation deployments, such as for different customers or at a different site.

Photo credit: Anirudh Koul / CC BY 2.0

Asp.Net Mystery Membership Database

I’ve used the built-in Asp.Net Membership Provider a number of times for its simplicity and straight forward API. There are a few mystical elements that can trip you up, if you don’t understand how the provisioning process works. There are two primary options:

• manual
• auto-magical

Manual

I generally create the membership database before I start working on the user interface (seems logical right?). I do that by running the membership provider wizard by opening a Command Prompt, navigating to the .Net 2.0 framework folder and running the aspnet_regsql.exe command (c:\windows\Microsoft.net\framework\v2.0.50727\aspnet_regsql.exe on a 32bit OS).

That launches the setup wizard where you can choose how you want the database configured in your SQL Server instance:

• use an existing database such as the application database or
• a stand-alone membership database if you want to have multiple applications use the same membership database

The wizard then runs the necessary scripts against the selected database (creating the database if it doesn’t exist) and populates it with the tables, views, and stored procedures that the API uses to manage the membership details.

Auto-magical

In case you hadn’t guessed, this is method enshrouded by mystery (not really, but it can seem like it). If you don’t choose to manually create your membership database (via the wizard), then the default option as defined in the machine.config file is to use a SQL Express database which ends up getting created in the application’s App_Data folder in Visual Studio.

<connectionStrings>
        <add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/>
    </connectionStrings>

Depending on your version of Visual Studio and perhaps the method you use to initiate the creation of this database, it might not be obvious exactly where the database is or how it is configured to point to that database file. The corresponding connection string doesn’t necessarily show up in the web.config file because it is inherited from the one defined in the machine.config.

There are multiple ways you can initiate the creation of the membership database. A couple include:

1. Select “ASP.NET Configuration” from the Visual Studio “Project” menu (screenshot is from VS 2010).
2. Drag a login control onto the design surface and select “Administer Website” from its context menu.

Upon first use of adding a user, role, or other activity a SQL Express database (ASPNETDB.MDF) will be created including the membership provider’s tables, views, and stored procedures used to manage the membership and personalization system.

Mystery

In a quick prototype project, I used the auto-magical process and added a few users. You will notice in the App_Data folder, there doesn’t appear to be an ASPNETDB.MDF, only a TeamStrength.mdf file, so where are my users stored?

Upon closer inspection with Windows Explorer, there actually is a membership database in that folder.

Mystery solved.

You may also be interested in my introduction to implementing ASP.NET Role Based Security presentation or the code samples from the presentation can be found through a link on that blog post.

Photo credit: m_bahareth / CC BY 2.0

Introduction to SSRS Report Builder

I made a short 30 minute presentation to fwPASS yesterday on the topic of Report Builder after Mark Dalman presented an Introduction to BizTalk. The intention was to present on Report Builder 3.0 (SQL Server 2008R2 version), but unfortunately I had SQL Server installation issues that prevented it. I presented using SQL Server 2005 Report Builder instead, but I highlighted some of the new features found in Version 3.0.

The first three slides are just tidbits of fwPASS information followed by the presentation.

Introduction to SSRS Report Builder

View more presentations from DeanWillson.

A side note: Other than slide 8, I created the entire presentation using Microsoft’s PowerPoint WebApp on my Windows Live SkyDrive account. It is a pretty nifty web-based alternative to Google Docs

SSMS Tools Pack – Handy SQL Server Management Studio Utilities

I ran across the SSMSToolsPack while reading the comments from a post on dynamically generating CRUD Stored Procedures. It has some other nice features like saving snapshots of the execution plan to the clipboard.

It can also generate data insert statements from query resultsets, tables, or a database. That can be pretty handy for a number of reasons like:

• populating lookup tables in the initial deploy scripts of a new application or application update where a lookup table is added
• dropping tables and recreating test data for testing installation scripts and application testing

 

photo attribution http://www.flickr.com/photos/jesse_sneed/2383953694/ CC BY-ND 2.0