Change SQL backup location

During the installation of a SQL instance, you'll have the ability to define some folders
These locations can be defined:

• User database directory
• User database lof directory
• Temp DB directory
• Temp DB log directory
• Backup directory



Within SQL Server Management Studio you have the ability to change the default location for your Data and Log files for all new databases. Just  right click on the server name and select properties, navigate to the Database Settings page. Here you can find a section Database default locations for changing the data and log directories.



But if you search through all of the pages under Database Settings you will not find anything that shows the default backup directory.  To find this we need to look in the registry.

Open the registry tool REGEDIT and navigate to following key:
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.BTSTPTST1\MSSQLServer

Or something similar for your instance of SQL server. The registry key BackupDirectory is the one you'll need to change to set another default Backup Directory.



Changing the registry can also be done with a T-SQL query command. To do so, you'll be using the extended stored procedures XP_REGREAD and XP_REGWRITE.

Reading the falue in registry can be done by using this command:

DECLARE @BackupDirectory VARCHAR(100)
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
  @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.BTSTPTST1\MSSQLServer',
  @value_name='BackupDirectory',
  @BackupDirectory=@BackupDirectory OUTPUT SELECT @BackupDirectory

This will result in something similar as this:

Changing the default folder can be done by using the following command

EXEC master..xp_regwrite
     @rootkey='HKEY_LOCAL_MACHINE',
     @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.BTSTPTST1\MSSQLServer',
     @value_name='BackupDirectory',
     @type='REG_SZ',
     @value='C:\Program Files\Microsoft SQL Server\MSSQL10_50.BTSTPTST\MSSQL\Backup'

key file authentication with the BizTalk SFTP adapter

Authentication on an SFTP location can be done with simple username/password credentials… However another way to authenticate the sender is by using a Key Authentication file. But how can this last authentication method be used inside the BizTalk SFTP adapter?

the BizTalk SFTP adapter we used to achieve this is the  freely available SFTP adapter on Codeplex. Just download the adapter and install it. And don’t forget to add the adapter in the BizTalk admin console of course.

Now then let’s set up the port in BizTalk. In my example I ‘ve configured a receive location, but obviously the same goes for the send port configuration.

First of all choose the created adapter in the biztalk admin console, as shown below.



To set up the proper configuration for the SFTP port, just press the ‘Configure’-button.



These parameters need to be set:

• Schedule: define a timely schedule how often you want to poll the SFTP location (receive location only)
• File Mask: set the correct file mask
• SSH Host: the host address of the SFTP location
• SSH Identyfile: Select the Key authentication file on disk
• SSH Identyfile Passphrase: the password of the selected key authentication file
• SSH Remote Path: the path on on the SFTP location
• SSH User: the user name to authenticate on the SFTP location

Seems pretty straight forward, doesn’t it?
Well there are some things you ‘ll need to take into account to make sure the connection can be made…

Make sure the identyfile type is supported

You’ll need to make sure the identyfile type is supported by the library. The supported versions can be found here. As this is the library which is used by the bizTalk SFTP adapter.

Make sure the identyfile can be recognized

In my case for example, I got an *.ppk file as authentication file. But as it turned out, this wasn’t recognized.
The error message you git is this:

invalid privatekey: D:\tempkey.ppk

Which in the end seemed a bit misleading, as the problem wasn’t the private key for the authentication file. It was just the *.ppk file that wasn’t supported.
To make the key authentication file working, I had to convert the file to an OpenSSH key file. This can be done by using PuttyGen for example, and exporting the authentication file as an OpenSSH key file.

Don’t use both password parameters

Also make sure you only use the intended parameter SSH Identyfile Passphrase, and leave the SSH password blank.
In case both password fields are filled, the SFTP adapter will try to authenticate by the username/password credentials stated. So it won’t offer the specified identyfile to authenticate.




I orignally posted this on my companies blog: http://blog.cnext.eu/2013/02/19/using-key-file-authentication-with-the-biztalk-sftp-adapter/

SQL Server Compact 4.0 Tooling in Visual Studio 2010

I developed a program which uses SQL Server Compact. Initially it was developed creating SQL Server COmpact 4.0 databases. Now after reinstalling my laptop, I tried to open the database, but it appaered I was missing the necessary tooling for SQL Server Compact 4.0, I could only open/create SQL Server Compact 3.5 databases.

I've immediatly installed the SQL Server Compact 4.0 runtime, which can be found here.
But this however didn't seem to be enough... I still couldn't open my created SQL compact database.

What I needed to do, was install some additional software using Microsoft Web Platform Installer 3.0.
Make sure you install these packages:

• Microsoft Visual Studio 2010 SP1
• Microsoft Visual Studion 2010 SP1 Tools for SQL Server Compact 4.0

Note that selecting and installing the Visual Studio 2010 SP1 or the Visual Web Developer Express 2010 SP1 also installs the SQL Server Compact 4.0 components that are needed for the tooling support. These components are called as Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0 and SQL Server Compact 4.0. The Web Platform Installer automatically detects whether these components are already installed on the machine, and if they are not installed, the Web Platform Installer will install them. There is no need to separately select the SQL Server Compact 4.0 components in the Web Platform Installer.

The installer for visual studio SP1 can also be found here.

Error: The self-extracting zip file is part of a multidisk zip file

I tried to install the Cumulative update package 14 for SQL Server 2008 R2.

After requesting the update on http://support.microsoft.com/kb/2703280, I got an email containing a link to download the file 449383_intl_x64_zip.exe. After downloading the file an trying to run the self extracting zip file, I got following error:

This self-extracting zip file is part of a multidisk zip file. Please insert the last disk of the set.

There is no other option than pressing the OK-button.And after pressing the OK-button 3 times, I got following error message:


An error occured while unzipping. One or more files were not succesfully unzipped. The error code is 110.

The solution for this seemed to be to just download the file from the given link in my email again.
After this I could succesfully run the self-extracting zip file an install my cumulative update package.

BizTalk deployment - How do YOU do it?

For the deployment of BizTalk applications we use a custom made tool within my company Cnext. Now I was curious to have some feedback from the community on the deployment of BizTalk applications. How do you handle the biztalk deployments at your company?

Our deployment manager tool is based on the idea to store all used objects in a database model (BizTalk artifacts, but also stuff like MSMQ, file locations, SQL objects, etc. …). Most of these objects are added using auto discovery of the BizTalk databases, so manually adding of objects is reduced to a minimum. Above all, BizTalk artifacts can have a different configuration (binding) defined per environment (test, dev, prod, …).

It also allows you to define all dependencies. Again most of these dependencies will be defined by the tool automatically.

This way of working makes it possible for the tool to define which actions need to be taken to deploy a certain application (or just a part of the application or only some objects). The deployment manager tool will define which objects need to be removed and redeployed (also unenlisting/disabling en starting/enabling artifacts will be done by itself). As a result, using the tool will allow us to deploy much faster, because the objects to redeploy are reduced to an absolute minimum and no complete redeploy is needed (like BizTalk Deployment framework does for example).

Another very useful and much used functionality is the possibility to define complete business/functional flows, including some generic components. This makes sure you can also deploy or redeploy a complete (new) flow (like an order flow for example) by itself, including all necessary objects (as well receive as send ports, file locations, etc. …).

The deployment can be done cross BizTalk applications. So the separation in applications is no longer deployment dependent.

The most important part is to set the database model correct and keep it this way… this will guarantee a much easier and faster deployment, where each environment has its own version of the current deployed objects.

Please answer following questions:

1.      Is your BizTalk deployment automated (BizTalk deployment framework, custom scripting with MSBuild or BTSTask, powershell scripts, etc. …), or do you just do manual deployment using MSI and binding files?
2.        Which deployment tools or scripts are you using, or have you used before? And what are your thoughts of these tools (benefits and complaints)?
3.        What do you think of a tool like our custom deployment manager tool (using autodiscovery, etc. …)?

Thanks for your replies.
 When there is enough feedback, I'll devote a new post on my analysis.

SQL Server Configuration Manager - Error 0x80041010

When opening the SQL Server Configuration manager (for SQL Server 2008 R2 in my case), I kept getting following error:

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid class [0x80041010]

The solution to resolve this turned out to be quite simple. Just open a command prompt and execute following command:

C:\Program Files\Microsoft SQL Server\100\Shared>mofcomp "C:\Program Files\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

Depending on the SQL version installed the path to the file could also be something like C:\Program Files\Microsoft SQL Server\90\Shared\sqlmgmproviderxpsp2up.mof

Blog admin of my companies blog

First of all, I would like to thank all of my regular reader and hope you've al enjoyed my blog posts for almost 2 years now. I already announced in one of my previous posts some time ago that I would join the contributors of the biztalk admin blog at http://www.biztalkadminsblogging.com/ .

Today, I'm proud to announce that there is another brand new blog I will be contributing to on BizTalk and Azure related news, problem solvement, etc.... My companies (Cnext) blog (http://blog.cnext.eu) . Well actually, the blog already existed quite some time (even longer than my own blog :) ), but there wasn't any new posts for over 2 years. Most recently I became the new administrator of the Cnext blog and had the task to revive it. After 1 month, 3 completely new posts, and still some blog posts in the making, it is ready to be shared with all of you... see for yourself at the Cnexts revived blog.

The first thing I did is restyle the entire blog to match the current branding of Cnext. And after publishing some new material, I can say that it is ready to be read!

Now go and check it out.. and keep following all of my blog posts.




More info on cnext can be found on http://www.cnext.eu. And as mentioned the blog will be situated at http://blog.cnext.eu... keep an eye out for all new material to be added soon!