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!

Friday, October 3, 2014

MYSql : Migrate Data in Big Table

Few days back our client communicated that their archive tables on MYSQL instance are consuming a lot of disk space (> 50GB >10000000 rows)  and half of the data is no longer required.

We have to clean the data and schedule regular removal on datetime basis and we cannot change any configuration on server.

It was risky on production to execute direct DML  :( CTAS was an option too but when we compare with volume of data, it takes a lot time and server load.

MYSql supports bulk data operation with file very effectively.I have used that path and it was successful.

Following I have described that scenario for viewers. You can set your filter criteria and indexes accordingly.

Have a nice weekend :)

-- Create test table
CREATE TABLE `migtest` (
  `Id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) DEFAULT NULL,
  KEY `Id` (`Id`)
);

-- Create procedure to Load test data
DELIMITER #
CREATE PROCEDURE Load_Test()
BEGIN
  DECLARE v1 INT DEFAULT 0;

  WHILE v1 < 1000001 DO
    INSERT INTO migtest (NAME) VALUES (CONCAT('John',v1));
    SET v1=v1+1;
   
    IF MOD(v1,5000) = 0 THEN -- Commit every 5000 records
      COMMIT;
    END IF;
  END WHILE;
 END #

 -- Call stored porc to laod data
 CALL Load_Test();

 -- counting rows
 SELECT COUNT(*) FROM migtest;

 -- Creating New host table for data
 CREATE TABLE `migtest_b` (
  `Id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) DEFAULT NULL,
  KEY `Id` (`Id`));
 
  -- Exporting data to file.
  -- I am using limit to transfer records. You can use your criteria to export records
  SELECT * FROM migtest
-- WHERE  Your criteria
LIMIT 500000
INTO OUTFILE 'F:\\lg_test\\logs.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"' LINES TERMINATED BY '\n';

SELECT '=============== importing File =======================================';   
# Populating new table
LOAD DATA INFILE 'F:\\lg_test\\logs.csv'  INTO TABLE migtest_b
FIELDS TERMINATED BY ','
ENCLOSED BY '"' LINES TERMINATED BY '\n';

 -- counting rows
 SELECT COUNT(*) FROM migtest_b;

-- Table Operations
RENAME TABLE migtest TO migtest_del;
RENAME TABLE migtest_b TO migtest;
DROP TABLE migtest_del;

Monday, September 8, 2014

Oracle : Enable Default Constraint on Table with Millions Records

Recently I got request from development team to add a  new column to an existing table with default value of sysdate.
This table has over 88 million records. if I add default constraint to that table directly, it will acquire exclusive table
lock on table and table is not be acessible for any activity during this operation.
Update this much records can take day(s) to complete, so we can't do this directly on production environment.

Following can be our checklist
1. Add column to table without default constraint
2- backfill new column
3- Enable default on new column

I have developed logic to select a chuck of record and update them.even this chunk can cause transactional locks, os I have
added logic to commit every 5000 records. Now I am in situation to execute it on any environment.




we can use CTAS as an alternate option, I will cover that in my next post.


sqlplus user/pwd;
-----------------------------------------
--Creating test table
-----------------------------------------

CREATE TABLE MyUpdtest
  ( id NUMBER,
    name varchar2(100)
  );
 

-----------------------------------------
--Populating test table
-----------------------------------------
declare
varcount number := 0;
BEGIN
  FOR t IN 1..4000000
  LOOP
  INSERT INTO MyUpdtest
  VALUES (t, 'Employee'||t );
  varcount := varCount+1;
  IF mod(varCount, 5000) = 0 THEN -- Commit every 5000 records
    COMMIT;
  END IF;
END LOOP;
END;
/

-----------------------------------------
--1- Add new column
-----------------------------------------

ALTER TABLE MyUpdtest ADD CreateDate DATE;

-----------------------------------------
--2- backfill new column
-----------------------------------------
/*
Following is the core script to update records.It is suggested to transform it in shell script and schedule to execute in
off-peak hours.
*/
spool on
spool '/loc/upd.txt' -- set spool location accordingly

SELECT TO_CHAR(sysdate,'dd/mm/yyyy HH:MM:SS') AS StartTime FROM dual;

select count(*) as totalRowsToProcess  from MyUpdtest where CreateDate is null;
declare
varcount number := 0;
varcount2 number := 0;
varDte date;
begin
--setting counter to set loop condition
SELECT COUNT(*) INTO varcount2 FROM MyUpdtest WHERE CreateDate IS NULL;
-- setting current date portion only
SELECT to_date(TO_CHAR(sysdate,'dd/mm/yyyy'),'dd/mm/yyyy')
  INTO varDte
  FROM dual;
WHILE varcount2 > 0 --quits if 0 or less
LOOP                -- while loop start
  FOR r IN
        (SELECT ID,
          Name
        FROM MyUpdtest
        WHERE CreateDate IS NULL
        AND rownum        <200001
        ) --limitint batch to 200000
  LOOP
    UPDATE MyUpdtest
    SET CreateDate         = varDte
    WHERE Id             = r.id
    AND name           =r.name;
    varcount              := varCount+1;
    IF mod(varCount, 5000) = 0 THEN -- Commit every 5000 records
      COMMIT;
    END IF;
  END LOOP;-- for loop ends
  COMMIT;
  varcount2:=varCount2-200000;
END LOOP; --ends while loop
END;
/
select to_char(sysdate,'dd/mm/yyyy HH:MM:SS') as EndTime from dual;
spool off

-----------------------------------------
--3- Enable Default
-----------------------------------------
ALTER TABLE MyUpdtest MODIFY CreateDate DEFAULT sysdate;

Monday, July 7, 2014

MSSQL TSql Interview Questions

There may be other ways too to solve same question. I am posting one I know

--=================================================================
--- Creating Temp Table
--=================================================================
CREATE TABLE #tmpEmpSal
(
Ename VARCHAR(20),
Dept  VARCHAR(10),
Salary INT
);
--=================================================================
--Populating temp table
--=================================================================
INSERT INTO #tmpEmpSal
SELECT 'Emp1','Dept1',10000 UNION ALL 
SELECT 'Emp2','Dept1',1000 UNION ALL
SELECT 'Emp3','Dept1',12000 UNION ALL
SELECT 'Emp4','Dept1',13000 UNION ALL
SELECT 'Emp5','Dept2',10000 UNION ALL
SELECT 'Emp6','Dept2',5000 UNION ALL
SELECT 'Emp7','Dept2',12000 UNION ALL
SELECT 'Emp8','Dept3',11000 UNION ALL
SELECT 'Emp9','Dept3',10000 UNION ALL
SELECT 'Emp10','Dept3',10000 UNION ALL
SELECT 'Emp11','Dept3',4000 UNION ALL
SELECT 'Emp14','Dept3',14000 UNION ALL
SELECT 'Emp12','Dept4',70000 UNION ALL
SELECT 'Emp13','Dept4',10000 ;

--=================================================================
-- Select lowest salary in Each department
--=================================================================
WITH myCte AS
(
SELECT
        dept,
        salary,
        ROW_NUMBER() OVER ( PARTITION BY dept ORDER BY salary asc ) AS RowNum
FROM    #tmpEmpSal
)
SELECT dept,
       salary
  FROM myCte
 WHERE RowNum=1;

--====================================
--- select nth highest record
--====================================

--Frist select all distinct salary to see nth number record
SELECT  DISTINCT salary
  FROM #tmpEmpSal
 ORDER BY Salary DESC;

--Query to fetch nth record
SELECT MIN(salary)
  FROM #tmpEmpSal
 WHERE Salary IN (
        SELECT  DISTINCT TOP 6  Salary
          FROM #tmpEmpSal --replace top to required nth position
         ORDER BY Salary desc);

--=================================================================
-- Select total number if employees who are having salary as 10000
--=================================================================
SELECT salary ,COUNT(*) Employees
 FROM  #tmpEmpSal
GROUP BY Salary
HAVING Salary = 10000 ;

--=================================================================
--Select salary and number of poeple having this salary
--=================================================================

SELECT salary ,COUNT(*) NoOfEmployeesHavingThisSalary
 FROM  #tmpEmpSal
GROUP BY Salary

--=================================================================
--Select repeated/duplicated salary.salary that exist mmore than one
--=================================================================
SELECT salary ,COUNT(*) NoOfEmployeesHavinfThisSalary
 FROM  #tmpEmpSal
GROUP BY Salary
HAVING COUNT(Salary) >1 ;
--DROP TABLE #tmpEmpSal;

Friday, May 2, 2014

Remove HTML Tags from MSSQL Column Data

Recently I have assigned a task to format data in column to display  and remove Html tags and related special characters. It was fun to develop that code. all we need to identify start and end index of htlm tag "< >" to replace with empty string.

Here you go ..

--Create temp table
CREATE TABLE #tmp(PrdName VARCHAR(10),HtmlData VARCHAR(2000)
);
 

--Loading test values

INSERT INTO #tmpSELECT 'prd1','<b class="newitem"></b> text<sup>&#174;</sup> - prd1' UNION ALLSELECT 'prd2','<b>prd2</b>' UNION ALLSELECT 'prd3','<b>prd3</b>' ;

--================================

--Create Function to remove html tags

--================================
 
CREATE  FUNCTION fnClearHTMLTags ( @HTMLString NVARCHAR(MAX) )RETURNS NVARCHAR(MAX)AS
BEGIN
DECLARE    
@Start INT ,
            
@End INT ,
            
@Length INT
    
  WHILE CHARINDEX
('<', @HTMLString) > 0 AND CHARINDEX('>', @HTMLString, CHARINDEX('<', @HTMLString)) > 0
  
BEGIN
  SELECT  
@Start  = CHARINDEX('<', @HTMLString) , --selecting start index of html tag
          
@End    = CHARINDEX  ('>', @HTMLString, CHARINDEX('<', @HTMLString)) -- selecting last index of html tag
  
SELECT  @Length = ( @End - @Start ) + 1  --selecting length of html tag data to remove
        
      
IF  @Length > 0
  
BEGIN
  
--Stuff function will replace data between <> to empty string ''
   -- replace function is used to replace other special characters e.g. &,# to empty string
  
SELECT  @HTMLString = REPLACE(REPLACE(REPLACE(REPLACE(STUFF(@HTMLString,@Start, @Length,''), '&', ''),'#', ''), '; -','.'), '*', '')
              
    
END
     END
    
RETURN LTRIM(RTRIM(@HTMLString ))

END  ;
--================================

--Display Data

--================================
  

 SELECT prdName,
      
HtmlData,
      
dbo.fnClearHTMLTags(HtmlData) AS CleanHtmlData
FROM #tmp;




--================================

--Drop Temp Table

--================================
 

DROP TABLE #tmp;-- Drop Function
-- DROP FUNCTION fnClearHTMLTags;

Tuesday, March 11, 2014

Oracle Schema Security Against Authorize Access

Few time back our dev team reported that application started crash and the reason was missing table.

As it was a legacy application and dev knew schema credentials. One of the dev dropped a table and application started throwing error.

This was alarming and I decided to find suitable way to overcome such situation in future.we can simply  create a new user and assign required privileges to new user.But as we can't change application , it was difficult to implement it. we have to avoid solution where we have to provide schema qualification to access objects.

Initially I though about but synonyms but it seems additional overhead to maintain then I found current_schema option from Oracle documentation.

Here is the step by step guide for you to implement and test that solution.

--Connect as sys
1 --Create UserA
CREATE USER USERA IDENTIFIED BY usera;

2 --Grant required privileges to userA
  GRANT CONNECT TO USERA;
  GRANT RESOURCE TO USERA;

3 -Connect as userA
sqlplus usera/usera

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 11 20:36:04 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL>                             

4 --Create table
  CREATE TABLE USERA_TEST
    ( Id NUMBER, Name VARCHAR2(20)
    );
   
5 --Insert test values USERA.USERA_TEST
INSERT INTO usera_test
SELECT 1,'Val1' FROM dual
UNION ALL
SELECT 2,'Val2' FROM dual
UNION ALL
SELECT 3,'Val3' FROM dual;
COMMIT;
   
6- -Create stored proc
CREATE PROCEDURE usera_sp
AS
BEGIN
  dbms_output.put_line('This is USERA stored Proc');
END;
/

7 -- disconnect USERA

8 --  Connect as sys and process following

9 --Creating role
CREATE ROLE ROLE_USERA_PRIVS;

10 --Create UserB
CREATE USER USERB IDENTIFIED BY userb;

GRANT CONNECT TO USERB;
 
11 --Create Grant Script
Spool ON
spool /path/usera_Privs.sql -- path can be any of your preference

SELECT
  CASE
    WHEN object_type ='TABLE'
    THEN 'Grant select on USERA.'||Object_Name||' to ROLE_USERA_PRIVS;'
    WHEN object_type IN ('PROCEDURE','FUNCTION')
    THEN 'Grant Execute on USERA.'||Object_Name||' to ROLE_USERA_PRIVS;'
    WHEN object_type IN ('SEQUENCE')
    THEN 'Grant SELECT on USERA.'||Object_Name||' to ROLE_USERA_PRIVS;'
    WHEN object_type IN ('MATERIALIZED VIEW','VIEW')
    THEN 'Grant select on USERA.'||Object_Name||' to ROLE_USERA_PRIVS;'
  END AS script
FROM dba_objects
WHERE owner='USERA' ;

spool OFF

12 -- Execute script usera_Privs.sql

13 -- Grant role to UserA and disconnect sys
Grant ROLE_USERA_PRIVS to USERB;

14 -- Connect as USERB
--Now if you will connect to USERB and execute following you will have error "ORA-00942: table or view does not exist"
SQL> select * from usera_test;
select * from usera_test
              *
ERROR at line 1:
ORA-00942: table or view does not exist 

However schema qualification will work
SQL> SELECT * FROM USERA.usera_test;

        ID NAME
---------- --------------------
         1 Val1
         2 Val2
         3 Val3
       
15 -- Disconnect USERB

16 -- Connect as sys.
17 -- Create Trigger to change current_schema to required one. So that our application/user need not to mention schema qualification

SQL>  CREATE OR REPLACE TRIGGER sys.afterlogon_myapp AFTER logon ON DATABASE
  DECLARE
  BEGIN
    -- Trigger Used to Set Correct Schema based on Role
    IF (dbms_session.is_role_enabled('ROLE_USERA_PRIVS')) THEN
      EXECUTE immediate 'alter session set current_schema=USERA';
    END IF;
  END;
  /

18 -- Disconnect sys

19 -- connect USERB 
sqlplus userb/userb

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 11 20:26:09 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
-- execute sql without schema qualification
SQL> select * from usera_test;

        ID NAME
---------- --------------------
         1 Val1
         2 Val2
         3 Val3
-- execute stored proc without schema qualification       
SQL> set serveroutput on
SQL> execute usera_sp
This is USERA stored Proc

PL/SQL procedure successfully completed.
                                          
Enjoy !