RSS

Tag Archives: sql server

3 SQL Server Instances You May Not Know You Have

There are 3 SQL Server database instance in most local machines. These are SQL Server Express instances that mainly useful for developers so they don’t have to install any SQL Server instance to get a project up and running. They are:

  • <machine_name>\SQLEXPRESS or .\SQLEXPRESS
    For example: “stack247\SQLEXPRESS”, or simply “.\SQLEXPRESS”.
    This is older version of SQL Server Express, 2008 to be precise. It’s also installed when you install Visual Studio 2010.
  • (LocalDB)\v11.0 or (LocalDB)\v11.1
    Comes with SQL Server 2012 or Visual Studio 2012. This is a newer version of SQL Server Express, or as the folks at SQL Express team call it, an improved SQL Express. The official name is SQL Express LocalDB
  • (LocalDB)\Projects
    Mainly used as default database by SQL Data Tools to host sandbox databases for your Database projects. Just like SQL Express LocalDB, it also comes with SQL Server 2012 or Visual Studio 2012.
Advertisements
 
Leave a comment

Posted by on December 18, 2014 in General

 

Tags: ,

Do Not Use Database Trigger

Or rather, use database trigger carefully.

Trigger is convenient and easy to write, but wrong implementation of database trigger can cause serious performance issue.

Basic rule is to avoid database trigger at all cost, find alternative way to achieve same result. Below is a video from Pluralsight showing usage of a database trigger that performs poorly. The slow performance caused by overhead that comes with database trigger, in this case the operation reads more table that necessary. When alternative approach is used (setting default constraint on a column), the difference is significant.

Try it for yourself.

-- Create our demo
CREATE TABLE [dbo].[charge_demo](
    [charge_no] [dbo].[numeric_id] NOT NULL PRIMARY KEY CLUSTERED,
    [charge_no] [dbo].[numeric_id] NOT NULL,
    [provider_no] [dbo].[numeric_id] NOT NULL,
    [category_no] [dbo].[numeric_id] NOT NULL,
    [charge_dt] [datetime] NOT NULL,
    [charge_amt] [money] NOT NULL,
    [statement_no] [dbo].[numeric_id] NOT NULL,
    [charge_code] [dbo].[status_code] NOT NULL,
    [insert_dt] [datetime] NULL;
GO

-- Create our trigger
CREATE TRIGGER [trg_i_charge_demo]
    ON [dbo].[charge_demo]
    AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    
    UPDATE [dbo].[charge_demo]
    SET [insert_dt] = GETDATE()
    FROM [dbo].[charge_demo] AS [c]
    INNERT JOIN inserted AS [i] ON
        [c].[charge_no] = [i].[charge_no];
END
GO

Source: Hidden Side Effect of Trigger

 
Leave a comment

Posted by on April 19, 2013 in General

 

Tags: , ,

Creating ASP.NET Membership in SQL (Web Forms and MVC)

ASP.NET Membership is Microsoft approach to provide security, authentication and authorization in that regards, for ASP.NET Web Forms and ASP.NET MVC. Both Web Forms and MVC can utilize this feature through Forms Authentication approach.

There are several ways to create Membership database in SQL Server.

SQL Server Registration Tool (Aspnet_regsql.exe) – Wizard

%WINDIR%\Microsoft.Net\Framework\<.net version>\aspnet_regsql.exe

Running this tool will guide you to installing ASP.NET Membership in SQL Server.
See the ASP.NET Membership Tutorial on “Installing the Application Services” section for more instruction.

SQL Server Registration Tool (Aspnet_regsql.exe) – Advanced Options

Beside the wizard guide, you can also use its command prompt with many available options.
See MSDN site for the parameters.

SqlServices.Install Method

To programatically install ASP.NET Membership, use SqlServices.Install method.

SQL Scripts Manual Approach (for Deployment Plan)

Generate sql script with SQL Server Registration Tool (Aspnet_reqsql.exe) to include them in a database project as part of deployment strategy.

%WINDIR%\Microsoft.Net\Framework\<.net version>\aspnet_regsql.exe -sqlexportonly <filename.sql> -A all

SQL Scripts Manual Approach (for Deployment Plan) – Individual Scripts

In the following folder, you will find many .sql script files to install / uninstall ASP.NET Membership. Each service (or feature – Membership, Roles, Profile, Web Parts Personalization, Web Events) is separated by files. This also can be included in a database project as part of deployment strategy.

%WINDIR%\Microsoft.Net\Framework\<.net version>\

ASP.NET Membership Schema Diagram

 
1 Comment

Posted by on July 13, 2012 in General

 

Tags: , , , , , ,

Audit Trail Performance Comparison

Below are audit trail performance testing in different technologies / approaches.

  • Baseline: Standard DML operations with no audit trail.
  • Change Data Capture (CDC): DML operations with CDC to capture audit trail in system tables.
  • Trigger: DML operations with trigger to record audit trail in different table.
  • Entity Framework 1: DML operations with audit trail recording in Entity Framework’s SaveChanges() method by accessing EF’s OriginalValues object for tracking data changes.
  • Entity Framework 2: DML operations with audit trail recording in Entity Framework’s SaveChanges() method by accessing EF’s DbSet object for tracking data changes.
Insert Update Delete All
Baseline 1717.05 2353.95 2650.5 6761.95
Change Data Capture (CDC) 1632 2399 2649 6689
Trigger 1649.6 2347.25 2708.3 6723.65
Entity Framework 1 3517.4 4214.6 3350.3 11106.15
Entity Framework 2 3386.35 5678 4916.95 13993.9
Test results are in millisecond. Operations are performed with 500 records.

 
Leave a comment

Posted by on May 2, 2012 in General

 

Tags: , ,

Database Audit with Change Data Capture (CDC) in SQL Server 2012

There are two feature in SQL Server 2008 (and above) to track data changes, Change Tracking (CT) and Change Data Capture (CDC).

Tracked Data: DML changes, DML type, columns, historical data (only in CDC).

Difference: CT doesn’t know the old and new value of the data being changed.

DML Operations:

  • 1 = delete
  • 2 = insert
  • 3 = update (before image)
  • 4 = update (after image)

Objects: The following are objects (system tables, stored procedures – sp, or functions – fn) used in CDC (Change Data Capture)

Object Description
sys.sp_cdc_enable_db Stored procedure to disable CDC in a specified database.
sys.sp_cdc_disable_db Stored procedure to disable CDC in a specified database.
sys.sp_cdc_enable_table Stored procedure to enable CDC in a specified table.
sys.sp_cdc_disable_table Stored procedure to disable CDC in a specified table.
cdc.fn_cdc_get_all_changes_<capture_instance> Function to return all changes that occurred for the specified interval.
cdc.fn_cdc_get_net_changes_<capture_instance> Function to return one change per modified source table row.
sys.fn_cdc_get_max_lsn Function to return the maximum log sequence number (LSN) from the start_lsn column in the cdc.lsn_time_mapping system table.
sys.fn_cdc_get_min_lsn Function to returns the start_lsn column value for the specified capture instance from the cdc.change_tables system table.
cdc.lsn_time_mapping System table to store one row for each transaction having rows in a change table. This table contains commit time for each data changes.
cdc.change_tables System table to store one row for each change table in the database.
sys.dm_tran_database_transactions System table to store information about transactions at the database level.

Source: MSDN

 
1 Comment

Posted by on April 23, 2012 in General

 

Tags:

 
%d bloggers like this: