SQL Server - Server Roles from Quackit.com

SQL Server - Server Roles from Quackit.com

Locate missing indices

Locate missing indices

 SELECT * FROM sys.dm_db_missing_index_groups G INNER JOIN sys.dm_db_missing_index_group_stats GS ON G.index_group_handle = GS.group_handle INNER JOIN sys.dm_db_missing_index_details D ON G.index_handle = D.index_handle CROSS APPLY sys.dm_db_missing_index_columns (D.index_handle) DC ORDER BY D.index_handle ,[statement] 

Locate unused indices

 SELECT OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName ,OBJECT_NAME(I.OBJECT_ID) AS ObjectName ,I.NAME AS IndexName FROM sys.indexes I WHERE OBJECTPROPERTY(I.OBJECT_ID, 'IsUserTable') = 1 AND NOT EXISTS ( SELECT index_id FROM sys.dm_db_index_usage_stats WHERE OBJECT_ID = I.OBJECT_ID AND I.index_id = index_id AND database_id = DB_ID() ) ORDER BY SchemaName ,ObjectName ,IndexName

Back Up your database using Cursor on SQL server 2008

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'D:\Backup\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
       BACKUP DATABASE @name TO DISK = @fileName

       FETCH NEXT FROM db_cursor INTO @name  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

Find out job name from command text

USE [msdb]
GO
SELECT j.job_id,
s.srvname,
j.name,
js.step_id,
js.command,
j.enabled
FROM dbo.sysjobs j
JOIN dbo.sysjobsteps js
ON js.job_id = j.job_id
JOIN master.dbo.sysservers s
ON s.srvid = j.originating_server_id
WHERE js.command LIKE N'%Your_text_Here%'
GO

SQL server 2008 Script for job details

set nocount on

select 'Server'       = left(@@ServerName,20),

       'JobName'      = left(S.name,30),

       'ScheduleName' = left(ss.name,25),

       'Enabled'      = CASE (S.enabled)

                          WHEN 0 THEN 'No'

                          WHEN 1 THEN 'Yes'

                          ELSE '??'

                        END,

       'Frequency'    = CASE(ss.freq_type)

                          WHEN 1  THEN 'Once'

                          WHEN 4  THEN 'Daily'

                          WHEN 8  THEN (case when (ss.freq_recurrence_factor > 1) then  'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Weeks'  else 'Weekly'  end)

                          WHEN 16 THEN (case when (ss.freq_recurrence_factor > 1) then  'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Months' else 'Monthly' end)

                          WHEN 32 THEN 'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Months' -- RELATIVE

                          WHEN 64 THEN 'SQL Startup'

                          WHEN 128 THEN 'SQL Idle'

                          ELSE '??'

                        END,

       'Interval'    = CASE

                         WHEN (freq_type = 1)                       then 'One time only'

                         WHEN (freq_type = 4 and freq_interval = 1) then 'Every Day'

                         WHEN (freq_type = 4 and freq_interval > 1) then 'Every ' + convert(varchar(10),freq_interval) + ' Days'

                         WHEN (freq_type = 8) then (select 'Weekly Schedule' = D1+ D2+D3+D4+D5+D6+D7

                                                       from (select ss.schedule_id,

                                                                     freq_interval,

                                                                     'D1' = CASE WHEN (freq_interval & 1  <> 0) then 'Sun ' ELSE '' END,

                                                                     'D2' = CASE WHEN (freq_interval & 2  <> 0) then 'Mon '  ELSE '' END,

                                                                     'D3' = CASE WHEN (freq_interval & 4  <> 0) then 'Tue '  ELSE '' END,

                                                                     'D4' = CASE WHEN (freq_interval & 8  <> 0) then 'Wed '  ELSE '' END,

                                                                    'D5' = CASE WHEN (freq_interval & 16 <> 0) then 'Thu '  ELSE '' END,

                                                                     'D6' = CASE WHEN (freq_interval & 32 <> 0) then 'Fri '  ELSE '' END,

                                                                     'D7' = CASE WHEN (freq_interval & 64 <> 0) then 'Sat '  ELSE '' END

                                                                 from msdb..sysschedules ss

                                                                where freq_type = 8

                                                           ) as F

                                                       where schedule_id = sj.schedule_id

                                                    )

                         WHEN (freq_type = 16) then 'Day ' + convert(varchar(2),freq_interval)

                         WHEN (freq_type = 32) then (select freq_rel + WDAY

                                                        from (select ss.schedule_id,

                                                                     'freq_rel' = CASE(freq_relative_interval)

                                                                                    WHEN 1 then 'First'

                                                                                    WHEN 2 then 'Second'

                                                                                    WHEN 4 then 'Third'

                                                                                    WHEN 8 then 'Fourth'

                                                                                    WHEN 16 then 'Last'

                                                                                    ELSE '??'

                                                                                  END,

                                                                    'WDAY'     = CASE (freq_interval)

                                                                                    WHEN 1 then ' Sun'

                                                                                    WHEN 2 then ' Mon'

                                                                                    WHEN 3 then ' Tue'

                                                                                    WHEN 4 then ' Wed'

                                                                                    WHEN 5 then ' Thu'

                                                                                    WHEN 6 then ' Fri'

                                                                                    WHEN 7 then ' Sat'

                                                                                    WHEN 8 then ' Day'

                                                                                    WHEN 9 then ' Weekday'

                                                                                    WHEN 10 then ' Weekend'

                                                                                    ELSE '??'

                                                                                  END

                                                                from msdb..sysschedules ss

                                                                where ss.freq_type = 32

                                                             ) as WS

                                                       where WS.schedule_id =ss.schedule_id

                                                       )

                       END,

       'Time' = CASE (freq_subday_type)

                        WHEN 1 then   left(stuff((stuff((replicate('0', 6 - len(Active_Start_Time)))+ convert(varchar(6),Active_Start_Time),3,0,':')),6,0,':'),8)

                        WHEN 2 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' seconds'

                        WHEN 4 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' minutes'

                        WHEN 8 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' hours'

                        ELSE '??'

                      END,

       'Next Run Time' = CASE SJ.next_run_date

                           WHEN 0 THEN cast('n/a' as char(10))

                           ELSE convert(char(10), convert(datetime, convert(char(8),SJ.next_run_date)),120)  + ' ' + left(stuff((stuff((replicate('0', 6 - len(next_run_time)))+ convert(varchar(6),next_run_time),3,0,':')),6,0,':'),8)

                         END

 

   from msdb.dbo.sysjobschedules SJ

   join msdb.dbo.sysjobs         S  on S.job_id       = SJ.job_id

   join msdb.dbo.sysschedules    SS on ss.schedule_id = sj.schedule_id

order by S.name

GO

IF (object_id('sp_ShowJobSchedules') IS NOT NULL)

  PRINT 'Procedure created.'

ELSE

  PRINT 'Procedure NOT created.'

GO

     

Sql queries for IT students

assign no 5
//check
1.create table tebranch (branch_name varchar2(10) primary key , branch_city varchar2(10), assests number(12,2), check (assests >1000));
insert into tebranch values ('sbi','kop',20000);

insert into tebranch values ('icici','kop',30000);

insert into tebranch values ('axis','pune',40000);

insert into tebranch values ('axis','pune',500);//ORA-02290: check constraint (SYSTEM.SYS_C004082) violated

select *from tebranch

BRANCH_NAME BRANCH_CITY ASSESTS
sbi kop 20000
icici kop 30000
axis pune 40000


//unique
2.create table tebranch1 (branch_name varchar2(10)  constraint tecons primary key , branch_city varchar2(10), assests number(12,2), check (assests >1000));

insert into tebranch1 values ('sbi','kop',20000);

insert into tebranch1 values ('icici','kop',30000);

insert into tebranch1 values ('axis','pune',40000);

insert into tebranch1 values ('rbi','pune',40);////ORA-02290:check constraint (SYSTEM.SYS_C004084) violated

insert into tebranch1 values ('sbi','pune',4000);//ORA-00001: unique constraint (SYSTEM.TECONS) violated


//

4.delete from tebranch where branch_name='icici';//ORA-02292: integrity constraint (SYSTEM.SYS_C004046) violated - child record found

//foreign key
5.create table teaccount1 (account_number number(10) not null primary key, branch_name varchar2(10), balance number (12,2), check(balance>100),
foreign key (branch_name) references tebranch1 on delete cascade);

insert into teaccount1 values(1,'sbi',5000);

insert into teaccount1 values(2,'icici',4000);

insert into teaccount1 values(3,'axis',3000);

select *from teaccount1;

ACCOUNT_NUMBER BRANCH_NAME BALANCE
1 sbi 5000
2 icici 4000
3 axis 3000

delete from tebranch1 where branch_name='icici';
1 row(s) deleted.
select * from tebranch;
BRANCH_NAME BRANCH_CITY ASSESTS
sbi kop 20000
axis pune 40000
select *from teaccount1;

ACCOUNT_NUMBER BRANCH_NAME BALANCE
1 sbi 5000
3 axis 3000


//not null
6.create table stebranch (branch_name varchar2(10) primary key , branch_city varchar2(10) not null, assests number(12,2), check (assests >1000));

insert into stebranch values('sbi','kop',2000);

insert into stebranch values('icici','kop',3000);

select *from stebranch;

BRANCH_NAME BRANCH_CITY ASSESTS
sbi kop 2000
icici kop 3000

insert into stebranch values('sbi','kop',4000);//unique constraint (SYSTEM.SYS_C004096) violated

insert into stebranch values('sbi',null,2000);//cannot insert NULL into ("SYSTEM"."STEBRANCH"."BRANCH_CITY")

insert into stebranch values('sbi','kop',300);// check constraint (SYSTEM.SYS_C004095) violated


7.create table stebranch1 (branch_name varchar2(10) constraint xxxx primary key , branch_city varchar2(10) not null, assests number(12,2),
check (assests >1000));  

insert into stebranch1 values('sbi','kop',10000);

insert into stebranch1 values('icici','kop',10000);  

select *from stebranch1

BRANCH_NAME BRANCH_CITY ASSESTS
sbi kop 10000
icici kop 10000

insert into stebranch1 values('sbi','kop',10000);// ORA-00001: unique constraint (SYSTEM.XXXX) violated

insert into stebranch1 values('icici',null,10000); //ORA-01400: cannot insert NULL into ("SYSTEM"."STEBRANCH1"."BRANCH_CITY")


8.//primary key
create table stebranch (branch_name varchar2(10) primary key , branch_city varchar2(10) not null, assests number(12,2), check (assests >1000));

insert into stebranch values('sbi','kop',2000);

insert into stebranch values('icici','kop',3000);

insert into stebranch values('sbi','kop',2000);//ORA-00001: unique constraint (SYSTEM.SYS_C004096) violated

select *from stebranch;

BRANCH_NAME BRANCH_CITY ASSESTS
sbi kop 2000
icici kop 3000

//foreign key

9.
create table steaccount (account_number number(10) not null primary key, branch_name varchar2(10), balance number (12,2), check(balance>100),
foreign key (branch_name) references stebranch);  

insert into steaccount values(1,'sbi',3400);

insert into steaccount values(2,'icici',3400);

select *from steaccount;

ACCOUNT_NUMBER BRANCH_NAME BALANCE
1 sbi 3400
2 icici 3400

insert into steaccount values(1,'sbi',3400); //ORA-00001: unique constraint (SYSTEM.SYS_C004102) violated

insert into steaccount values(2,'rbi',3400);//ORA-02291: integrity constraint (SYSTEM.SYS_C004102) violated - parent key not found

delete from stebranch where branch_name='sbi';//ORA-02292: integrity constraint (SYSTEM.SYS_C004103) violated - child record found

insert into steaccount values(null,'sbi',3400);//ORA-01400: cannot insert NULL into ("SYSTEM"."STEACCOUNT"."ACCOUNT_NUMBER")

//foreign & delete cascade
10.create table steaccount1 (account_number number(10) not null primary key, branch_name varchar2(10), balance number (12,2), check(balance>100),
foreign key (branch_name) references stebranch on delete cascade);

insert into steaccount1 values(1,'sbi',10000);

insert into steaccount1 values(2,'icici',10000);

select *from steaccount1;

ACCOUNT_NUMBER BRANCH_NAME BALANCE
1 sbi 10000
2 icici 10000

delete from steaccount1;
2 row(s) deleted.
//delete from stebranch where branch_name='sbi'
select *from steaccount1;
no data found




//character function

1.select upper(branch_name) as branch_name from tebranch;

BRANCH_NAME
SBI
ICICI
AXIS

2.select lower(branch_name) from tebranch;

LOWER(BRANCH_NAME)
sbi
icici
axis

3.select initcap(branch_name) from tebranch;

INITCAP(BRANCH_NAME)
Sbi
Icici
Axis

4.select concat(branch_name,branch_city) from tebranch;

CONCAT(BRANCH_NAME,BRANCH_CITY)
sbikop
icicikop
axispune
   select concat(branch_name,assests) from tebranch;

CONCAT(BRANCH_NAME,ASSESTS)
sbi20000
icici30000
axis40000

5.select substr(branch_name,2,3)from tebranch;

SUBSTR(BRANCH_NAME,2,3)
bi
cic
xis

6.select instr(branch_name,'i')from tebranch;

INSTR(BRANCH_NAME,'I')
3
1
3

7.select trim('i' from branch_name)from tebranch;

TRIM('I'FROMBRANCH_NAME)
sb
cic
axis

8.select length(branch_name)from tebranch;

LENGTH(BRANCH_NAME)
3
5
4

9.select lpad(branch_name,10,'xx')from tebranch;

LPAD(BRANCH_NAME,10,'XX')
xxxxxxxsbi
xxxxxicici
xxxxxxaxis
   select rpad(branch_name,10,'xx')from tebranch;

RPAD(BRANCH_NAME,10,'XX')
sbixxxxxxx
icicixxxxx
axisxxxxxx



//number fuction

1.select round(123.467,2) from dual;

ROUND(123.467,2)
123.47
   select round(assests,3)from tebranch

ROUND(ASSESTS,3)
20000
30000
40000
   select *from tebranch

2.select trunc(123.467,2) from dual;

TRUNC(123.467,2)
123.46
   select trunc(assests,2) from tebranch;

TRUNC(ASSESTS,2)
20000
30000
40000

3.select power(2,3) from dual;

POWER(2,3)
8
   select power(assests,2) from tebranch;

POWER(ASSESTS,2)
400000000
900000000
1600000000
 
4.select abs(-2) from dual;

ABS(-2)
2
   select abs(-3) from tebranch;

ABS(-3)
3
3
3
 
5.select mod(5,3) from dual;

MOD(5,3)
2
   select mod(5,3) from tebranch;

MOD(5,3)
2
2
2


//date function
1.select sysdate from dual;

SYSDATE
20-MAR-13

2.select months_between('10-mar-13', '10-july-13') from dual;

MONTHS_BETWEEN('10-MAR-13','10-JULY-13')
-4

3.select add_months('20-mar-13', 5) from dual;
or
 select add_months(sysdate, 5) from dual;

ADD_MONTHS('20-MAR-13',5)
20-AUG-13

4.select next_day('20-mar-13', 'sunday') from dual;
or
   select next_day(sysdate, 'sunday') from dual;

NEXT_DAY('20-MAR-13','SUNDAY')
24-MAR-13

5.select last_day('20-mar-13') from dual;
or
  select last_day(sysdate) from dual;

LAST_DAY('20-MAR-13')
31-MAR-13

6.select round(sysdate,'year') from dual;

ROUND(SYSDATE,'YEAR')
01-JAN-13
  select round(sysdate,'mm') from dual;
ROUND(SYSDATE,'MM')
01-APR-13

7.select trunc(sysdate,'mm') from dual;
or
  select trunc(sysdate,'year') from dual;

TRUNC(SYSDATE,'MM')
01-MAR-13

8.select to_char(sysdate,'yyyy') from dual;

TO_CHAR(SYSDATE,'YYYY')
2013

  select to_char(sysdate,'mm') from dual;

TO_CHAR(SYSDATE,'MM')
03


9.select to_date('20032012','dd-mm-yyyy') from dual;

TO_DATE('20032012','DD-MM-YYYY')
20-MAR-12

10.select to_number(marks,'99.9') from testud;

Basics of SQL Server 2008 R2 for DBA

List of all database with recovery modes and version information.

'--- SQL 2005

SELECT NAME,COMPATIBILITY_LEVEL,RECOVERY_MODEL_DESC,STATE_DESC  FROM SYS.DATABASES

--- SQL 2000

SELECT NAME,CMPTLEVEL,DATABASEPROPERTYEX(NAME,'RECOVERY')AS RECOVERYMODEL,

DATABASEPROPERTYEX(NAME,'STATUS') AS STATUS FROM SYSDATABASES

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

List database Names with Physical location where the data files reside.

--- SQL 2005
SELECT DB_NAME(DATABASE_ID) AS DATABASENAME,NAME,TYPE_DESC,PHYSICAL_NAME FROM SYS.MASTER_FILES
--- SQL 2000
SELECT DB_NAME(DBID) AS DATABASENAME,NAME,FILENAME FROM MASTER.DBO.SYSALTFILES

e='m 2 n b ��m H p ;margin-bottom:.0001pt;line-height: normal'>How to retrieve the configuration paramaters in SQL Server 2005?

'SELECT * from sys.configurations order by NAME

or

SP_CONFIGURE 'show advanced options',1

go

RECONFIGURE with OVERRIDE

go

SP_CONFIGURE

Go

List of all database and its last backup taken on SQL Server .

SELECT db.name,case when MAX(b.backup_finish_date) is NULL then 'No Backup' else convert(varchar(100),

 MAX(b.backup_finish_date)) end AS last_backup_finish_date

FROM sys.databases db

LEFT OUTER JOIN msdb.dbo.backupset b ON db.name = b.database_name AND b.type = 'D'

 WHERE db.database_id NOT IN (2) GROUP BY db.name

ORDER BY 2 DESC

DBA Concepts

How to update statistics which are outdated?

--- updating usage statistics on SQL Server
EXEC sp_updatestats

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

How to identify the current SQL version on the server?

SELECT
          SERVERPROPERTY('MachineName') as Host,
          SERVERPROPERTY('InstanceName') as Instance,
          SERVERPROPERTY('Edition') as Edition, /*shows 32 bit or 64 bit*/
          SERVERPROPERTY('ProductLevel') as ProductLevel, /* RTM or SP1 etc*/
          Case SERVERPROPERTY('IsClustered') when 1 then 'CLUSTERED' else
      'STANDALONE' end as ServerType,
          @@VERSION as VersionNumber         

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

How to retrieve the configuration paramaters in SQL Server 2005?

'SELECT * from sys.configurations order by NAME

or

SP_CONFIGURE 'show advanced options',1

go

RECONFIGURE with OVERRIDE

go

SP_CONFIGURE

Go

Identify Top 50 queries running with execution count

Identify Top 50 queries running with execution count

Use Master
go
SELECT TOP 50 creation_time, last_execution_time, total_clr_time,total_clr_time/execution_count AS [Avg CLR Time], last_clr_time, execution_count, (SELECT SUBSTRING(text, statement_start_offset/2, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2  ELSE statement_end_offset END - statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [Avg CLR Time] DESC;
GO

How to configure AWE in SQL 2005/SQL 2008 on 32 bit Operating System?

How to configure AWE in SQL 2005/SQL 2008 on 32 bit Operating System?

Before you configure AWE ensure the database server has sufficient memory , ideal sceanio is Server RAM: 6GB , leave 2 GB for OS and other background running process, configure 4GB as AWE, check if awe is enabled or not first
Following steps needs to be performed
USE MASTER
GO
sp_configure 'show advanced options',1
go
reconfigure
Go
sp_configure 'min server memory (MB)','1024'
go
sp_configure 'max server memory', 4096
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO

Requirement For DBA

Knowledge, skills, and abilities needed to be successful in this role include:

* Knowledge of SQL Server 2005/2008

* Ability to perform database and SQL performance analysis and to make tuning changes, including but not limited to the use of SQL Profiler

* Expertise in the use and design of SQL Server Analysis Services 2008

* Expertise in the use of SQL Server Reporting Services 2008

* Experience estimating storage requirements for an application

* Excellent verbal and written communication skills

* Ability to work individually or within a team environment

* Ability to take direction and work to specified deadlines

* Displays professionalism and a commitment to quality

* Experience with design and maintenance of data warehouse

Replication In SQL Server 2008 R2

What is replication?

In its simplest form, data in a master site are replicated to other sites in the form of snapshots. A snapshot does not have to contain all the master data—it can, for example, exclude certain columns from a table for security reasons.

What are different types of replication?

1.      Transactional Replication

              Transactional replication replicates the data on one server (the publisher) to another server (the subscriber) with less latency than log shipping.

              Transactional replication is implemented by the SQL Server Snapshot Agent, Log Reader Agent, and Distribution Agent. The Snapshot Agent prepares snapshot files containing schema and data of published tables and database objects, stores the files in the snapshot folder, and records synchronization jobs in the distribution database on the Distributor.

              The Log Reader Agent monitors the transaction log of each database configured for transactional replication and copies the transactions marked for replication from the transaction log into the distribution database, which acts as a reliable store-and-forward queue. The Distribution Agent copies the initial snapshot files from the snapshot folder and the transactions held in the distribution database tables to Subscribers.

              Incremental changes made at the Publisher flow to Subscribers according to the schedule of the Distribution Agent, which can run continuously for minimal latency, or at scheduled intervals. Because changes to the data must be made at the Publisher (when transactional replication is used without immediate updating or queued updating options), update conflicts are avoided. Ultimately, all Subscribers will achieve the same values as the Publisher. If immediate updating or queued updating options are used with transactional replication, updates can be made at the Subscriber, and with queued updating, conflicts might occur.

2.       Merge Replication

                Merge was designed for more of a client-server environment where the client      sometimes syncs up with the server but is disconnected quite often.Changes are tracked by triggers instead of by the log reader agent, and latency isn't as low as it is with peer to peer.

3.      Snapshot  Replication

          Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.

   Define below terms used in replication

·        Publisher

·        Subscriber

·        Distributor

Publisher:

Publisher is the server or database that sends its data to another server or database.

Publisher contain publication/publications. Publication is a collection of one or more articles that is sent to a subscriber server or database. Article is the basic unit of replication and can be a table or a subset of a table.

Subscriber

A Subscriber is a database instance that receives replicated data. A Subscriber can receive data from multiple Publishers and publications. Depending on the type of replication chosen, the Subscriber can also pass data changes back to the Publisher or republish the data to other Subscribers.

Distributor

The Distributor is a database instance that acts as a store for replication specific data associated with one or more Publishers. Each Publisher is associated with a single database (known as a distribution database) at the Distributor. The distribution database stores replication status data, metadata about the publication, and, in some cases, acts as a queue for data moving from the Publisher to the Subscribers. In many cases, a single database server instance acts as both the Publisher and the Distributor. This is known as a local Distributor. When the Publisher and the Distributor are configured on separate database server instances, the Distributor is known as a remote Distributor.

What is the difference between Push and Pull Subscription?

Push and Pull, as named in the title of this article,are the two methods available for moving data from the Distributor to the Subscriber(s).  Under the Push method, the Distributor is responsible for queuing data from the Publisher,then propagating it to the Subscriber(s).  Under the Pull method, the Distributor is responsible for queuing data from the Publisher, and it is the job of each Subscriber to connect to the Distributor and grab all queued data ready for replication.  Selecting the incorrect method can lead to serious performance issues, especially at peak times of database use.