Quickly and Safely Move a Microsoft SQL Server Database (MDF and LDF Files) to a New Physical Location (Including Setting Read Write Access)

To move a Microsoft SQL Server database to a new physical location you need to detach, copy, reattach and set permissions for the database MDF and log LDF files associated with the database. I needed to do it as the drive on which each type of database required file (MDF/LDF) was located was to be separately backed up, as per the site backup regulations. I was using SQL Server 2008 R2.

Microsoft’s recommended way of doing this is to use SQL Management Studio. Create a query and detach your database “mydb” by entering and running the following:

use master
go
sp_detach_db ‘mydb’
go

Now copy the MDF and LDF files to their new location and reattach (locations and file names are for this example only):

use master
go
sp_attach_db ‘mydb’,’E:\DATA\mydb.mdf’,’F:\DATA\mydb_log.ldf’
go

You can check the basic properties of the database (and that the file locations have been correctly set) using:

use mydb
go
sp_helpfile
go

The final thing I needed to do was to set the file/folder permissions so that the database could go from read-only to read/write. I first set the folder permissions for my two new folders (“E:\DATA\” and “F:\DATA\” as above). To do this I needed to add the following user to the security settings with full control:

sqlservermssqluser$COMPUTERNAME$mssqlserver

This didn’t actually set the files (server permissions error) so I set the permissions for the “E:\DATA\mydb.mdf” and “F:\DATA\mydb_log.ldf” files individually the same way.

Now open up another query window and type the following to enable read/write access to the database:

use master
go
alter database mydb set read_write with no_wait
go

Now your database is set up exactly as it was previously, only the associated files have moved physical location.

Using ADODB to easily connect to MySQL and Microsoft SQL Server from PHP

In previous posts I’ve talked about connecting to Microsoft SQL Server from PHP using ODBC and using mssql_connect to connect directly. The most important thing I’ve realised is not to re-invent the adodb wheel, which has shown to be a really great cross-platform way of connecting to databases.

The best thing about adodb is that just by changing the connection string I can talk to MySQL and Microsoft SQL Server without changing any of my code. I’ve used this for performance and migration testing and it’s been perfect, with none of the strange hangs I noticed with a standard ODBC connection. Currently I am using Ubuntu Server 10.10 to host all the PHP, which has overall been a pretty good experience, apart from these strange hangs.

If you are in Ubuntu Server you may need to install some ODBC packages in order to get adodb to work. Some of these packages are not totally necessary but I would recommend them for testing ODBC connections etc:

sudo apt-get install php5-odbc unixodbc unixodbc-dev freetds-dev sqsh tdsodbc

Now you need to create a SQL user in either your MySQL or SQL Server database. Make sure you map the SQL Server user as a database owner on the DB you want to connect to and give them the same default schema. The reason for this is I couldn’t get the connection in adodb to work with the actual DB specified (unlike in MySQL).

Download adodb5 and place the directory in the root of your webserver and add the following to the top of your PHP file:

include(‘adodb5/adodb.inc.php’);

Now open up your PHP file and insert the following for SQL Server with IP “the.ser.ver.ip”:

$dbcstring = “mssql://username:password@the.ser.ver.ip”;

Or the following for MySQL:

$dbcstring = “mysql://username:password@the.ser.ver.ip/databasename?persist”;

Create a connection to the database:

$DB = NewADOConnection($dbcstring);

Now you can use the built in commands in adodb to easily pull data from the database and display it on screen. It doesn’t matter which database you are connecting to, the commands are now the same. This is the main advantage of adodb in my opinion, along with the ease of use:

$arr = $DB->GetAll(“SELECT column FROM table”);
for($i=0;$i<count($arr);$i++)
{
echo $arr[$i][0]  . “<br/>”;
}

It’s worth reading the guide on adodb for the commands you can use, such as $DB->GetRow(), $DB->GetOne() and $DB->Execute(). The great thing is that adodb makes everything so simple you can treat your data as arrays or you can go further and make use of some of the more advanced functionality. Either way, by using adodb you massively cut down on code rewrites when you migrate to a new database.

Connecting to Microsoft SQL Server using ODBC from Ubuntu Server

Previously I showed how to connect to Microsoft SQL Server in PHP using mssql_connect(). This works but is not as neat as using ODBC.

Unfortunately when writing this guide I got stuck at the last hurdle when trying to use ODBC and adodb in PHP. I could connect to the database using:

include(‘adodb5/adodb.inc.php’);
$connection=& ADONewConnection(‘odbc_mssql’);
$connection->Connect(‘datasourcename’,’username’,’password’);

Or using odbc_connect:

$connection= odbc_connect(‘datasourcename’, ‘username’, ‘password’);

But selecting data using “$connection->GetAll()” or “odbc_exec()” just resulted in a page that never loaded. Fortunately the ODBC part worked, there was just something I haven’t figured out yet with ODBC in PHP.

This guide will show you how to set up ODBC in Ubuntu using FreeTDS to talk to Microsoft SQL Server. I am trying to work out why the server connects fine but won’t execute queries in PHP. When I find out why I will update this post. To create this guide I went off a post on Ubuntu forums with some additions. There are a lot of steps, but at least some of the programs you use along the way allow you to check things are working as expected before you come to test in PHP.

First you need to install php5-odbc in Ubuntu:

sudo apt-get install php5-odbc

Now you need to add freetds to create a connection and talk to your server, plus a few other handy programs:

sudo apt-get install unixodbc unixodbc-dev freetds-dev sqsh tdsodbc

Edit the example server details or create a new one in /etc/freetds/freetds.conf to point to your server (in my case I called it “sqlserver”):

[sqlserver]
host = ip.add.ress
port = 1433
tds version = 7.0

You can check the connection is set up using sqsh:

sqsh -S sqlserver -U username -P password

Alternatively, you can connect directly to the ip.add.ress:

sqsh -S ip.add.ress -U username -P password

Now in sqsh you can select from a table in your SQL Server database and display it on screen by entering your query and “go” on the next line:

1> select * from databasename.dbo.table
2> go

Hopefully you should see your data on screen. The next step is to configure ODBC so check where the config files are located using:

odbcinst -j

Which should say “DRIVERS: /etc/odbcinst.ini” and “SYSTEM DATA SOURCES: /etc/odbc.ini” which are the files we will be editing.

Now we set up the FreeTDS driver so it can be used by ODBC so open “/etc/odbcinst.ini” (was a blank file in my case) and add:

[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/odbc/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so
CPTimeout =
CPReuse =
FileUsage = 1

Open “/etc/odbc.ini” and create the system data source “datasourcename” that we will use everywhere (including in PHP):

[datasourcename]
Driver = FreeTDS
Description  = ODBC connection via FreeTDS
Trace = No
Servername = sqlserver
Database = databasename

It’s important to note that “Servername” here refers to the server details in /etc/freetds/freetds.conf NOT to an IP address.

You can test the connection works using isql:

isql -v datasourcename username password

Now you can use this data source “datasourcename” in PHP, which is the goal! To test the connection works I tried odbc_connect():

$connection = odbc_connect(‘datasourcename’, ‘username’, ‘password’);
if (!$connection) die(“Connection failed”);
else echo “works”;

Which does work. Now I just need to work out why queries such as the following cause PHP to hang:

$query = odbc_exec($connection, ‘SELECT column FROM table’) or die (odbc_errormsg());

Connecting to Microsoft SQL Server from PHP in Ubuntu using mssql_connect()

As part of ongoing testing to find the best way of storing large amounts of data we are considering Microsoft SQL Server, which needs to be accessible from our PHP5 application. The site is hosted on Ubuntu Server 10.10, which makes things a little more interesting. We currently use adodb5 to talk to a MySQL server, which works fine, apart from MySQL being far too slow. It is possible to create an ODBC connection in Ubuntu to talk to SQL Server but for testing the speed of our queries I just used mssql_connect(). In the near future I will move the database connection entirely to ODBC/adodb so we don’t have to rewrite any of our existing code.

Ubuntu doesn’t come with the packages needed for mssql_connect() by default so you need to install them:

sudo apt-get install php5-sybase

Then restart the apache server to apply the changes:

sudo /etc/init.d/apache2 restart

Now you can use mssql_connect and its associated functions in your PHP to connect to your Microsoft SQL Server, as in this example:

ini_set(‘display_errors’, 1);
$server = ‘my.server.ip:1433DATABASEINSTANCE’;
$link = mssql_connect($server, ‘username’, ‘password’);

if (!$link) {
die(‘<br/><br/>Something went wrong while connecting to MSSQL’);
}
else {
$selected = mssql_select_db(“databasename”, $link)
or die(“Couldn’t open database databasename”);
echo “connected to databasename<br/>”;

$result = mssql_query(“select name from table”);

while($row = mssql_fetch_array($result))
echo $row["name"] . “<br/>”;
}

Migrating data from MySQL to Microsoft SQL Server (and away from MySQL slowness)

I hit a stumbling block with MySQL that has made me seriously rethink using it in future. A simple JOIN query between two tables (one with 1k rows, 1 with 660k rows) was taking a very long time in MySQL. I had a look at indexing etc but couldn’t get the execution time down below 220 seconds on the test machine, no matter how the query was structured. I even considered adding redundant columns to the dataset to allow for indexing using integers, rather than varchars. While trying a few things I decided to test the exact same query on the same data set in Microsoft SQL Server Express 2005, where it took 4 seconds (1 second after the query had been cached).

The difference is obviously massive, far more than I was expecting. I then ran a few tests using DISTINCT, GROUP BY and COUNT(*) and noticed that in every instance, MySQL was slower than SQL Server, even on identical test machines. Considering the database is expected to grow massively over the next year we decided that it would be sensible to consider alternatives to MySQL. We already have a license for SQL Server so the decision was pretty easy (also, all my testing is currently done using SQL Server Express 2005).

Migrating the data across is not easy using .sql exports from MySQL Workbench as the data set is large (60mb of .sql) and the output file needs editing for SQL Server compatibility. Annoyingly SQL Server Express 2005 doesn’t support multiple INSERT VALUES queries (please upgrade to SQL Server 2008!) making it even more difficult.

I found a post on CodeProject by Niklas Henricson that explains how easy it is to migrate tables from MySQL to SQL Server using an ODBC connector from MySQL. You can download the ODBC connector from MySQL and create a connection which can then be easily used in SQL Server Management Studio to transfer data between MySQL and SQL Server. A summary of Niklas’ steps are:

  • Open your ODBC Data Source Administrator from the Control Panel -> Administrative Tools. Under the tab labelled as “System DSN”, press the “Add” button.
  • On the “Create New Data Source” dialog that appeared, choose MySQL ODBC 5.1 Driver and then press the “Finish” button.
  • After that, a MySQL connection configuration dialog will appear. Add your MySQL database account information in it, preferably the “root” account which has full access to your databases in MySQL. Do not change the port to anything other than 3306, unless during your MySQL server installation, you have defined something else.
  • Press the “Test” button to ensure your connection settings are set properly and then the “OK” button when you’re done.
  • In this state, you are ready to establish a link towards MySQL database from your Microsoft SQL Server Management Studio. Open a query window and run the following SQL statement:

EXEC master.dbo.sp_addlinkedserver
@server = N’MYSQL’,
@srvproduct=N’MySQL’,
@provider=N’MSDASQL’,
@provstr=N’DRIVER={MySQL ODBC 5.1 Driver}; SERVER=my.server.ip.or.name; _
DATABASE=databasename; USER=username; PASSWORD=password; OPTION=3′

  • In the query window, run the following SQL statement to import table tablename from the MySQL database databasename, into the database in Microsoft SQL called SQLServerdatabasename.

SELECT * INTO SQLServerdatabasename.dbo.tablename
FROM openquery(MYSQL, ‘SELECT * FROM databasename.tablename’)

The potential for ODBC based migration of data is brilliant and openquery can be scripted to run across all the database tables. I am currently looking at a way of bulk importing an entire database from MySQL to SQL Server and will update this post in the future.

There are a few things to be wary of, such as keys, data types, creation of identity specifications etc.. but this can all be sorted once the data is migrated across. The speed is pretty good and shouldn’t be too much of a concern as this is a one time operation.

Running Microsoft SQL Server SQL scripts with sqlcmd.exe from the command line

I needed to import a large amount of data in the form of a .sql script file, auto generated elsewhere, into my SQL Server 2005 Express database. The command was run on the localhost of the database and is actually really easy. sqlcmd.exe needs to be in your PATH and it is by default when you install SQL Server. I was also running this as an administrator with database permissions, although you can enter specific permissions manually if you need to:

sqlcmd -S SERVERNAMESQLEXPRESS -d DatabaseName -i c:scriptname.sql

 

Connecting to SQL Server from C++

Despite being quite a basic requirement, it was very difficult to find anything solid on connecting to a SQL database without using CLR or 3rd party libraries in visual C++. This code was tested and works when connecting to a SQL Server Express 2005 server in Visual Studio 2010 with a win32 console project using visual C++. Thanks to Tidy Tutorials, who look to have scalped it from MSDN (although I couldn’t find it anywhere).

#include <iostream>
#include <windows.h>
#include <sqltypes.h>
#include <sql.h>
#include <sqlext.h>

using namespace std;

void show_error(unsigned int handletype, const SQLHANDLE& handle){
    SQLCHAR sqlstate[1024];
    SQLCHAR message[1024];
    if(SQL_SUCCESS == SQLGetDiagRec(handletype, handle, 1, sqlstate, NULL, message, 1024, NULL))
        cout<<"Message: "<<message<<"nSQLSTATE: "<<sqlstate<<endl;
}

int main(){

    SQLHANDLE sqlenvhandle;    
    SQLHANDLE sqlconnectionhandle;
    SQLHANDLE sqlstatementhandle;
    SQLRETURN retcode;

    if(SQL_SUCCESS!=SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &sqlenvhandle))
        goto FINISHED;

    if(SQL_SUCCESS!=SQLSetEnvAttr(sqlenvhandle,SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0)) 
        goto FINISHED;
    
    if(SQL_SUCCESS!=SQLAllocHandle(SQL_HANDLE_DBC, sqlenvhandle, &sqlconnectionhandle))
        goto FINISHED;

    SQLCHAR retconstring[1024];
    switch(SQLDriverConnect (sqlconnectionhandle, 
                NULL, 
                (SQLCHAR*)"DRIVER={SQL Server};SERVER=localhost, 1433;DATABASE=MyDatabase;UID=sa;PWD=Admin-123;", 
                SQL_NTS, 
                retconstring, 
                1024, 
                NULL,
                SQL_DRIVER_NOPROMPT)){
        case SQL_SUCCESS_WITH_INFO:
            show_error(SQL_HANDLE_DBC, sqlconnectionhandle);
            break;
        case SQL_INVALID_HANDLE:
        case SQL_ERROR:
            show_error(SQL_HANDLE_DBC, sqlconnectionhandle);
            goto FINISHED;
        default:
            break;
    }
    
    if(SQL_SUCCESS!=SQLAllocHandle(SQL_HANDLE_STMT, sqlconnectionhandle, &sqlstatementhandle))
        goto FINISHED;

    if(SQL_SUCCESS!=SQLExecDirect(sqlstatementhandle, (SQLCHAR*)"select * from testtable", SQL_NTS)){
        show_error(SQL_HANDLE_STMT, sqlstatementhandle);
        goto FINISHED;
    }
    else{
        char name[64];
        char address[64];
        int id;
        while(SQLFetch(sqlstatementhandle)==SQL_SUCCESS){
            SQLGetData(sqlstatementhandle, 1, SQL_C_ULONG, &id, 0, NULL);
            SQLGetData(sqlstatementhandle, 2, SQL_C_CHAR, name, 64, NULL);
            SQLGetData(sqlstatementhandle, 3, SQL_C_CHAR, address, 64, NULL);
            cout<<id<<" "<<name<<" "<<address<<endl;
        }
    }

FINISHED:
    SQLFreeHandle(SQL_HANDLE_STMT, sqlstatementhandle );
    SQLDisconnect(sqlconnectionhandle);
    SQLFreeHandle(SQL_HANDLE_DBC, sqlconnectionhandle);
    SQLFreeHandle(SQL_HANDLE_ENV, sqlenvhandle);
    
}

Finding the size of each table in a SQL Server database

I needed to work out how to get the size of each table in a DotNetNuke database to work out why the DB was 3.9gb in size. I found a stored procedure from The Right Stuff which gives me the details on the database and each table within it using the undocumented sp_msForEachTable stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE checksize
AS
BEGIN

SET NOCOUNT ON

DBCC UPDATEUSAGE(0)

-- DB size.
EXEC sp_spaceused

-- Table row counts and sizes.
CREATE TABLE #t
(
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
)

INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

SELECT *
FROM   #t

-- # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM   #t

DROP TABLE #t

END
GO

With this I could see that the DotNetNuke search cache was getting massive as the SearchItemWord and SearchItemWordPosition were both huge.

Sharepoint indexing and 100% cpu sqlservr.exe

Turns out indexing for sharepoint search was set to reindex every 5 minutes, causing 100% cpu on sqlservr.exe. This in turn slowed down the sharepoint portal significantly.

To set reindexing of sharepoint search to a more sensible time (out of hours) open up sharepoint central administration on your sharepoint server. In “Farm Topology” click on the server name that is running “Windows SharePoint Services Search”. Now click on “Windows SharePoint Services Search” in the list that pops up. Now go to the “Indexing Schedule” section and set the indexing schedule to something sensible, like daily between 12am and 12:15am (or whenever the server is not being heavily used).

Using ExpressMaint to Backup SQL Server Express 2005 Databases

You can automatically back up all user databases in your SQL Server Express 2005 install using either a stored procedure or command line program combined with windows task scheduler. ExpressMaint was written to bring some of the backup functionality from SQL Server 2000 to SQL Server Express 2005 and seems to be pretty easy to use based on the instructions at sqldbatips.com.

Combining regular backups using ExpressMaint with some shadow copying of the data files provides a fair bit of redundacy in the event of hardware failure, database corruption etc..