Replication Q&A

Q. Can you bring replicated database offline?

A. No, We can’t bring replicated database offline as it will throw error as database is being replicate and can’t bring I offline, we must take it off of replication before making it offline.

Q. Can you schedule replication?

A. Yes, using replication jobs schedule can be done where subscriber don’t need recent data.

Q.  Under what circumstances will you re-initialize replication?

A.   1. Replication expires (snapshot expires)
      2. Replication is disabled and needed to be re-enabled.
      3. Adding article to an existing replication. (sometimes only snapshot needed for added article in transactional replication)
      4. Certain structure errors related to replication.

Q. What is orphan replication? How to clean it?

A.  Sometimes we see publication and subscription on database which is not connected to system, it happens because during restore/refresh publication comes with database and gets created.
     SP_removedbreplication ‘’ can remove orphan replication, without parameter there is risk of deleting all replication on database.

Q : Does activity need to be stopped on a database when it is published?

A : No. Activity can continue on a database while a publication is being created. Be aware that producing a snapshot can be resource-intensive, so it is best to generate snapshots during periods of lower activity on the database (by default a snapshot is generated when you complete the New Publication Wizard).

 

Q :Are tables locked during snapshot generation?

 A : The length of time that the locks are taken depends on the type of replication used:

For merge publications, the Snapshot Agent does not take any locks.

For transactional publications, by default the Snapshot Agent takes locks only during the initial phase of snapshot generation.
For snapshot publications the Snapshot Agent takes locks during the entire snapshot generation process.

Because locks prevent other users from updating the tables, the Snapshot Agent should be scheduled to execute during periods of lower activity on the database, especially for snapshot publications.

Q :Should I script my replication configuration?

A : Yes. Scripting the replication configuration is a key part of any disaster recovery plan for a replication topology

Q : What recovery model is required on a replicated database?

A : Replication functions properly using any of the recovery models: simple, bulk-logged, or full. Merge replication tracks change by storing information in metadata tables. Transactional replication tracks changes by marking the transaction log, but this marking process is not affected by the recovery model.

Q : Why does replication add a column to replicated tables; will it be removed if the table isn't published?

A : To track changes, merge replication and transactional replication with queued updating subscriptions must be able to uniquely identify every row in every published table. To accomplish this:
Merge replication adds the column rowguid to every table, unless the table already has a column of data type uniqueidentifier with the ROWGUIDCOL property set (in which case this column is used). If the table is dropped from the publication, the rowguid column is removed; if an existing column was used for tracking, the column is not removed.

If a transactional publication supports queued updating subscriptions, replication adds the column msrepl_tran_version to every table. If the table is dropped from the publication, the msrepl_tran_version column is not removed.

A filter must not include the rowguidcol used by replication to identify rows. By default this is the column added at the time you set up merge replication and is named rowguid

Q : How do I manage constraints on published tables?

A : There are a number of issues to consider regarding constraints on published tables:

Transactional replication requires a primary key constraint on each published table. Merge replication does not require a primary key, but if one is present, it must be replicated. Snapshot replication does not require a primary key.

By default, primary key constraints, indexes, and check constraints are replicated to Subscribers.

 

The NOT FOR REPLICATION option is specified by default for foreign key constraints and check constraints; the constraints are enforced for user operations but not agent operations.

Q: Can multiple publications use the same distribution database?

A: Yes. There are no restrictions on the number or types of publications that can use the same distribution database. All publications from a given Publisher must use the same Distributor and distribution database.

Q :Does replication encrypt data?
A : No. Replication does not encrypt data that is stored in the database or transferred over the network

Q :Does replication resume if a connection is dropped

A :Yes. Replication processing resumes at the point at which it left off if a connection is dropped. If you are using merge replication over an unreliable network, consider using logical records, which ensures related changes are processed as a unit.

Q: Are logins and passwords replicated?
A :No. You could create a DTS package to transfer logins and passwords from a Publisher to one or more Subscribers

Q: Why can't I run TRUNCATE TABLE on a published table?

A: TRUNCATE TABLE is a non-logged operation that does not fire triggers. It is not permitted because replication cannot track the changes caused by the operation: transactional replication tracks changes through the transaction log; merge replication tracks changes through triggers on published tables.

Q : What is the effect of running a bulk insert command on a replicated database?

A: For transactional replication, bulk inserts are tracked and replicated like other inserts

Q : Does replication affect the size of the transaction log?

A : Merge replication and snapshot replication do not affect transaction log size, but transactional replication can. If a database includes one or more transactional publications, the log is not truncated until all transactions relevant to the publications have been delivered to the distribution database. If the transaction log is growing too large, and the Log Reader Agent is running on a scheduled basis, consider shortening the interval between runs. Or, set it to run in continuous mode 

Q: How do I move or rename files for databases involved in replication?

A : In versions of SQL Server prior to SQL Server 2005, moving or renaming database files required detaching and reattaching the database. Because a replicated database cannot be detached,replication had to be removed from these databases first. Beginning with SQL Server 2005, you can move or rename files without detaching and re-attaching the database, with no effect onreplication.

Q: How do I drop a table that is being replicated?

A: First drop the article from the publication using sp_droparticle, sp_dropmergearticle, or the Publication Properties - dialog box, and then drop it from the database using DROP

Q: How do I add or drop columns on a published table?

A: SQL Server supports a wide variety of schema changes on published objects, including adding and dropping columns. For example, execute ALTER TABLE … DROP COLUMN at the Publisher, and the statement is replicated to Subscribers and then executed to drop the column. Subscribers running versions of SQL Server prior to SQL Server 2005 support adding and dropping columns through the stored procedures sp_repladdcolumn and sp_repldropcolumn

Q: How do I determine if the data at Subscribers is synchronized with data at the Publisher?

A: Use validation. Validation reports on whether a given Subscriber is synchronized with the Publisher. For more information, see Validate Replicated Data. Validation does not provide information on which rows if any are not synchronized correctly, but the tablediff utility does.

Q: How do I add a table to an existing publication?

A: It is not necessary to stop activity on the publication or subscription databases in order to add a table (or another object).

Add a table to a publication through the Publication Properties - dialog box or the stored procedures sp_addarticle

and sp_addmergearticle. For more information, see Add Articles to and Drop Articles from Existing Publications.

 

Q : Does replication work in conjunction with log shipping, database mirroring and clusering?
A : Yes

Q: What options are there to delete rows on the publisher and not on the subscriber?

A: One option is to replicate stored procedure execution instead of the actual DELETE command.  You can create two different versions of the stored procedures one on the publisher that does the delete and the other on the subscriber that does not do the delete.

Another option is to not replicate DELETE commands.

Q: Explain what stored procedure sp_replcounters is used for?

A: Sp_replcounters is a system stored procedure that returns information about the transaction rate, latency, and first and last log sequence number (LSN) for each publication on a server. This is run on the publishing server. Running this stored procedure on a server that is acting as the distributor or subscribing to publications from another server will not return any data

Q: If I create a publication with one table as an article, and then change the schema of the published table (for example, by adding a column to the table), will the new schema ever be applied at the Subscribers?

A: Yes. Schema changes to tables must be made by using Transact-SQL or SQL Server Management Objects (SMO). When schema changes are made in SQL Server Management Studio, Management Studio attempts to drop and re-create the table and since you cannot drop a published objects, the schema change will fail.

Q. how to configure alerts if replication fails?
A.  Set up replication monitor to configure alerts or from replication jobs as well,

Easy way would be : replication->launch replication monitor -> click on publication-> warnings tab-> configure alerts and select type of alert as needed.

Q. Can you setup replication with always ON?
A. Yes, but with certain limitation, please follow the MS site for detailed info.

SQL Server Collation

Q. Can instance level collation be different from database collation?

A. Yes , they can be different. Database collation can be changed but server collation can’t be changed without reinstallation.

========

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.

A table and column can have different collation than database and server

Create table Table1 ([name] char(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[name2] [char] (18) COLLATE Korean_Wansung_Unicode_CS_AS_KS NOT NULL ,
[name3] [char] (15) COLLATE )

 Create database Db1 collate Latin1_General_CS_AS_KS_WS

Case sensitivity

If A and a, B and b, etc. are treated in the same way then it is case-insensitive. A computer treats A and a differently because it uses ASCII code to differentiate the input. The ASCII value of A is65, while a is 97. The ASCII value of B is 66 and b is 98.

Accent sensitivity

If a and á, o and ó are treated in the same way, then it is accent-insensitive. A computer treats a and á differently because it uses ASCII code for differentiating the input. The ASCII value of a is 97and áis 225. The ASCII value of o is 111 and ó is 243.

 

What is IO Affinity?

Q.  What is IO Affinity?

A : There are two type of affinity, IO and CPU affinity, they are together called processor affinity.

SQL is highly resource intensive, it is sometimes necessary to allocate CPU resource to sql.

If we have 4 CPUs we might need 2 CPUs to allocate to sql just for I/O operation and that is called IO affinity so it would be dedicated to input and output operation. By this way CPUs affinity and IO affinity can be set for all processor available and enhance the usage of CPUs.

It can be set through server-> processor

By default it is set to automatically set processor and IO affinity mask for all processor in this way sql engine can decide how much cpu will be used for different operation (SQL processor and IO modules.)

change data capture (CDC) and change tracking (CT)

Track Data Changes (SQL Server) (supported by 2008 and above)

SQL Server 2014 provides two features that track changes to data in a database: change data capture and change tracking. These features enable applications to determine the DML changes (insert, update, and delete operations) that were made to user tables in a database. Change data capture and change tracking can be enabled on the same database;

Using change data capture or change tracking in applications to track changes in a database, instead of developing a custom solution, has the following benefits:

·         There is reduced development time. Because functionality is available in SQL Server 2014
·         Schema changes are not required. You do not have to add columns, add triggers, or create side table in which to track deleted rows or to store change tracking information if columns cannot be added to the user tables
·         There is a built-in cleanup mechanism.
·         Functions are provided to obtain change information.
·         There is low overhead to DML operations. Synchronous change tracking will always have some overhead. However, using change tracking can help minimize the overhead. The overhead will frequently be less than that of using alternative solutions.
·         Change tracking is based on committed transactions. The order of the changes is based on transaction commit time
·         Standard tools are available that you can use to configure and manage. SQL Server 2014 provides standard DDL statements, SQL Server Management Studio, catalog views, and security permissions.

NB : All properties of change data capture and change tracking are same except CDC captures historical data also.

CDC provides historical change information for a user table by capturing both the fact that DML changes were made and the actual data that was changed. Changes are captured by using an asynchronous process that reads the transaction log and has a low impact on the system.

Check if database is already enabled for CDC :

 

USE master
SELECT [name], database_id, is_cdc_enabled
FROM sys.databases

 

Enable CDC for database;

 

USE AdventureWorks
GO
EXEC sys.sp_cdc_enable_db
GO

 

CT captures the fact that rows in a table were changed, but does not capture the data that was changed. This enables applications to determine the rows that have changed with the latest row data being obtained directly from the user tables. Therefore, change tracking is more limited in the historical questions it can answer compared to change data capture. However, for those applications that do not require the historical information, there is far less storage overhead because of the changed data not being captured. A synchronous tracking mechanism is used to track the changes. This has been designed to have minimal overhead to the DML operations.

Enable Change Tracking at the database level and then at the table level. When enabling Change Tracking at the database level, you can use the CHANGE_RETENTION (default 2 days) clause.

ALTER DATABASE LearningCT

SET CHANGE_TRACKING = ON

(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

ALTER TABLE Employee

ENABLE CHANGE_TRACKING

WITH (TRACK_COLUMNS_UPDATED = ON)

System tables which provide information about Change Tracking. sys.change_tracking_databases shows a row for each database if Change Tracking is enabled for it whereas sys.change_tracking_tables shows a row for each table if it has Change Tracking enabled in the current database only.

SQL Server creates an internal tracking table, if you want to know more about it you can query the sys.internal_tables system table.

SELECT * FROM sys.change_tracking_databases

SELECT * FROM sys.change_tracking_tables
SELECT * FROM sys.internal_tables
WHERE parent_object_id = OBJECT_ID('Employee')

use the CHANGE_TRACKING_CURRENT_VERSION function to get the current version number at the database level, possibly the higher boundary for retained change information. The other function, CHANGE_TRACKING_MIN_VALID_VERSION gives the minimum version after the information for a table change has been retained or lower boundary for a table change information

CHANGETABLE function to retrieve change information after version 0

After performing DML operation on table.

SELECT CHANGE_TRACKING_CURRENT_VERSION ()

SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('Employee'))
SELECT * FROM CHANGETABLE
(CHANGES Employee,0) as CT ORDER BY SYS_CHANGE_VERSION+

CHANGETABLE columns :

·         SYS_CHANGE_VERSION: It represents the last version number when a particular row was updated.
·         SYS_CHANGE_CREATION_VERSION: It represents the version number when a record was inserted. It will not be over-written as in case of SYS_CHANGE_VERSION.
·         SYS_CHANGE_OPERATION : It represents the DML operations (I=INSERT, U=UPDATE and D=DELETE)
·         SYS_CHANGE_COLUMNS: It represents all the columns impacted since last baseline version. This column will have values only for UPDATE operations and if columns are not impacted during update it will have NULL
·         : It represents the primary key columns of your tracked user table. You can join your tracked user table with this columns to get only changed data from the tracked user table.

Notes

You need to have a primary key on a table in order to enable Change Tracking. If you try to drop a primary key constraint after enabling Change Tracking, you will get an error.

• Primary key update is not treated as update, but rather it will be treated as deletion of old value and insertion of new value in case of Change Tracking.
• You need to enable Change Tracking on a database level first then you can enable it on a table level.
• In order to disable Change Tracking on a database level, you need to disable it on all tables, within specified database, if they have Change Tracking enabled.
• Change Tracking works properly only if a database has a compatibility level 90 or higher.
• Similar to the Filtered Index, you cannot specify a filter predicate on a table for Change Tracking.
• Column adds and drops will not be tracked (no DDL), only the updates to the column are tracked.
• If you perform TRUNCATE on Change Tracking enabled table, no tracking is done for the records deleted, but rather a minimum valid version is updated and your application data will need to be re-initialized.
• Change Tracking puts extra overhead on DML operations as additional information is also recorded as part of the DML operation. Though it has been designed to have minimal impact compared to other alternatives to track changes.
• An internal table is created (created in the same file group as the user table) for each Change Tracking enabled table. Also one internal transaction table is created in the database which stores one row for each committed transaction. Hence you will have additional storage requirements for these internal tables.

 

SQL Server Always On (AO) Q&A:

Q. How many SQL nodes can be configured in AO CONFIGURATION?

A: Max 5, 1 for primary, 4 for secondary replica. Max 2 can be part of automatic failover, max  2 can part of synchronous replica, only primary can be read write and rest will be read only.

Q. What is the use of listener in AO?

A. By defining listener a virtual network name (VNN) in AO one has no need to use server name to connect current primary replica, using listener name connection can be automatically redirected to primary replica and there would be no changes in application connection string after failover.

Listener consists of a Domain Name System (DNS) listener name, listener port designation, and one or more IP addresses. Only the TCP protocol is supported by availability group listener

When you create a new availability group listener it becomes a resource in a cluster with an associated virtual network name (VNN), virtual IP (VIP), and availability group dependency

One can set default port to 1433 in order to allow for simplicity of the client connection strings. If using 1433, you do not need to designate a port number in a connection string. Also, since each availability group listener will have a separate virtual network name, each availability group listener configured on a single WSFC can be configured to reference the same default port of 1433.

Q. What is the difference between read-intent and read only replica?

A.  Read-intent only: Only read-only connections are allowed
     Yes: All connections are allowed, but only for read-only access
So the main difference between Read-intent-only and Yes is that the later allows all connections, regardless if it read or read/write, but only read access will be granted.

Q. what are the availability modes in AO?

A.         Asynchronous-commit mode

Under asynchronous-commit mode, the primary replica commits transactions without waiting for acknowledgement that an asynchronous-commit secondary replica has hardened the log. Asynchronous-commit mode minimizes transaction latency on the secondary databases but allows them to lag behind the primary databases, making some data loss possible.

       Synchronous-commit mode

Under synchronous-commit mode, before committing transactions, a synchronous-commit primary replica waits for a synchronous-commit secondary replica to acknowledge that it has finished hardening the log. Synchronous-commit mode ensures that once a given secondary database is synchronized with the primary database, committed transactions are fully protected. This protection comes at the cost of increased transaction latency.

 Q. What is License policy for Always on server?

A. SQL Server 2012 has been released with a new license model. With SQL Server 2012 AlwaysOn’s ability to have multiple secondaries you need to take into account the licensing when you are going to be implementing multiple secondaries. The license model requires you to license your Active (Primary) SQL Server in your AlwaysOn Cluster. You are allowed one Passive (Secondary) server that you do not need to license. If you have more than one secondary server, you need to license that server whether it is active or passive.For example: If we were to have one Primary Server (Active), three Secondary Servers (one Active, two Passive) we would be required to license three of the four servers.

Q. what is Session timeout period.

A. The session-timeout period is an availability-replica property that determines how long connection with another availability replica can remain inactive before the connection is closed. The primary and secondary replicas ping each other to signal that they are still active. Receiving a ping from the other replica during the timeout period indicates that the connection is still open and that the server instances are communicating. On receiving a ping, an availability replica resets its session-timeout counter on that connection.

The session-timeout period prevents either replica from waiting indefinitely to receive a ping from the other replica. If no ping is received from the other replica within the session-timeout period, the replica times out. Its connection is closed, and the timed-out replica enters the DISCONNECTED state. Even if a disconnected replica is configured for synchronous-commit mode, transactions will not wait for that replica to reconnect and resynchronize.

The default session-timeout period for each availability replica is 10 seconds. This value is user-configurable, with a minimum of 5 seconds. Generally, we recommend that you keep the time-out period at 10 seconds or greater. Setting the value to less than 10 seconds creates the possibility of a heavily loaded system declaring a false failure.

Q. Number of Databases in a Availability Group (AG)?

A. There is no limit of database it all depend on workload.

Q. What if one database fails out many in one availability group?

A. No, failover depends over instance level. The AG uses Windows Clustering for detection of failure events

Q. Recovery model for AO?

A. Full recovery model.

Q. Is cluster required for AO?    

A.            Deploying AlwaysOn Availability Groups requires a Windows Server Failover Clustering (WSFC) cluster. To be enabled for AlwaysOn Availability Groups, an instance of SQL Server must reside on a WSFC node, and the WSFC cluster and node must be online. Each availability replica of a given availability group must reside on a different node of the same WSFC cluster,The WSFC cluster monitors this resource group to evaluate the health of the primary replica.

The quorum for AlwaysOn Availability Groups is based on all nodes in the WSFC cluster regardless of whether a given cluster node hosts any availability replicas. In contrast to database mirroring, there is no witness role in AlwaysOn Availability Groups.

The overall health of a WSFC cluster is determined by the votes of quorum of nodes in the cluster. If the WSFC cluster goes offline because of an unplanned disaster, or due to a persistent hardware or communications failure, manual administrative intervention is required. A Windows Server or WSFC cluster administrator will need to force a quorum and then bring the surviving cluster nodes back online in a non-fault-tolerant configuration

An availability replica can be hosted by either a standalone instance of SQL Server or an FCI instance. Only one FCI partner can host a replica for a given availability group. When an availability replica is running on an FCI, the possible owners list for the availability group will contain only the active FCI node.

AlwaysOn Availability Groups does not depend on any form of shared storage. However, if one use a SQL Server failover cluster instance (FCI) to host one or more availability replicas, each of those FCIs will require shared storage as per standard SQL Server failover cluster instance installation.

Q. Can we set failover to automatic for AO replica?
A. yes for standalone instance on clustered environment, SQL Server Failover Cluster Instances (FCIs) do not support automatic failover by availability groups, so any availability replica that is hosted by an FCI can only be configured for manual failover.

Q. How to change the failover mode for AO?
A. ALTER AVAILABILITY GROUP MyAG MODIFY REPLICA ON 'COMPUTER01' WITH  (FAILOVER_MODE = AUTOMATIC);

 It should always be performed on primary replica.

Q. Can AO work for nodes in different geographic location?
A. can have geographically remote nodes participating in one AG, but they must be members of the same AD domain.

Q. what are the different types of failover?
A. Failover event is when your primary replica fails. In that case, Secondary node which can be automatically promoted to Primary, will be promoted automatically. Such nodes are known as Failover Targets. You may also have secondary nodes which may not be automatically promoted to primary. The new primary replica then recovers its databases and makes them available to users of the availability group.

1. Automatic failover (without data loss). Automatic failover is only available when both the primary replica and the secondary replica are running in synchronous-commit mode, and the failover mode is set to automatic. In automatic failover mode, failure of the primary replica causes failover to the secondary replica without the need for administrator intervention. No data loss will occur on failover.
2. Planned manual failover (without data loss). Planned manual failover is only available when both the primary replica and the secondary replica are running in synchronous-commit mode. In planned manual failover mode, a database administrator must issue a failover command to initiate failover. No data loss will occur on failover.
3. Forced manual failover (with possible data loss). Forced manual failover is the only failover type that you can use for replicas that are in asynchronous-commit mode. You must initiate forced manual failover manually. Any transactions that were committed on the primary replica, but which the secondary replica has not yet written to its log, will be lost. You can also use forced manual failover for replicas that are in synchronous-commit mode when the secondary replica is not showing as synchronized with the primary replica

Q. how to perform manual failover?

A. It requires ALTER AVAILABILITY GROUP permission on the availability group, CONTROL AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.

Connect to a server instance that hosts a replica whose role is in the SECONDARY or RESOLVING state in the availability group that that needs to be failed over

Forced manual failover:

Data loss is possible during the forced failover of an availability group. In addition, if the primary replica is running when you initiate a forced failover, clients might still be connected to former primary databases

ALTER AVAILABILITY GROUP AccountsAG FORCE_FAILOVER_ALLOW_DATA_LOSS;

Planned manual failover:

The target secondary replica and the primary replica must both be running in synchronous-commit availability mode.

ALTER AVAILABILITY GROUP MyAg FAILOVER;

Q. What are DMVs used for AO?

A. select * from sys.dm_hadr_cluster

select * from sys.dm_hadr_cluster_members
select * from sys.dm_hadr_cluster_networks
select * from sys.availability_groups
select * from sys.availability_groups_cluster
select * from sys.dm_hadr_availability_group_states
select * from sys.availability_replicas
select * from sys.dm_hadr_availability_replica_cluster_nodes
select * from sys.dm_hadr_availability_replica_cluster_states
select * from sys.dm_hadr_availability_replica_states
select * from sys.dm_hadr_auto_page_repair
select * from sys.dm_hadr_database_replica_states
select * from sys.dm_hadr_database_replica_cluster_states
select * from sys.availability_group_listener_ip_addresses
select * from sys.availability_group_listeners
select * from sys.dm_tcp_listener_states


Q. Can we delete primary or secondary replica database?

A. This task is supported only on secondary replicas. You must be connected to the server instance that hosts the secondary replica from which the database is to be removed

ALTER DATABASE MyDb2 SET HADR OFF;