SQL Server (user) instances and AutoAttach DB

Developers often need to attach databases on demand and then start using them immediately. However before developers can start working with DB's they need to first attach the DB, then create a SQL login and then add that login to the DB users list. with SQL Express this is no longer a requirement.

With SQL Express we can use Auto Attach DB's and User instances.
SQL 2005 Express and Full editions both support autoAttachment of DB's, however User instancing is only supported with SQL Express.

To enable AutoAttachment of DB's use : AttachDbFilename=db.mdf in connection string.
When you use "AttachDbFilename=|DataDirectory|db.mdf" the connection string automatically looks for the DB in the AppData folder of the Web Application.

However there is a limitation that if the user is not a administrator, he would still not be able to AutoAttach the DB. we can solve this by using "User instances".
User instance is another SQL server instance and runs under the credentials of the currently logged in user, thus no need to give the user administrative privileges.
==================================================
User Instances is a feature that makes SQL Server 2005 Express different from other SQL Server editions. Before I explain User Instances, you need to understand that a SQL Server instance is essentially an in-memory occurrence of the sqlservr.exe executable program. Different SQL Server editions support different numbers of instances. For example, the SQL Server 2005 Enterprise Edition supports 50 instances, and the SQL Server 2005 Standard, Workgroup, and Express editions each support 16 instances. Each instance runs separately and has its own set of databases that aren't shared by any other instance. Client applications connect to each instance by using the instance name.

Typically, the first SQL Server instance you install becomes the "default" instance. The default instance uses the name of the computer on which it's installed. You can assign a name to subsequent instance installations, so they're called "named" instances. During the installation process, you can assign any name to a named instance. Client applications that want to connect to an instance use the \ convention. For example, if the default instance name is SQLServer1 and the instance name is MyInstance, the client application would connect to the named instance by using the server name SQLServer1\MyInstance.

As with the other SQL Server editions, SQL Server Express supports the default instance and named instances, but SQL Server Express uses SQLExpress as the default instance name rather than the name of the computer system.

In addition to regular SQL Server instances, SQL Server Express also supports User Instances. User instances are similar to named instances, but SQL Server Express creates user instances dynamically, and these instances have different limitations. When you install SQL Server Express, you have the option of enabling User Instances. By default, User Instances aren't enabled. After installation, you can enter the sp_configure command in SQL Server Management Studio Express (SSMSE) or the sqlcmd tool by using the following syntax:

sp_configure 'user instances enabled','1'

To disable User Instance support, replace 1 with a 0 in the sp_configure command.

User Instances were designed to make deploying databases along with applications easier. User Instances let users create a database instance on demand even if they don't have administrative rights. To utilize User Instances, the application's connection string needs to use the attachdbfilename and user instance keywords as follows:

Data Source=.\SQLExpress;integrated security=true;
attachdbfilename=\MyDatabase.mdf;user instance=true;"

When an application opens a connection to a SQL Server Express database in which User Instances are enabled and the application uses the attachdbfilename and user instance keywords, SQL Server Express copies the master and msdb databases to the user's directory. SQL Server Express starts a new instance of the sqlserver.exe program and SQL Server Express attaches the database named in the attachdbfilename keyword to the new instance.

Unlike common SQL Server instances, SQL Server Express User Instances have some limitations. User Instances don't allow network connections, only local connections. As you might expect with the network-access restriction, User Instances don't support replication or distributed queries to remote databases. In addition, Windows integrated authentication is required. For more information about SQL Server Express and User Instances you can read the Microsoft article "SQL Server 2005 Express Edition User Instances" at
http://msdn.microsoft.com/sql/express/default.aspx?pull=/library/en-us/dnsse/html/sqlexpuserinst.asp

SQL Tips

Difference between truncate and delete

• delete operations are logged and thus can be restored using transaction log backups (if not using Simple recovery model - the default)
• truncate operations are not logged, thus cannot be rolled back.
• delete can be used with WHERE clause to selectively delete rows
• truncate cannot selectively delete rows.
• truncate reseeds the identity column , delete does not reseed
• truncate cannot be used on tables with foreign key constraints
• since truncate is not logged, it does not activate triggers, delete does activate triggers
  

=====================================================
Difference between temp table and table variables:

• table variables are not logged, i.e. they don't use transaction logs, thus cannot participate in transactions
• indexes cannot be created on table variables
• foreign key constraints cannot be created either on table variables or temp tables
• table variables cannot be nested, i.e. they cannot be reused within the nested subquery, whereas temp tables can be accessed from within nested subqueries
• SP with table variables require fewer recompilation than SP with temp tables.
• table variables dont make use of parallelism since they reside in memory, temp table do make use of multiple processors since they reside in tempdb.
• table variables used for small amounts of data since they reside in memory.
  

=====================================================
Types of Data Integrity
Entity Integrity: Primary Key and Unique key constraints are used to maintain entity integrity
Domain Integrity : Check and Not NULL constraints are used to maintain domain integrity
Referential integrity : Foreign key constraints are used to maintain referential integrity
=====================================================
Local Temp tables & variables : exist for the duration of session, thus not shared by different client sessions. If Dynamic sql creates the local temp table then it falls out of scope as soon as it exits the EXEC statement used to execute the dynamic SQL.
Global Temp tables & variables : shared by all client sessions and are destroyed when the last client session exits
===========================================================
Types of UDFs:
Scalar valued udf : returns a single value, can be used anywhere an expression is used, just like subqueries.
table valued udf : returns a table.
2 types of table valued udf's :

• inline table valued udf : uses a single statement to return a table
• multi-statement table valued udf : multiple statements are issued in the udf before returning the table

UDF's can be used within select/having/where clauses and table valued udf's return rowsets/table and can be used in joins.
Cannot use temp tables in UDF's, only table variables.
UDFs are analogous to views that accept input parameters
================================================
Indexed Views are views that use disk space to store data.
Created with SCHEMABINDING option
have restrictions on the columns that the Indexed view can contain
have restrictions on the columns that can be used within the index created for the view
represent views that have a unique clustered index
Must have ansi_nulls, quoted_identifier, arithabort etc session options set to ON
If using standard edition than cannot be used implicitly by queries, must use the NOEXPAND optimizer HINT or reference with view with the VIEW name.
If using Group by then count_big(*) must be used
Must not be used in OLTP environments, suitable for less frequently updated base tables.

============================================================
ANSI_NULLS : default OFF, when ON then returns 0 rows when col = null or col <> null used even though the column has both null and not null values.
Thus we need to insure that we use the col is null, col is not null syntax for comparisons

ANSI_WARNINGS : default OFF: when ON then shows warnings when one of the following situations arises:
sum, max, min, avg used against a column that contains nulls
Divide by Zero exception occurs
Overflow exception occurs (string may be truncated)

QUOTED_IDENTIFIER : default OFF : when ON then allows object identifiers to use double quotes for distinguishing from reserved words and allows string literals to use single quotes

Indexed views need all the above sessions options to be set to ON

Concat_NULL_YIELDS NULL : default OFF

ANSI_DEFAULTS : default OFF: When ON then sets the above and the following session options to ON:
ANSI_PADDING

More on MSMQ

Recoverable messages:

MSMQ messages are stored in memory by default, hence there is a possibility of loosing them if the appdomain exits or the application crashes.
To create messages that get persisted to disk we use Recoverable messages.

Method 1:
Set the Recoverable property on the Message itself before sending it.

Message recoverableMessage = new Message();

recoverableMessage.Body = "Sample Recoverable Message";

recoverableMessage.Recoverable = true;

Method 2:
Use the "DefaultPropertiesToSend.Recoverable" property on MessageQueue itself to mark all messages sent via this queue as recoverable:

MessageQueue msgQ = new MessageQueue(@".\private$\Orders");

msgQ.DefaultPropertiesToSend.Recoverable = true;

msgQ.Send("This message will be marked as Recoverable");

=====================================================
Transactional Messages (Internal transactions)

MessageQueueTransaction msgTx = new MessageQueueTransaction();

MessageQueue msgQ = new MessageQueue(@".\private$\Orders");

msgTx.Begin();

try

{

msgQ.Send("First Message", msgTx);

msgQ.Send("Second Message", msgTx);

msgTx.Commit();

msgTx.Begin();

Message msg = msgQ.Receive(msgTx);

msgTx.Commit();

}

catch

{

msgTx.Abort();

}

finally

{

msgQ.Close();

}

================================================================

External Transactions (using MSDTC and COM+)

====================================================================

using System.EnterpriseServices;

using System.Messaging;

[assembly: ApplicationName("MSMQ_Example")]

[assembly: ApplicationActivation(ActivationOption.Server)]

[Transaction(TransactionOption.Required)]

public class Message : ServicedComponent

{

[AutoComplete()]

public void DoTransaction()

{

//Create the queue

MessageQueue msgQ = new MessageQueue(@".\private$\Orders");

Order incomingOrder;

//Configure the formatter with the expected type(s)

Type[] targetTypes = new Type[1];

targetTypes[0] = typeof(Order);

msgQ.Formatter = new XmlMessageFormatter(targetTypes);

//DB Connection setup code removed

SqlConnection sqlConn = new SqlConnection(connectionString);

SqlCommand insertLog = new SqlCommand(sqlString, sqlConn);

//Command setup code removed

try

{

//Receive new message, casting the result to the Order type

incomingOrder = (Order)(msgQ.Receive(new TimeSpan(0, 0, 30)).Body);

//Use ID from message to setup parameters for database insert

orderIDParam.Value = incomingOrder.ID;

receiveParam.Value = DateTime.Now;

insertLog.Parameters.Add(orderIDParam);

insertLog.Parameters.Add(receiveParam);

sqlConn.Open();

//Insert a new entry into the Log table

insertLog.ExecuteNonQuery();

//Mark the transaction as completing successfully... can be done using ContextUtil.SetComplete

}

catch (Exception ex)

{

//Remember to throw the exception, only then will AutoComplete() abort the transaction

// transaction can be aborted using ContextUtil.Abort if AutoComplete() atribute is not used

throw ex;

}

finally

{

//Always close the connection, regardless of success/failure

sqlConn.Close();

}

}

}

After you create the serviced component, it must be strongly named and then deployed to the GAC so as to work correctly as a COM+ application.

After the component has been installed into the COM+ application, clients can create and call your component just like any other .NET class, and your component will automatically run inside COM+ and act as part of a transaction.

MSMQ Primer

Creating Queues and Managing Queues is a breeze with .NET
Queuenames in .NET:

public queues : .\qname
private : .\private$\qname
journal: .\qname\journal$



Create queues using the MessageQueue class and specify if you need to use Journal queues with the message queue. the Journal queues hold the messages after that have been "received" from the main message queue.
The third type of queues are the AdministrationQueues /Acnowledgement queues and we use the Message.RecieveByCorrelationId method or PeekByCorrelationId to receive admin/ack messages from these queues after a message has been received from the main message queue.
the PeekByCorrelationId method takes in the message id of the message in the main message queue.

public class QueueMgmt

{

public static MessageQueue CreateQueue(string path)

{

MessageQueue mq = null;

if (MessageQueue.Exists(path))

{

mq = new MessageQueue(path);

Console.WriteLine("Connected to existing queue " + path);

}

else

{

mq = MessageQueue.Create(path);

Console.WriteLine("Created to new queue " + path);

}

Console.WriteLine("Message Queue format is " + mq.FormatName);

return mq;

}

}

=========================================================

private void btnCreateQueue_Click(object sender, EventArgs e)

{

// Create a public queue and configure it for journaling.

using (MessageQueue mq = QueueMgmt.CreateQueue(@".\DotNet"))

{

mq.Label = "DotNet Message Queue";

mq.UseJournalQueue = true;

} // mq disposed.

// Create a private queue for acknowledgments.

using (MessageQueue mq = QueueMgmt.CreateQueue(@".\Private$\DotNetAdmin"))

{

mq.Label = "DotNet Acknowledge Queue";

} // mq disposed.

}

=======================================
After creating the queues, you can send messages to the queue using mq.Send method, note we can send any XML Serializable types in the message body. Remember to set the mq.Formatter.TargetTypes property with the TYPES that it is supposed to deserialize when receiving them.

Note we are also setting the Administration queue which will receive the acknowledgment messages once they are read from the Queue.

private void btnSendMessage_Click(object sender, EventArgs e)

{

using (MessageQueue mq = new MessageQueue(@".\dotnet"))

{

using (System.Messaging.Message msg = new System.Messaging.Message())

{

msg.Label = "dotnetmessage";

msg.Body = "Message : " + DateTime.Now.ToString();

msg.AdministrationQueue = new MessageQueue(@".\private$\dotnetadmin");

msg.AcknowledgeType = AcknowledgeTypes.FullReceive;

mq.Send(msg);

Console.WriteLine("Message sent with ID: " + msg.Id);

}

}

}

==============================================
Once the message is sent it arrives in the public or the private queue where it was sent.
Then while receiving the messages we read the message from the public/private queue, once it has been received a copy is stored in the Journal queue if one has been setup.
Also admin messages are sent to the Admin queue depending on the AcknowledgementTypes setup during the sending of the message.
Note we can get mutiple ack/admin messages for a single message by ORing different ackTypes in the message.AcknowledgeType enumneration.

private void btnReceive_Click(object sender, EventArgs e)

{

Type[] targetTypes = { typeof(string) };

string msgId = string.Empty;

using (MessageQueue mq = new MessageQueue(@".\dotnet"))

{

((XmlMessageFormatter)mq.Formatter).TargetTypes = targetTypes;

using (System.Messaging.Message msg = mq.Receive())

{

Console.WriteLine("Received Message ID {0} : \n\t Body = {1}", msg.Id, msg.Body);

msgId = msg.Id;

}

}

using (MessageQueue mq = new MessageQueue(@".\dotnet\Journal$"))

{

((XmlMessageFormatter)mq.Formatter).TargetTypes = targetTypes;

using (System.Messaging.Message msg = mq.PeekById(msgId))

{

Console.WriteLine("Peeked message from Journal Queue {0}:\n\t Body = {1}", msg.Id, msg.Body);

}

}

using (MessageQueue mq = new MessageQueue(@".\private$\dotnetadmin"))

{

using (System.Messaging.Message msg = mq.PeekByCorrelationId(msgId))

{

Console.WriteLine("Peeked Acknowledgement queue {0} : \n\t Acknowledgement is {1}", msg.Id, msg.Acknowledgment);

}

}

}

DBCC Commands

Some useful DBCC commands are:

DBCC CHECKIDENT ('authors',RESEED,0)

DBCC DROPCLEANBUFFERS --- Removes all clean buffers from the buffer pool
DBCC FREEPROCCACHE -- this clears the proc cache on all db's on the server.
DBCC FLUSHPROCINDB ---- clears the cache for a paricular DB


You can create PROXY users in SQL 2005 using 2 methods
1 CREATE USER greg WITHOUT LOGIN

2. create certificate al_cert with subject='certificate for al'
create user al FOR CERTIFICATE al_cert.