Check list for before Migration?

 Migration from SQL Server 2008 to SQL Server 2014 Check list

1. Identify databases you would like to migrate

2. Backup all user databases

3. Script out all the existing login

4. Script out all the Server roles if applicable

5. Script out all the Audit and Audit Specifications if Applicable

6. Script out backup devices if Applicable

7. Script out Server level triggers if Applicable

8. Script out Replication along with Configuration if Applicable

9. Script out Mirroring if Applicable

10. Script out Data Collection if Applicable

11. Script out Resource Governor’s objects if Applicable

12. Script out Linked Server if Applicable

13. Script out Logshipping if Applicable

14. Script out SQL Server Agent jobs

15. Script out all DB Mail objects such as Profile and its settings

16. Script out all Proxy accounts and credentials if Applicable

17. Script out all Operators if Applicable

18. Script out all alerts if Applicable

19. Save SQL Server, Server configuration in a file

20. Data Encryption keys

Destination SQL Server Checklist

1. Required SQL Server is installed

2. DBA SQL Server Check List is Completed

3. Enough Space for storing Backup and source scripts

4. Applications compatibility is signed off

How applications generally connect to database?

 1. When application try to connect, uses Config file (Configuration file--Resides in application server). Config file (.txt) should contains SQL Server instance name + user name [service account name--DBA team should add the account into SQL Server under security] + Password [Strong]

Application use all these details from Config file and point connection to SQL server database

Real time points:

> Any business 2 data centers maintains called PRODUCTION and DR data center.

> Always distance between data centers should not be more than 50 KM.

> Few people always work inside the data centers.

sp who3 script to check processes in SQL server

 --who3 to check active sessions in SQL Server.

SELECT

    SPID                = er.session_id

    ,BlkBy              = er.blocking_session_id      

    ,ElapsedMS          = er.total_elapsed_time

    ,CPU                = er.cpu_time

    ,IOReads            = er.logical_reads + er.reads

    ,IOWrites           = er.writes     

    ,Executions         = ec.execution_count  

    ,CommandType        = er.command         

    ,ObjectName         = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  

    ,SQLStatement       =

        SUBSTRING

        (

            qt.text,

            er.statement_start_offset/2,

            (CASE WHEN er.statement_end_offset = -1

                THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2

                ELSE er.statement_end_offset

                END - er.statement_start_offset)/2

        )        

    ,STATUS             = ses.STATUS

    ,[Login]            = ses.login_name

    ,Host               = ses.host_name

    ,DBName             = DB_Name(er.database_id)

    ,LastWaitType       = er.last_wait_type

    ,StartTime          = er.start_time

    ,Protocol           = con.net_transport

    ,transaction_isolation =

        CASE ses.transaction_isolation_level

            WHEN 0 THEN 'Unspecified'

            WHEN 1 THEN 'Read Uncommitted'

            WHEN 2 THEN 'Read Committed'

            WHEN 3 THEN 'Repeatable'

            WHEN 4 THEN 'Serializable'

            WHEN 5 THEN 'Snapshot'

        END

    ,ConnectionWrites   = con.num_writes

    ,ConnectionReads    = con.num_reads

    ,ClientAddress      = con.client_net_address

    ,Authentication     = con.auth_scheme

FROM sys.dm_exec_requests er

LEFT JOIN sys.dm_exec_sessions ses

ON ses.session_id = er.session_id

LEFT JOIN sys.dm_exec_connections con

ON con.session_id = ses.session_id

CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt

OUTER APPLY 

(

    SELECT execution_count = MAX(cp.usecounts)

    FROM sys.dm_exec_cached_plans cp

    WHERE cp.plan_handle = er.plan_handle

) ec

ORDER BY

    er.blocking_session_id DESC,

    er.logical_reads + er.reads DESC,

    er.session_id