How to Create Linked Server between MYSQL and SQL Server

Steps to configure Linked Server

Step 1:

• Install the My SQL OBDC connector on the SQL Server machine (This will come default if you have already installed My SQL in the SQL Server machine. The setup file for the drive is attached with in the mail.
• Create an ODBC System DSN with the name “MYSQL” using the driver installed on the previous step as shown in the below image

 Step 2:

Create a linked server between the SQL Server and the My SQL Server using the steps given below.

• Connect to the SQL Server instance and expand to the section Linked Servers 
• Right click on the Linked Server and select the option “New Linked Server”

• Specify the connection details to the ODBC driver as shown below

Details

Linked Server : MYSQL
Provider : Microsoft OLE DB provider for ODBC drivers
Product Name : MYSQL
Data Source : MYSQL
Provider String : DRIVER={MySQL ODBC 5.1 Driver};SERVER=SAMSUDEEN;PORT=3306;DATABASE=salem_dbo;USER=root;PASSWORD=password
Catalog : salem_dbo

• Go to the Security Tab and give tab and give the MY SQL username & password under the option “Be made using this security context”
• Go to the Server options and set value true for RPC & RPC Out properties.

Step 3:

• Click OK to create the linked server. You can test the connection using the test connection option as shown below

How to run a SQL batch multiple times

Today I came across one surprising use of GO statement in SQL Server which is solely used as batch terminator in SQL Server utilities such as SQL Server Management Studio etc. Though “GO” is not a Transact –SQL statement it can be used in multiple ways .One such example is to run a batch of statements as illustrated below

CREATE TABLE TestData ( Name VARCHAR(100), Age INT)
GO
Insert into TestData
SELECT 'John' ,28
GO 100

In the above example I have inserted 100 rows of test data by just specifying 100 next to the GO statement. All these days I have used the traditional while loop statement for doing this, now just specify the number next to GO statment the job is done. This feature is no where documented and supported only in SQL Server 2005 and greater versions

How to recover a Database in suspect mode

I was struck with a database which is on suspect mode and need to recover it without any recent backups. I searched across the net and found these steps which are quite useful

Step 1:
Clear the suspect mode of the database using sp_resetstatus DatabaseName. This will clear the suspect flag and make the database available online

Step 2:
Change the database status to Emergency using the following command. Emergency mode allows you to access the databases as normal but with no consistency guarantee. This option also allows us to export the table data so that we can minimize the damage.
ALTER DATABASE DatabaseName SET EMERGENCY;

Step 3:
Restrict database to single user by changing the access mode as mentioned below
ALTER DATABASE DatabaseName SET SINGLE_USER;

Step 4:
Run the CHECKDB command with “REPAIR_ALLOW_DATA_LOSS” option. This option should be tried as last option as it always behaves the way it is named. We are not sure of what data it removes.
DBCC CHECKDB (DatabaseName, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

There are some best (simple) practices which prevents us from such failures. Below are some of them

• Backup your data frequently ( daily or once in two days)
• Have multiple backups. Move the backups to external drives or tapes frequently
• Validate that your backups are good by doing trial restores to alternate server
• Run CHECKDB regularly