Remove SQL Replication in Publisher and Subscriber after changing server names

Remove SQL Replication even when the publisher or Subscriber Server is not available

Existing server/computer are renamed and given new names. If you are trying to update the Instance name of  SQL Server. it wont allow you to change the name.  And if you try to remove subscriber and publishers Forcefully  also will through below error.

OLE DB provider "SQLNCLI10" for linked server "repl_distributor" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "repl_distributor" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 65535, Level 16, State 1, Line 0

SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. 

Solution to drop subscriber and publisher are as below (As per MS This procedure should be used only if other methods of removing replication objects have failed)

Declare @repdb as sysname

Set @repdb = 'MyPublisherDatabase' 

EXEC sp_removedbreplication @repdb

Go 

Grant VIEW DEFINITION to all databases stored procedure in SQL Server

/*
Change the user name name in 2nd line and
Replace print @sqlstmt  with exec @sqlstmt to grant VIEW DEFINITION to all databases stored procedure in SQL Server
*/



DECLARE @userName varchar(200)
SET @userName = 'corp\domainuser'

DECLARE @name varchar(50)
DECLARE @spname varchar(50)
DECLARE @schemaname varchar(50)
DECLARE @sqlstmt nvarchar(300)
DECLARE @sqlsel varchar(300)


DECLARE db_cursor CURSOR FOR
SELECT
  name
FROM MASTER.sys.databases
WHERE state_desc = 'ONLINE'
AND is_in_standby = 0
AND name NOT IN ('master', 'model', 'msdb', 'tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
  --Print @name  
  SET @sqlsel = 'DECLARE spro_cursor CURSOR FOR SELECT  SPECIFIC_NAME, SPECIFIC_SCHEMA  from [' + @name + '].information_schema.routines where routine_type = ''PROCEDURE'' and routine_name not like ''dt%'' and routine_name not like ''ms%'' and routine_name not like ''xp%'' '
  -- print @sqlsel
  --DECLARE @spro_cursor CURSOR    
  --  DECLARE spro_cursor CURSOR FOR SELECT  SPECIFIC_NAME, SPECIFIC_SCHEMA  from information_schema.routines where routine_type = 'PROCEDURE'
  EXEC (@sqlsel)
  --DECLARE spro_cursor CURSOR FOR @sqlsel
  OPEN spro_cursor
  FETCH NEXT FROM spro_cursor INTO @spname, @schemaname
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @sqlstmt = 'use [' + @name + ']' + CHAR(13) + CHAR(10) + '  GRANT VIEW DEFINITION ON [' + @schemaname + '].[' + @spname + '] TO [' + @userName + ']'
    PRINT @sqlstmt
    EXECUTE sp_executesql @sqlstmt
    FETCH NEXT FROM spro_cursor INTO @spname, @schemaname
  END
  CLOSE spro_cursor
  DEALLOCATE spro_cursor

  FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

Blocking notification alert job

Step  1:  : Create below procedure in msdb database

use msdb
go
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Blocking_Notification]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_Blocking_Notification]
GO
/*

exec usp_Blocking_Notification 'venkateswara.sangu@gmail.com',500000

change DBMail profile & From address in below procedure before creating
*/

CREATE PROCEDURE usp_Blocking_Notification   @Receipiant nvarchar(30),@waittime int = 100000
AS
SELECT s.session_id
    ,r.STATUS
    ,r.blocking_session_id
    ,r.wait_type
    ,wait_resource
    ,r.wait_time / (1000.0) 'WaitSec'
    ,r.cpu_time
    ,r.logical_reads
    ,r.reads
    ,r.writes
    ,r.total_elapsed_time / (1000.0) 'ElapsSec'
    ,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
            (
                CASE r.statement_end_offset
                    WHEN - 1
                        THEN Datalength(st.TEXT)
                    ELSE r.statement_end_offset
                    END - r.statement_start_offset
                ) / 2
            ) + 1) AS statement_text
    ,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text
    ,r.command
    ,s.login_name
    ,s.host_name
    ,s.program_name
    ,s.host_process_id
    ,s.last_request_end_time
    ,s.login_time
    ,r.open_transaction_count
INTO #temp_requests
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID and
r.wait_time > @waittime
ORDER BY r.cpu_time DESC
    ,r.STATUS
    ,r.blocking_session_id
    ,s.session_id

IF (
        SELECT count(*)
        FROM #temp_requests
        WHERE blocking_session_id > 50
        ) <> 0
BEGIN
    -- blocking found, sent email.
    DECLARE @tableHTML NVARCHAR(MAX);

    SET @tableHTML = N'<H1>Blocking Report</H1>' + N'<table border="1">' + N'<tr>' + N'<th>session_id</th>' + N'<th>Status</th>' +
                     N'<th>blocking_session_id</th><th>wait_type</th><th>wait_resource</th>' +
                     N'<th>WaitSec</th>' + N'<th>cpu_time</th>' +
                     N'<th>logical_reads</th>' + N'<th>reads</th>' +
                     N'<th>writes</th>' + N'<th>ElapsSec</th>' + N'<th>statement_text</th>' + N'<th>command_text</th>' +
                     N'<th>command</th>' + N'<th>login_name</th>' + N'<th>host_name</th>' + N'<th>program_name</th>' +
                     N'<th>host_process_id</th>' + N'<th>last_request_end_time</th>' + N'<th>login_time</th>' +
                     N'<th>open_transaction_count</th>' + '</tr>' + CAST((
                SELECT td = s.session_id
                    ,''
                    ,td = r.STATUS
                    ,''
                    ,td = r.blocking_session_id
                    ,''
                    ,td = r.wait_type
                    ,''
                    ,td = wait_resource
                    ,''
                    ,td = r.wait_time / (1000.0)
                    ,''
                    ,td = r.cpu_time
                    ,''
                    ,td = r.logical_reads
                    ,''
                    ,td = r.reads
                    ,''
                    ,td = r.writes
                    ,''
                    ,td = r.total_elapsed_time / (1000.0)
                    ,''
                    ,td = Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
                            (
                                CASE r.statement_end_offset
                                    WHEN - 1
                                        THEN Datalength(st.TEXT)
                                    ELSE r.statement_end_offset
                                    END - r.statement_start_offset
                                ) / 2
                            ) + 1)
                    ,''
                    ,td = Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) +
                        N'.' + Quotename(Object_name(st.objectid, st.dbid)), '')
                    ,''
                    ,td = r.command
                    ,''
                    ,td = s.login_name
                    ,''
                    ,td = s.host_name
                    ,''
                    ,td = s.program_name
                    ,''
                    ,td = s.host_process_id
                    ,''
                    ,td = s.last_request_end_time
                    ,''
                    ,td = s.login_time
                    ,''
                    ,td = r.open_transaction_count
                FROM sys.dm_exec_sessions AS s
                INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
                CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
                WHERE r.session_id != @@SPID
                    AND blocking_session_id > 0
                ORDER BY r.cpu_time DESC
                    ,r.STATUS
                    ,r.blocking_session_id
                    ,s.session_id
                FOR XML PATH('tr')
                    ,TYPE
                ) AS NVARCHAR(MAX)) + N'</table>';

    EXEC msdb.dbo.sp_send_dbmail @body = @tableHTML
,@from_address  = 'DevDBAAdmin@techridesol.com'
        ,@body_format = 'HTML'
        ,@profile_name = N'TechRideSolDBMail'
        ,@recipients = @Receipiant
        ,@Subject = N'Blocking Detected'
END

DROP TABLE #temp_requests


Step  2 : 

Connect to SQL Server using SSMS and Expand SQL Server Agent and right click on Jobs and select New Job

In New Job window provide the job name and owner as SA and select Steps Tab in left side pane 

In Job Steps Window click on NEW button to create a job step

Transactional Replication Without the Snapshot Agent

When we configuring subscriber in transnational replication it is initialised with snapshot is generated by Snapshot Agent and applied by the Distribution agent. In couple of scenarios like replicating large databases generating & applying Snapshot will time consuming process. Here we will "initialise subscription using backup". In this method we can initialise subscriber database quickly. 

In this method you need to restore the recent full backup taken after the publication was enabled with option of initialise replication with backup below are the steps to configure replication without snapshot using GUI 

Step 1: Configure Distribution 

• Start SSMS and connect to publisher server 
• Right click on Replication folder and select Configure Distribution .. as shown in below fig

• In configure Distribution wizard select the distribution server information & Click Next 

Provide the Snapshot folder and click on Next 

In Distribution Database properties page please provide and name and location of distribution database data & log files. Click on NEXT

• In Next page select the Publishers to use this distribution 

• In final screen of wizard check Configure distribution check box and click Next and in next screen click on as shown in below two screens 

                    

• it will create a distribution and enable the publisher for this distributer.

Step 2: Configure Publisher 

• Open SSMS and connect to publisher server 
• Expand the server, Navigate to Replication folder and expand in Object explorer and right click on Local Publisher and select New Publication ...

• in New Publication Wizard Select the database which you want to publish & click Next 

• Select the publication type as Transnational Publication & Click on Next

• Select the Articles (Tables & other objects) which you want to publish and also select columns to filter tables

• In Snapshot Agent wizard leave the check boxes un-ticked (In this you are configuring replication without snapshot) 

• In Agent Security Dialog box Click on Security Settings button to provide credentials to connect Snapshot agent and LogReader Agent 

• In below image i selected SQL Server Agent Service account but as per MS it is best practice it should have separate windows credentials  

• After provide appropriate credentials it looks like below image 

• Click on next. In next wizard Create the publication check box must need to select 

• Provide Publication name in Final Wizard. and Click on Finish

• It creates and Publication. 

• After publication created open properties of publication by right click on the publication 

• In publication properties window goto Subscriber Option 

• In Subscriber options screen Change "Allow initialization from backup files" value to "true" from false

Step 3: Backup Published Database

• In this table full backup of Published database 

• Right click on database goto Task and select Backup ...

• In Backup database wizard select the Database, Backup type to FULL & backup location in Destination part 

Step 4: Restore Published Database in Subscriber server

• Copy the backup file from Publisher server to Subscriber server and restore it in Subscriber server. Right click on Databases and select Restore Database ... 

• In Restore Database wizard select to restore database from device option and provide the backup file name along with location 

• Provide the database name and click on Restore option 

• It restores the database with given name in subscriber server 

Step 5: Add Subscriber information 

Connect to publisher server goto New Query and run below SQL Statement on Publisher Database with appropriate values 

sp_addsubscription 

@publication ='PublicationName', -- Provide Publisher name 

@subscriber='Subscriber Server', -- Provide Subscriber Server instance name 

@destination_db='TranRepl_WOSnapShotTest',  -- Provide Destination Server 

@sync_type = 'initialize with backup', -- With this it recognise that subscriber will initiate using backup file 

@backupdevicetype = 'disk', -- 

@backupdevicename = 'd:\TranRepl_WOSnapShotTest.bak' -- Provide backup file location 

go

http://sql2005ted.blogspot.in/2010/12/setting-up-transactional-replication.html

What is – DML, DDL, DCL,TCL and DQL

What is DDL Command  : (Data Definition Language)

It is used to create and modify the structure of database objects in database.

Command	Description
CREATE	Creates a new table, a view of a table, or other object in database
ALTER	Modifies an existing database object, such as a table
DROP	Deletes an entire table, a view of a table or other object in the database

What is DML Command  : (Data Manipulation Language)

It is used to retrieve, store, modify, delete, insert and update data in database.

Command	Description
INSERT	Creates a record
UPDATE	Modifies records
DELETE	Deletes records

What is DCL Command  : (Data Control Language)

It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

Command	Description
GRANT	Gives a privilege to user
REVOKE	Takes back privileges granted from user

What is DQL Command  : (Data Query Language)

It is used to fetch the records from Physical files

Command	Description
SELECT	Retrieves certain records from one or more tables

What is TCL Command  : (Transaction Control Language)

It is used to manage different transactions occurring within a database.

Command	Description
COMMIT	To commit the bunch of SQL Statements
ROLLBACK	To revert a transaction

Issue in expanding MSDB packages in SSIS

When you are trying to expand MSDB folder in Integration services. if you are facing below error.

TITLE: Import Package

------------------------------

The SQL Server instance specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in %SQL_PRODUCT_SHORT_NAME% Books Online.

Login timeout expired

A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

Named Pipes Provider: Could not open a connection to SQL Server [2].  (MsDtsSrvr)

------------------------------

ADDITIONAL INFORMATION:

Login timeout expired

A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

Named Pipes Provider: Could not open a connection to SQL Server [2].  (Microsoft SQL Server Native Client 11.0)

Solution : you need to configure Integration Services service 

you will have to modify the configuration file if your packages are stored in a named instance or a remote instance of Database Engine.

Configuration file : MsDtsSrvr.ini.xml

Location :  %ProgramFiles%\Microsoft SQL Server\120\DTS\Binn

which version of Windows you are running

To know which version of windows os you are using enter the following commands in the Command Prompt

wmic os get caption

wmic os get osarchitecture

to get the information of used/free space of all SQL Server databases in an instance.

To get the information of used/free space of all SQL Server databases in an instance.


EXEC sp_msforeachdb
'USE [?];
SELECT DB_NAME() AS [Database Name],
    CAST(SUM(CASE WHEN type_desc = ''ROWS'' THEN size END) /128.0 AS DECIMAL(18,2)) AS [Data file size(MB)],
    CAST(SUM(CASE WHEN type_desc = ''LOG'' THEN size END) / 128.0 AS DECIMAL(18,2)) AS [Log file size(MB)],
    CAST(SUM(FILEPROPERTY(name, ''SpaceUsed''))/128.0 AS decimal(18,2)) AS [Used space(MB)],
    CAST(SUM(size)/128.0 AS DECIMAL(18,2)) - CAST(SUM(FILEPROPERTY(name,''SpaceUsed''))/128.0 AS decimal(18,2)) AS [Free space(MB)]  
FROM sys.database_files
GROUP BY type_desc'

Enabling CLR Integration

Msg 6263, Level 16, State 1, Line 1

Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

The common language runtime (CLR) integration feature is off by default, and must be enabled in order to use objects that are implemented using CLR integration. To enable CLR integration, use the clr enabled option of the sp_configure stored procedure

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

VB Script Conversion functions

VB Script Conversion functions

CBool() - Convert to Boolean Type 
Converts an expression to a Boolean variable type that is either True or False.

CByte() - Convert to Byte Value 
Converts an expression to a Byte type (i.e. an integer value in the range 0 to 255).

CCur() - Convert to Currency Type 
Converts an expression to a Currency type (i.e. a floating-point number with up to four decimal places).

CDate() - Convert to Date Value 
Converts a formatted date string to a date variable type.

CDbl() - Convert to a Double Value 
Converts an expression to a double-precision floating point number.

CInt() - Convert to Integer Value 
Converts an expression into an integer value in the range -32,768 to 32,767.

CLng() - Convert to Long Integer Value 
Converts an expression to a long integer value.

CSng() - Convert to a Single Value 
Converts an expression into a single-precision floating-point number.

CStr() - Convert to String Value 
Converts an expression into a string value.