Transactional Replication Configuration is not that easy. Replication is always table level. Lot of maintenance work. There is a latency and latency may increase in busy system. Need to configure it for each table. Not a high availability option.You can use it as a DR strategy. AlwaysOn High Availability and Disaster recovery strategy.Built on top of Windows Failover Clustering... » read more
SQL Server AlwaysOn provides a high-availability and Disaster-recovery solution for SQL Server. An availability group supports one set of primary databases and one to eight sets of corresponding secondary databases. Each set of availability database is hosted by an availability replica. Two types of availability replicas exist: a single primary replica. which hosts the primary databases, and one... » read more
Error: The filegroup ‘xxxxxx’ cannot be removed because it is not empty. Fix: Remember to remove the partition the filegroup belongs to.
ALLOW_PAGE_LOCKS = { ON | OFF }Applies to: SQL Server 2008 through SQL Server 2017. Specifies whether page locks are allowed. The default is ON. ONPage locks are allowed when accessing the index. The Database Engine determines when page locks are used. OFFPage locks are not used.
ALLOW_ROW_LOCKS = { ON | OFF }Applies to: SQL Server 2008 through SQL Server 2017. Specifies whether row locks are allowed. The default is ON. ONRow locks are allowed when accessing the index. The Database Engine determines when row locks are used. OFFRow locks are not used.
IGNORE_DUP_KEY = { ON | OFF }Specifies the response type when an insert operation attempts to insert duplicate key values into a unique index. The IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. The option has no effect when executing CREATE INDEX, ALTER INDEX, or UPDATE. The default is OFF. ONA warning message occurs when duplicate... » read more
STATISTICS_NORECOMPUTE = { ON | OFF }Specifies whether statistics are recomputed. The default is OFF. ONOut-of-date statistics are not automatically recomputed. OFFAutomatic statistics updating are enabled. The general recommendation to leave auto-update stats turned on (STATISTICS_NORECOMPUTE = OFF, which is the default) is for safety reasons, because if this is turned off and nothing is manually updating the stats,... » read more
From MSDN: PAD_INDEX = { ON | OFF } Specifies index padding. The default is OFF. ON: The percentage of free space that is specified by fillfactor is applied to the intermediate-level pages of the index. OFF or fillfactor is not specified: The intermediate-level pages are filled to near capacity, leaving sufficient space for at least... » read more
Object Creation File Group (with correct folder structure) Partition Function Partition Schema Partition Table Note: If the database is part of Always On, make sure the folder structure has been created on the secondary servers as well. Create File Group and Associate File to File Group ALTER DATABASE Test02 ADD FILEGROUP [fg201701_tbImageFpMatchARCH] GO ALTER DATABASE... » read more
Use the “Date” datatype rather than “Int” datatype. Int (formatted in YYYYMMDD) used to be the recommended format for partitioning, as it was cheaper (@ 4 bytes/row) than datetime (@ 8 bytes/row). Date is 3 bytes/row and is in a natural date format. Also, INT is a pain in the butt to query on as... » read more