In a post about how update works I showed what happened when run an update with the same values. SQL Server is smart enough to see that and not changing anything and register minimum log. Another day I saw a comment to avoid updating records when none of the values are changing. Avoid updating records when … Continue reading How update works in transactional replication?
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
SqlPackage a tool to import/export SQL Server and Azure SQL DB
SqlPackage is a command-line utility that you can use for exporting and importing operations in both on-premises SQL Server databases and in cloud databases. SqlPackage supports the following operations: Extract. Creates a database snapshot DACPAC file from a SQL Server database or from Azure SQL Database. Publish. Updates the schema in a live database to … Continue reading SqlPackage a tool to import/export SQL Server and Azure SQL DB
What is Stretch Database?
Stretch Database is a feature of SQL Server where data can be split between on-premises storage and cloud storage. With Stretch Database, cold, historical data is kept in the cloud and active data is kept on-premises for maximum performance. Stretch Database requires no changes to client applications or existing Transact-SQL queries, so you can implement … Continue reading What is Stretch Database?
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
TempDB summary
Tempdb is a special database available as a resource to all users of a SQL Server instance, you use it to hold temporary objects that users, or the database engine, create. In many respects, tempdb files are identical to the files that make up other SQL Server databases. From the perspective of storage I/O, … Continue reading TempDB summary
Difference between Lock and Latch
Locks everywhere is a good start to understand how SQL Server provides logical consistency. Every operation has a lock and latch. What does Latch mean? Latch protects memory on Buffer Pool, is a method that provides physical consistency. SQL Server does operations in memory, that means, it read the page from disk and put that … Continue reading Difference between Lock and Latch
