Database migration methods

When thinking about migration the most common issue is downtime. There are some methods that require downtime like backup/restore or less downtime using Log Shipping. You can choose several different methods to migrate your database.  For example, methods that require downtime include: Backup the database, manually copy the backup file to the machine and then … Continue reading Database migration methods

Moving the msdb, model, and tempdb databases files

All system databases, except the resource database, can be moved to new locations to help balance I/O load. To move the msdb, model, and tempdb databases, perform the following steps: For each file to be moved, execute the ALTER DATABASE … MODIFY FILE statement. Stop the instance of SQL Server. Move the files to the … Continue reading Moving the msdb, model, and tempdb databases files

How SQL Server stores data?

SQL Server stores data in heaps or b-tree structures. Heaps are unordered set and balanced trees are ordered by their keys. Heaps and b-tree use collection of pages within the structure and it's called allocation units. IN_ROW_DATA -> contains all data. LOB_DATA -> structure for large objects used to stored in xml, varchar(max), nvarchar(max), varbinary(max) … Continue reading How SQL Server stores data?