In the simple model the transaction log automatically truncates itself when the data file has reached certain checkpoints. The benefit is that the log is kept small, but it does so at a cost. Database restores can only occur to the exact point that the last full backup was taken. With a simple recovery model,... » read more
The tuning advisor will analyze the database and make recommendations for indexesthat would be beneficial to add or remove. It even provides the TSQL commands that will create the suggested indexes. The Microsoft Database Engine Tuning Advisor (DTA) analyzes databases and makes recommendations that you can use to optimize query performance. You can use the... » read more
On data tables with a large number of columns, it might be beneficial to consider a column store index. As the name would imply, column store indexes organize the columns of a data table, rather than the rows. It’s a subtle distinction, but this arrangement allows SQL Server to fit more values in memory and... » read more
You can create a partitioned table or index in SQL Server 2017 by using SQL Server Management Studio or Transact-SQL. The data in partitioned tables and indexes is horizontally divided into units that can be spread across more than one filegroup in a database. Partitioning can make large tables and indexes more manageable and scalable.... » read more
Here’s a few guidelines to get started. Indexes should almost always be built on columns used as primary keys, foreign keys, or frequently used in query join and where clauses. Tables with frequent updates should have very carefully chosen indexes, since each additional index will dramatically reduce write speeds to the table. Very large and... » read more
Instead of being a SQL Server capability, though,RAID is a feature of the hardware that powers the server. RAID stands for Redundant Array of Independent Disks, and it’s configured by the operating system. RAID arrays use multiple physical hard drives, provide increased disk read and write speeds, more data redundancy protection, or both. RAID 5... » read more
SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually. An optional third server instance, known as the monitor server, records the history and status of backup and... » read more
Replication is a set of technologies for copying and distributing data and database objects from one database to another, and then synchronizing between those databases to help maintain consistency of the data. Using replication, you can distribute data to different locations and even to remote or mobile users over local and wide area networks, dial-up... » read more
In a nutshell, the three main steps involved in moving a database file to new location are: Set database to single user mode. Detach the database. Move database file to new location. Reattach the database by referencing the new location of the database file. Set database to multiple user mode. Note: There are 2 ways to... » read more
Copy–only backups are independent backup files that do not disrupt the regular Log Sequence Number (LSN) routine. This means that backup chains and restore sequences won’t be affected by the copy–only backup. A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups... » read more