Monday, June 8, 2015

TSQL Cocktail for DBA's

I was doing a MSSQL migration to new server. In my script directory there many scripts that either I wrote or download from internet.These scripts help me alot in smooth migration and I believe it will be helpful for you as well. I don't have original author name for scripts, I downloaded from internet, but I would like to thank them all for all the great work :)

These scripts can work on MSSQL 2005 or above.


--============================================
-- Sql Instance Startup datetime
--============================================


SELECT crdate AS SQLStarted
  FROM master..sysdatabases
 WHERE name = 'tempdb';

--=====================================================
-- Database Last Accessed date time and Size
--=====================================================

SELECT DatabaseName, MAX(LastAccessDate) LastAccessDate,
            case when (datediff(month,MAX(LastAccessDate),getdate())) > 1
            then 'OneMonth'
            Else 'Less Than A month'
            end as TimeDecision
  into #tmp                                                
FROM
    (SELECT

        DB_NAME(database_id) DatabaseName

        , last_user_seek

        , last_user_scan

        , last_user_lookup

        , last_user_update

    FROM sys.dm_db_index_usage_stats) AS PivotTable

UNPIVOT

    (LastAccessDate FOR last_user_access IN

        (last_user_seek

        , last_user_scan

        , last_user_lookup

        , last_user_update)

    ) AS UnpivotTable

GROUP BY DatabaseName
HAVING DatabaseName NOT IN ('master', 'tempdb', 'model', 'msdb')
ORDER BY 2;

--
SELECT d.name,tmp.LastAccessDate,
ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
left join #tmp tmp
on d.name = tmp.DatabaseName
WHERE d.database_id > 4 -- Skip system databases
GROUP BY d.name,tmp.LastAccessDate
ORDER BY tmp.LastAccessDate desc
--
drop table #tmp
---==============================================================
-- Database Connection Details
--===============================================================
-- MSSQL 2012-2014
SELECT
    source_IP = dec.client_net_address,
    source_Machine = des.host_name,
    Database_Name = DB_NAME(des.database_id),
    des.login_name,
    Connection_Time = des.login_time
FROM sys.dm_exec_sessions des
INNER JOIN sys.dm_exec_connections dec
    ON dec.session_id = des.session_id
where DB_NAME(des.database_id) not in ('msdb','temp','master');


--MSSQL 2008
SELECT
    source_IP = dec.client_net_address,
    source_Machine = des.host_name,
    Database_Name = db_name(sp.dbid),
    des.login_name,
    CLIENT_INTERFACE_NAME,
    Connection_Time = des.login_time

FROM sys.dm_exec_sessions des
INNER JOIN sys.dm_exec_connections dec
    ON dec.session_id = des.session_id
INNER join sys.sysprocesses sp
        on des.session_id = sp.spid
where db_name(sp.dbid) not in ('msdb','temp','master','model');


--=====================================================================
-- SQL Account last accessed/Create datetime
--=====================================================================

select max (login_time)as last_login_time, login_name from sys.dm_exec_sessions
group by login_name;

 select name, accdate as Acc_CreateDate
   FROM sys.syslogins
  WHERE name not like '%##%'
order by accdate desc --and type_desc = 'SQL_LOGIN';
 --===================================================================
-- Extract Database User Creation Script
--===================================================================
/*

    There is a user-defined function that’s created in master.  You can create it in any database you want but you’ll need to update the script.  The function converts varbinary hashed passwords to a string representation.
    It keeps the password intact for SQL Server logins.
    It scripts both Windows logins and SQL Server logins.  It also scripts role membership.
    It keeps the SID intact for SQL Server logins.  This is important so you don’t have to remap users to logins.
    The script that is generated uses IF NOT EXISTS so that it doesn’t try to create logins that already exist.
    It DOES NOT handle removal of logins from roles.  It does handle disabled accounts but I haven’t done much testing on that.
    I’ve tested this on SQL Server 2005 and SQL Server 2008.
    You’ll probably need to change your results so that you display more characters by default.  Under Tools –> Options –> Query Results –> SQL Server –> Results to Text increase the maximum number of characters returned to 8192 (or a number high enough that the results aren’t truncated).  You’ll want to set results to text before running this.


*/

--execute sp_help_revlogin
USE [master]
GO

/****** Object:  UserDefinedFunction [dbo].[fn_hexadecimal]    ****/
SET ANSI_NULLS ON
GO
--drop FUNCTION fn_hexadecimal;

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fn_hexadecimal]
(
    -- Add the parameters for the function here
     @binvalue varbinary(256)
)
RETURNS VARCHAR(256)
AS
BEGIN

    DECLARE @charvalue varchar(256)
    DECLARE @i int
    DECLARE @length int
    DECLARE @hexstring char(16)
    SELECT @charvalue = '0x'
    SELECT @i = 1
    SELECT @length = DATALENGTH (@binvalue)
    SELECT @hexstring = '0123456789ABCDEF'
    WHILE (@i <= @length)
    BEGIN
      DECLARE @tempint int
      DECLARE @firstint int
      DECLARE @secondint int
      SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
      SELECT @firstint = FLOOR(@tempint/16)
      SELECT @secondint = @tempint - (@firstint*16)
      SELECT @charvalue = @charvalue +
        SUBSTRING(@hexstring, @firstint+1, 1) +
        SUBSTRING(@hexstring, @secondint+1, 1)
      SELECT @i = @i + 1
    END
    return @charvalue

END
GO


SET NOCOUNT ON
GO
--use MASTER
GO
PRINT '-----------------------------------------------------------------------------'
PRINT '-- Script created on ' + CAST(GETDATE() AS VARCHAR(100))
PRINT '-----------------------------------------------------------------------------'
PRINT ''
/*PRINT '-----------------------------------------------------------------------------'
PRINT '-- Create the windows logins'
PRINT '-----------------------------------------------------------------------------'
SELECT 'IF NOT EXISTS (SELECT * FROM master.sys.server_principals WHERE [name] = ''' + [name] + ''')
    CREATE LOGIN [' + [name] + '] FROM WINDOWS WITH DEFAULT_DATABASE=[' +
        default_database_name + '], DEFAULT_LANGUAGE=[us_english]
GO

'
FROM master.sys.server_principals
where type_desc In ('WINDOWS_GROUP', 'WINDOWS_LOGIN')
AND [name] not like 'BUILTIN%'
and [NAME] not like 'NT AUTHORITY%'
and [name] not like '%\SQLServer%'
GO
*/
PRINT '-----------------------------------------------------------------------------'
PRINT '-- Create the SQL Logins'
PRINT '-----------------------------------------------------------------------------'
select 'IF NOT EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ''' + [name] + ''')
    CREATE LOGIN [' + [name] + ']
        WITH PASSWORD=' + [master].[dbo].[fn_hexadecimal](password_hash) + ' HASHED,
        SID = ' + [master].[dbo].[fn_hexadecimal]([sid]) + ', 
        DEFAULT_DATABASE=[' + default_database_name + '], DEFAULT_LANGUAGE=[us_english],
        CHECK_EXPIRATION=' + CASE WHEN is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECK_POLICY=OFF
GO
IF EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ''' + [name] + ''')
    ALTER LOGIN [' + [name] + ']
        WITH CHECK_EXPIRATION=' +
            CASE WHEN is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECK_POLICY=' +
            CASE WHEN is_policy_checked = 1 THEN 'ON' ELSE 'OFF' END + '
GO


' as SqlLogins
--[name], [sid] , password_hash
from master.sys.sql_logins
where type_desc = 'SQL_LOGIN'
and [name] not in ('sa', 'guest')

PRINT '-----------------------------------------------------------------------------'
PRINT '-- Disable any logins'
PRINT '-----------------------------------------------------------------------------'
SELECT 'ALTER LOGIN [' + [name] + '] DISABLE
GO
' as DisableUser
from master.sys.server_principals
where is_disabled = 1

PRINT '-----------------------------------------------------------------------------'
PRINT '-- Assign groups'
PRINT '-----------------------------------------------------------------------------'
select
'EXEC master..sp_addsrvrolemember @loginame = N''' + l.name + ''', @rolename = N''' + r.name + '''
GO

' as AssignGroups
from master.sys.server_role_members rm
join master.sys.server_principals r on r.principal_id = rm.role_principal_id
join master.sys.server_principals l on l.principal_id = rm.member_principal_id
where l.[name] not in ('sa')
AND l.[name] not like 'BUILTIN%'
and l.[NAME] not like 'NT AUTHORITY%'
and l.[name] not like '%\SQLServer%'
 --==================================================================
-- Extract Current Email Configurations on Server
--==================================================================
--Author Lowell
USE msdb
GO

Declare @TheResults varchar(max),
        @vbCrLf CHAR(2)
SET @vbCrLf = CHAR(13) + CHAR(10)       
SET @TheResults = '
use master
go
sp_configure ''show advanced options'',1
go
reconfigure with override
go
sp_configure ''Database Mail XPs'',1
--go
--sp_configure ''SQL Mail XPs'',0
go
reconfigure
go
'
SELECT @TheResults = @TheResults  + '
--#################################################################################################
-- BEGIN Mail Settings ' + p.name + '
--#################################################################################################
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE  name = ''' + p.name + ''')
  BEGIN
    --CREATE Profile [' + p.name + ']
    EXECUTE msdb.dbo.sysmail_add_profile_sp
      @profile_name = ''' + p.name + ''',
      @description  = ''' + ISNULL(p.description,'') + ''';
  END --IF EXISTS profile
  '
  +
  '
  IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE  name = ''' + a.name + ''')
  BEGIN
    --CREATE Account [' + a.name + ']
    EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name            = ' + CASE WHEN a.name                IS NULL THEN ' NULL ' ELSE + '''' + a.name                  + '''' END + ',
    @email_address           = ' + CASE WHEN a.email_address       IS NULL THEN ' NULL ' ELSE + '''' + a.email_address         + '''' END + ',
    @display_name            = ' + CASE WHEN a.display_name        IS NULL THEN ' NULL ' ELSE + '''' + a.display_name          + '''' END + ',
    @replyto_address         = ' + CASE WHEN a.replyto_address     IS NULL THEN ' NULL ' ELSE + '''' + a.replyto_address       + '''' END + ',
    @description             = ' + CASE WHEN a.description         IS NULL THEN ' NULL ' ELSE + '''' + a.description           + '''' END + ',
    @mailserver_name         = ' + CASE WHEN s.servername          IS NULL THEN ' NULL ' ELSE + '''' + s.servername            + '''' END + ',
    @mailserver_type         = ' + CASE WHEN s.servertype          IS NULL THEN ' NULL ' ELSE + '''' + s.servertype            + '''' END + ',
    @port                    = ' + CASE WHEN s.port                IS NULL THEN ' NULL ' ELSE + '''' + CONVERT(VARCHAR,s.port) + '''' END + ',
    @username                = ' + CASE WHEN c.credential_identity IS NULL THEN ' NULL ' ELSE + '''' + c.credential_identity   + '''' END + ',
    @password                = ' + CASE WHEN c.credential_identity IS NULL THEN ' NULL ' ELSE + '''NotTheRealPassword''' END + ',
    @use_default_credentials = ' + CASE WHEN s.use_default_credentials = 1 THEN ' 1 ' ELSE ' 0 ' END + ',
    @enable_ssl              = ' + CASE WHEN s.enable_ssl = 1              THEN ' 1 ' ELSE ' 0 ' END + ';
  END --IF EXISTS  account
  '
  + '
IF NOT EXISTS(SELECT *
              FROM msdb.dbo.sysmail_profileaccount pa
                INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
                INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id 
              WHERE p.name = ''' + p.name + '''
                AND a.name = ''' + a.name + ''')
  BEGIN
    -- Associate Account [' + a.name + '] to Profile [' + p.name + ']
    EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
      @profile_name = ''' + p.name + ''',
      @account_name = ''' + a.name + ''',
      @sequence_number = ' + CONVERT(VARCHAR,pa.sequence_number) + ' ;
  END --IF EXISTS associate accounts to profiles
--#################################################################################################
-- Drop Settings For ' + p.name + '
--#################################################################################################
/*
IF EXISTS(SELECT *
            FROM msdb.dbo.sysmail_profileaccount pa
              INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
              INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id 
            WHERE p.name = ''' + p.name + '''
              AND a.name = ''' + a.name + ''')
  BEGIN
    EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = ''' + p.name + ''',@account_name = ''' + a.name + '''
  END
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE  name = ''' + a.name + ''')
  BEGIN
    EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = ''' + a.name + '''
  END
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE  name = ''' + p.name + ''')
  BEGIN
    EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = ''' + p.name + '''
  END
*/
  '
FROM msdb.dbo.sysmail_profile p
INNER JOIN msdb.dbo.sysmail_profileaccount pa ON  p.profile_id = pa.profile_id
INNER JOIN msdb.dbo.sysmail_account a         ON pa.account_id = a.account_id
LEFT OUTER JOIN msdb.dbo.sysmail_server s     ON a.account_id = s.account_id
LEFT OUTER JOIN sys.credentials c    ON s.credential_id = c.credential_id

   ;WITH E01(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL 
                    SELECT 1 UNION ALL SELECT 1 UNION ALL 
                    SELECT 1 UNION ALL SELECT 1 UNION ALL 
                    SELECT 1 UNION ALL SELECT 1 UNION ALL 
                    SELECT 1 UNION ALL SELECT 1), --         10 or 10E01 rows 
         E02(N) AS (SELECT 1 FROM E01 a, E01 b),  --        100 or 10E02 rows 
         E04(N) AS (SELECT 1 FROM E02 a, E02 b),  --     10,000 or 10E04 rows 
         E08(N) AS (SELECT 1 FROM E04 a, E04 b),  --100,000,000 or 10E08 rows 
         --E16(N) AS (SELECT 1 FROM E08 a, E08 b),  --10E16 or more rows than you'll EVER need, 
         Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E08), 
       ItemSplit( 
                 ItemOrder, 
                 Item 
                ) as ( 
                      SELECT N, 
                        SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,N + DATALENGTH(@vbCrLf),CHARINDEX(@vbCrLf,@vbCrLf + @TheResults + @vbCrLf,N + DATALENGTH(@vbCrLf)) - N - DATALENGTH(@vbCrLf)) 
                      FROM Tally 
                      WHERE N < DATALENGTH(@vbCrLf + @TheResults) 
                      --WHERE N < DATALENGTH(@vbCrLf + @INPUT) -- REMOVED added @vbCrLf 
                        AND SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,N,DATALENGTH(@vbCrLf)) = @vbCrLf --Notice how we find the delimiter 
                     ) 
  select 
    row_number() over (order by ItemOrder) as ItemID, 
    Item 
  from ItemSplit  ;
--===================================================================
-- Sql Job History
--==================================================================

SELECT
    --[sJOB].[job_id] AS [JobID]
     [sJOB].[name] AS [JobName]
    , CASE [sJOB].[enabled]
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
      END AS [IsEnabled]
    , CASE
        WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL
        ELSE CAST(
                CAST([sJOBH].[run_date] AS CHAR(8))
                + ' '
                + STUFF(
                    STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)),  6)
                        , 3, 0, ':')
                    , 6, 0, ':')
                AS DATETIME)
      END AS [LastRunDateTime]
    , CASE [sJOBH].[run_status]
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Canceled'
        WHEN 4 THEN 'Running' -- In Progress
      END AS [LastRunStatus]
    , STUFF(
            STUFF(RIGHT('000000' + CAST([sJOBH].[run_duration] AS VARCHAR(6)),  6)
                , 3, 0, ':')
            , 6, 0, ':')
        AS [LastRunDuration (HH:MM:SS)]
    , [sJOBH].[message] AS [LastRunStatusMessage]
    , CASE [sJOBSCH].[NextRunDate]
        WHEN 0 THEN NULL
        ELSE CAST(
                CAST([sJOBSCH].[NextRunDate] AS CHAR(8))
                + ' '
                + STUFF(
                    STUFF(RIGHT('000000' + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)),  6)
                        , 3, 0, ':')
                    , 6, 0, ':')
                AS DATETIME)
      END AS [NextRunDateTime]
FROM
    [msdb].[dbo].[sysjobs] AS [sJOB]
    LEFT JOIN (
                SELECT
                    [job_id]
                    , MIN([next_run_date]) AS [NextRunDate]
                    , MIN([next_run_time]) AS [NextRunTime]
                FROM [msdb].[dbo].[sysjobschedules]
                GROUP BY [job_id]
            ) AS [sJOBSCH]
        ON [sJOB].[job_id] = [sJOBSCH].[job_id]
    LEFT JOIN (
                SELECT
                    [job_id]
                    , [run_date]
                    , [run_time]
                    , [run_status]
                    , [run_duration]
                    , [message]
                    , ROW_NUMBER() OVER (
                                            PARTITION BY [job_id]
                                            ORDER BY [run_date] DESC, [run_time] DESC
                      ) AS RowNumber
                FROM [msdb].[dbo].[sysjobhistory]
                WHERE [step_id] = 0
            ) AS [sJOBH]
        ON [sJOB].[job_id] = [sJOBH].[job_id]
        AND [sJOBH].[RowNumber] = 1
ORDER BY [JobName];

--==============================================================================
-- Sql Jobs's Steps details
--=========================================================================
=====
SELECT
    [sJOB].[job_id] AS [JobID]
    , [sJOB].[name] AS [JobName]
    , [sJSTP].[step_uid] AS [StepID]
    , [sJSTP].[step_id] AS [StepNo]
    , [sJSTP].[step_name] AS [StepName]
    , CASE [sJSTP].[subsystem]
        WHEN 'ActiveScripting' THEN 'ActiveX Script'
        WHEN 'CmdExec' THEN 'Operating system (CmdExec)'
        WHEN 'PowerShell' THEN 'PowerShell'
        WHEN 'Distribution' THEN 'Replication Distributor'
        WHEN 'Merge' THEN 'Replication Merge'
        WHEN 'QueueReader' THEN 'Replication Queue Reader'
        WHEN 'Snapshot' THEN 'Replication Snapshot'
        WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'
        WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'
        WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'
        WHEN 'SSIS' THEN 'SQL Server Integration Services Package'
        WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'
        ELSE sJSTP.subsystem
      END AS [StepType]
    , [sPROX].[name] AS [RunAs]
    , [sJSTP].[database_name] AS [Database]
    , [sJSTP].[command] AS [ExecutableCommand]
    , CASE [sJSTP].[on_success_action]
        WHEN 1 THEN 'Quit the job reporting success'
        WHEN 2 THEN 'Quit the job reporting failure'
        WHEN 3 THEN 'Go to the next step'
        WHEN 4 THEN 'Go to Step: '
                    + QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3)))
                    + ' '
                    + [sOSSTP].[step_name]
      END AS [OnSuccessAction]
    , [sJSTP].[retry_attempts] AS [RetryAttempts]
    , [sJSTP].[retry_interval] AS [RetryInterval (Minutes)]
    , CASE [sJSTP].[on_fail_action]
        WHEN 1 THEN 'Quit the job reporting success'
        WHEN 2 THEN 'Quit the job reporting failure'
        WHEN 3 THEN 'Go to the next step'
        WHEN 4 THEN 'Go to Step: '
                    + QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3)))
                    + ' '
                    + [sOFSTP].[step_name]
      END AS [OnFailureAction]
FROM
    [msdb].[dbo].[sysjobsteps] AS [sJSTP]
    INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]
        ON [sJSTP].[job_id] = [sJOB].[job_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP]
        ON [sJSTP].[job_id] = [sOSSTP].[job_id]
        AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP]
        ON [sJSTP].[job_id] = [sOFSTP].[job_id]
        AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]
    LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX]
        ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]
ORDER BY [JobName], [StepNo];





Wednesday, January 28, 2015

Access Remote MySql Data

I was working on a task where I need to write ETL between 2 mysql servers. I was thinking about MSSQL linkedserver nd Oracel DbLink. But  nothing like that exist in Mysql. :(

Mysql offer such connection with Federated Storage option. It has pros and cons and one should use it carefully. Baron Schwartz wrote a worth reading article on this subject.You should read this for in depth understanding.

My objective today is to communicate a step-by-step guide to save your time and to utilize your time more effectively :)

Remote server refers to the instance where I want to read data, Local server is machine where I want to use remote data.


#########################################################
## Enable Federated Storage
#########################################################
Federated storage should be enable on both remote and local server.

# Verify whether Federated engine is enable or not

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)


As it is not enable. We need to enable it first.

#Enable Federated Storage
Edit your /etc/my.cnf global server configuration file and under [mysqld] stanza section, add the line:

federated

#Verify if it is enable
mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | YES      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)



################################################
# CREATE AND POPULATE TABLE ON REMOTE SERVER
################################################
USE test;

CREATE TABLE tbl_remote(
   id INT NOT NULL PRIMARY KEY,
   num INT NOT NULL
);

INSERT INTO tbl_remote
SELECT 1,1 UNION ALL
SELECT 2,1 UNION ALL
SELECT 3,2 UNION ALL
SELECT 4,2 UNION ALL
SELECT 5,3 ;

SELECT * FROM tbl_remote;

################################################
# CREATE FEDERATED TABLE ON LOCAL SERVER
################################################
Table creation syntax is same except following. Once you have mentioned those, you can select remote server data.
ENGINE=FEDERATED
CONNECTION='mysql://user:pwd@Remote-IP/remote-db-name/remote-table-name';

USE test;

CREATE TABLE tbl_local (
 id INT NOT NULL PRIMARY KEY,
 num INT NOT NULL
) ENGINE=FEDERATED
 CONNECTION='mysql://user:pwd@Remote-IP/remote-db-name/remote-table-name';

mysql > SELECT * FROM tbl_local;
+----+-----+
| id | num |
+----+-----+
|  1 |   1 |
|  2 |   1 |
|  3 |   2 |
|  4 |   2 |
|  5 |   3 |
+----+-----+
5 rows in set (0.00 sec)

You can join remote table to local table(s) for any operation.

 Enjoy!