Jan 30, 2012

SQL Server Database Restore for Dummies – like me

I got a SQL server (express) database backup (.bak file) from a customer to do a test against. In the past, I just restored from my own, previously existing databases, so I never came across the issue of restoring one from a complete different machine. Using SSMS I got the following error messages

The backup set holds a backup of a database other than the existing database – or

File 'Db' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.CUSTOMER\MSSQL\DATA\Db.mdf'. Use WITH MOVE to identify a valid location for the file.

The path does not exist on my machine. After browsing the web I found out the following:

  1. create a new and empty database named as the one from the customer (in this case ‘Db’)
  2. use a script to run

    RESTORE DATABASE [Db]
    FROM DISK = 'c:\theDbBackup.bak'
    WITH REPLACE

Jan 29, 2012

Excellence in Software Engineering Conference 2012 - in Zurich

 

This year the ESE conference 2012 will take place in Zurich April 24. and 25. – hosted by Zühlke Engineering AG.

That’s where software experts from around the world meet their peers and prominent speakers. The conference targets software architects, project managers, IT manager, process experts and all kinds of techies. Interactive discussion and talks are a major part of the two days.

image

There are three trendy tracks: Agile, Cloud and Mobile. See this for more details.

Jan 16, 2012

Struggling with Azure SDK Samples –> use Microsoft.ServiceBus.dll Version 1.6! or still 1.5?

Got some issues recently with the Azure SDK samples (e.g. RelayedConnectionSample with NetTcp, MulticastSample, etc.). SecurityExceptions were thrown, etc.

Until I noticed that the samples still reference the Microsoft.ServiceBus.dll in

  • version 1.5.0.0 under c:\Program Files\Windows Azure AppFabric SDK\V1.5\Assemblies\NET4.0\ instead of the latest one
  • version 1.6.0.0 which is now installed under C:\Program Files\Windows Azure SDK\v1.6\ServiceBus\ref

So you need to

  1. change assembly references to Microsoft.ServiceBus 1.6.0.0
  2. change .config settings to 1.6.0.0 to:

    <extensions>

      <bindingExtensions>

        <add name="netEventRelayBinding" type="Microsoft.ServiceBus.Configuration.NetEventRelayBindingCollectionElement, Microsoft.ServiceBus, Version=1.6.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />

      </bindingExtensions>

    </extensions>

  </system.serviceModel>

 

  <runtime>

    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">

      <dependentAssembly>

        <assemblyIdentity name="Microsoft.ServiceBus"

                          publicKeyToken="31bf3856ad364e35"

                          culture="neutral" />

        <bindingRedirect oldVersion="1.0.0.0"

                         newVersion="1.6.0.0"/>

      </dependentAssembly>

    </assemblyBinding>

  </runtime>

and it works again.

However, some of the samples did not with 1.6 but only with 1.5. Maybe I messed up with the latest SDK installation.

Azure Connect

Using Windows “Azure Connect” - a service from the Windows Azure platform – one can establish a protected network connection between enterprise computers and the cloud roles (E2C). This virtual IPsec network allows for:

  • easier migration of tiers of an applications to the cloud
  • tunnel firewalls, proxies, NATs
  • hybrid (cloud to on-premise) apps
  • various higher-level communication protocols
  • apps where the protocol might change
  • have high number of communicating endpoints
  • domain joins (some ActiveDirectory; logins)
  • remote administration, debugging and troubleshooting
  • DNS
  • etc.

Setting up Connect is a 3 step process. There is a tutorial showing all the details.

1. Get an activation token from the Azure portal and configure your Azure roles to use it.

image

image

Copy the token into your role’s properties / virtual network settings

image

Now you publish your Azure roles to the staging or production environment in the cloud. Wait until the service is ready and check that it appears in the portals “Virtual network” –> “Groups and Roles” and “Activated Endpoints”.

2. Enable local machines to connect to roles in the cloud. Install the Connect Agent on local machines. Copy the link URL to a browser, download the configured executable and run it. A system tray icon like the following should appear

image

3. Pair the local machines and Azure roles in a “Group” (network policy). In the portal click Create Group

image

The system tray icon should change to connected now.

Please note that ping will not work until you enable it in your role using a startup task script. From my limited home network I experienced some significant (first-hit) network delays

Pinging RD00155D3A622D [2a01:111:f102:121:858:cf20:a9dd:5639] with 32 bytes of data:
Reply from 2a01:111:f102:121:858:cf20:a9dd:5639: time=299ms
Reply from 2a01:111:f102:121:858:cf20:a9dd:5639: time=258ms
Reply from 2a01:111:f102:121:858:cf20:a9dd:5639: time=359ms
Reply from 2a01:111:f102:121:858:cf20:a9dd:5639: time=368ms

Ping statistics for 2a01:111:f102:121:858:cf20:a9dd:5639:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 258ms, Maximum = 368ms, Average = 321ms

Microsoft Azure SQL Data Sync

Azure Data Sync let’s you synchronize enterprise (on-premise) SQL Server (2005 and above) databases with SQL Azure databases – or several SQL Azure databases. There is a local agent and a cloud sync group service necessary for it to work.

This is the first preview from Nov 14 2011; let’s have a try.

In the management portal navigation tree go to Data Sync, select a subscription, a data center region,

image

You now have two options:

image

Let’s synchronize on-prem and a cloud SQL Azure database. The wizard leads you through the configuration of a sync group

image

image

image

image

Download and install the so called Sync Agent for the local server.

image

Then start the Data Sync Agent Preview from the start menu.

image

Now generate an agent key from the management portal and copy it into the local agent wizard.

image

Then register a SQL Server

image

to get

image

Back in the portal we add the agent to the Sync Group

image

Now we are half way through. Now let’s add an SQL Azure database:

image

Just creating a new SQL Azure database for this demo – and selecting it in Step 3 above.

image

In the following step (4) we configure the scheduling, and the conflict resolution (client or hub wins)

image

Select the on-premise SQL database

image

Click deploy now

image

Done!

Now, let’s see that it worked correctly. In the local Northwind database there are 2 people from Switzerland, add a row filter for Country = ‘Switzerland’. Then go the SSMS, connect the SQL Azure DB. There is a new dbo.Customers table now. Please also note the added tables for the change tracking. There are also some triggers and stored procedures added to your database. This may impact your runtime performance.

image

There are the 2 rows as expected:

image

That’s it again in the portal

image

This was actually super simple to setup. There are some best practices out there.

Negative point: there does not seem to exist an API or .NET library for scripted configuration and operation.