What is mirroring?

Mirroring constantly synchronizes the transaction log of a mirror server with the transaction log of a principal server. The synchronization can be either synchronal to enable high availability, or asynchronal to enable high performance.

If set to high availability, a witness server is required. this can be either the principal server itself, or a 3rd server. If a 3rd server is chosen, this ensures more security in case of a failure.

To configure mirroring, the mirror server must restore a backup of the principal with NORECOVERY mode. Thus you cannot access the mirror database directly, since it remains in recovery mode. But you can create a snapshot of the mirror to read data. Also, the database must set to full recovery mode. (Note: if you want to have a full working copy of a database, then log shipping or replication is your choice over mirroring)

Mirroring requires endpoints (TCP or HTTP) for the principal, the witness and the mirror server. The endpoints are configured automatically, when you create mirroring in the properties options of the database.

The main purpose for mirroring however is to have a high available backup in case of an error of the principal server.  In High Availability mode, a failover is automatically performed to the mirror, when a failure occurs on the principal. Otherwise you need to launch a failover manually:

ALTER DATABASE <mirror_database> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

When you right click any Database in the SSMS, you can access the Copy Database tool:

This feature easily allows you to copy or move a database from one server to another or within one server.

But before you can do this you need to ensure the following conditions:

• SQL Server Agent service must be running on the destination server
• SQL Server Integration Services service must be running on the destination server
• These services must be running with an account that has at least sysdbadmin rights on the target sql server.
• The source database must not have an owner that is a local account to the source server (e.g. LocalServer1\Tom),
  otherwise the transfer will fail. If it is associated with a local account, change the owner, for instance to sa.

After the prerequisites are confirmed, you can launch the Copy Database wizard. There are currently two ways to copy or move the database:

1. Use the detach and attach method
2. Use the SQL Management Object Method

Use the SQL Management Object Method

1) is significantly faster but requires a share on the target server and a configured Integration Services Proxy Account on the target server. So the easiest way to copy the source is method 2).

After some view steps in the wizard, you reach the Select Server Objects page where Logins are preselected:

Copying the Logins is not necassarily required unless you have granted some rights to specific logins, so in some cases you can remove the Logins from the Selected related objects list. in some cases.

Use the detach and attach method

If you decide to use this method to copy or move a database, before you can archive this, you need to create a Credential and a Proxy for the SSIS Package Execution in the SQL Server Agent:

To create a credential that will be later associated to the proxy open Server/Security/Credential and right click to open the popup menu where you select New Credential:

The New Credential Dialog is self explanatory so just configure the required values.

Now you can create a  Proxy:

In the dialog select a proxy name of your choice and as Credential name select the previously created credential. Select at least “SQL Server Integration Services Package” and that’s it:

Sometimes you need to get a result set for a field that contains any value from an array.
The default approach in LINQ would be:

from var c in data where array.Contains(c.field) select c;

which creates a T-SQL that looks like that:

select * from data where data.field in (1,2,3,...)

Unfortunately, this would produce an error with EF, since Contains is currently not implemented with .NET 3.5SP1.

Therefore, we need a workarround that builds an expression that implements an or for each indiviudal value in the array to be compared against the field:

private void button2_Click(object sender, RoutedEventArgs e)
{
    int[] types = new int[] { 1, 2, 3, 10 };
    using (TomsPortal.TomsPortalEntities1 tp = new TomsPortalEntities1())
    {
        var r = from node in tp.Node where node.Taxonomy.Id != 3 where Contains<Node, int>(types) select node;
        r = r.Where(Contains<Node, int>(node => node.Id, types));

        foreach (var o in r)
        {
            Trace.WriteLine(o.Id);
        }
    }
}

static Expression<Func<TElement, bool>> Contains<TElement, TValue>(this Expression<Func<TElement, TValue>> valueSelector, IEnumerable<TValue> values)
{
    if (null == valueSelector) { throw new ArgumentNullException("valueSelector"); }
    if (null == values) { throw new ArgumentNullException("values"); }
    if (!values.Any()) return e => false;

    var equals = from value in values
        select
            System.Linq.Expressions.Expression.Equal(
                valueSelector.Body,
                System.Linq.Expressions.Expression.Constant(value, typeof(TValue))
            );

    var body = equals.Aggregate((accumulate, equal) =>
        System.Linq.Expressions.Expression.Or(accumulate, equal));

    ParameterExpression p = valueSelector.Parameters.Single();
    var ex = System.Linq.Expressions.Expression.Lambda<Func<TElement, bool>>(body, p);
   
    return ex;
}

Select with EF is always slower than with Linq2SQL

This is only true when you leave the default value of ObjectQuery.MergeOption or set it to any other value but MergeOption.NoTracking. However, if you do set this value to MergeOption.NoTracking, a select will be 3 times faster than with LINQ2SQL.

The following table compares various scenarios:

So what you see here is that EDM is the fastest access after DataReader, but also the slowest if tracking is (by default) enabled. Depending on what you do, you might consider to disable Tracking for faster read access, but with the possibility of redundant data in memory.

Linq2Sql supports MS SQL only

This is not really true! Though there is currently only one IQueryProvider for MS SQL, but who prevents other SQL Provider to implement their own IQueryProvider? For instance, Oracle offers native DbConnection, DbCommand and DbReader, why not a QueryProvider?

EDM does not support UDF (User defined Functions)

Unfortunately, as with .NET 3.5 SP1 this is true. Though you can currently describe a function with

where it differs from a stored procedure by IsComposable set to true instead of false, there is currently no support for it. Usage of UDF with EF is announced to be available with .NET 4.0

Linq2SQL can do everything that EF can do and more

No. There are some features that is currently not possible with EF, but there are also features in EF that is not available with Linq2SQL.

• EF is significantly (>300%) faster in writing and updating data back to the database
• EF supports n:m relations which Linq2SQL doesn't
• Linq2SQL supports contains in query, while EF (currently) doesn't
• Linq2SQL supports UDF, EF doesn't (with Framework 3.5SP1)