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.