Execution runtime information for developers ;)

Developers should use this statistics to get more information about what they just did and pay attention to high physical or logical read values.

SQL Server provides commands that are used to return query statistics like SET STATISTICS IO, TIME.

The IO command return information about physical, logical for tables referenced in the query and also worktables, which means tables in tempdb for the duration of the query and are removed automatically when it has finished the operation.

Using statistics time will show the parse, compile time for the query and the actual execution time after the query results. It’s useful to measure the amount of time a query takes to execute from end to end.

Using SQL Operations Studio Agent

SQL Operations Studio Agent is visual way to see, edit and create SQL Server jobs and everybody was asking this feature when SQL Operations Studio appears.

To install this the agent follow the link https://docs.microsoft.com/en-us/sql/sql-operations-studio/sql-server-agent-extension?view=sql-server-2017.

sqlops01

Installing the agent 

sqlops02

With the extension installed, right-click on the instance -> Manage to see the server jobs 

sqlops03

But, not everything is working. I tried to edit a job step and didn’t work. For example, the Ola hallengren maintenace scripts after created wasn’t possible to change using the GUI differently than using SSMS.

Even trying to using the GUI to create a new step didn’t work and I changed using TSQL.

sqlops05
sqlops04

The script is available in my Git here to download. After changed the script I succeeded ran it.

With extensions we have more features available to work with SQL Operations Studio and is improving the utilization. Also you can create your own widget. In my tip on MSSQLTips.com I’m showing how to create it.

I recommend install SQL Server Agent, Profiler, Reports and certainly whoisactive

sqlops06

How to install SQL Server SQLPS Powershell module

SQLPS module to Powershell helps to do so many things on SQL Server without SSMS and the main objective is to automate tasks for your environment.

This module isn’t installed by default, so there are a couple of steps before use it. First, install the module and the simple cmdlet install-module SQLPS won’t work.

To install the module you need install a couple of things (if you have SQL Server instance there is already installed).

The installation is quite simple and isn’t necessary to restart the system. There are 3 files necessary and need to install in the sequence below.

With all installed let’s try to import the module on powershell.

2018-08-01_20-36-01

Actually that didn’t work (yet). In the error message shows it’s not allowed to run scripts in my system.

Next step is to change the execution policy to run scripts in the system. Let’s try it using the cmdlets Get-ExecutionPolicy to know the actual value and Set-ExecutionPolicy to change it.  ( more information )

2018-08-01_20-45-23

Ok, second error and this time because I’m trying to change to RemoteSigned and only as administrator I can change it.

2018-08-01_20-54-28

Running as administrator the local machine execution policy has changed and the SQLPS module can be imported and use the cmdlets.

2018-08-01_21-00-16

Troubleshooting Transactional Replication – part 1

The common way to replicate data and objects from one server to another is using transactional replication. As you know, SQL Server processes all actions within the database using Transact-SQL statements. Each completed statement is called a transaction. In transactional replication, each committed transaction is replicated to the subscriber as it occurs.

trnsact
“Sometimes” can occur a problem and the process don’t work accordingly. The fist step I like to do is run sp_whoisactive in the servers (Publisher, Distributor and Subscriber), you can see if there are any process blocking the replication.

Next, is to see the publisher and subscriber agents, if there is any issue they will show, like for example login locked.

To have more information what’s happening I like to query the system view MSrepl_Transactions in the distribution database, this view contains one row for each replicated transaction and with that information run the procedure sp_browsereplcmds specifying the seqno start @xact_seqno_start = N’0x0000014F000034B0001C’ for example.

The procedure sp_browserreplcmds returns a record set with the commands stored in the distribution database. It show like a table like below.

2018-07-29_20-31-59

How update works in transactional replication?

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 none of the values are changing. This still does a write and, if the table is in replication or change tracking, still causes the row to be propagated out to other servers. If you are updating a potentially large number of records, make sure to only update the ones where the new value doesn’t equal the old value.

Let’s see how the update behavior when update 10 millions rows but without any change and see if will be any row propagation to another server.

objectexplorer

My Lab contains two servers and a demo database replicated from SQL01 to SQL02.

It’s configured a transaction replication to send the articles and I’m using a table created with the code:

CREATE TABLE tblMillionsRows (
    id BIGINT NOT NULL IDENTITY PRIMARY KEY,
    largeColumn NVARCHAR(MAX) DEFAULT REPLICATE('TESTE',100),
    smallColumn NVARCHAR(150) DEFAULT 'DEMO',
    dateColumn DATETIME
        DEFAULT GETDATE()
);
GO

Inserting rows:

INSERT INTO dbo.tblMillionsRows (
    largeColumn,
    smallColumn,
    dateColumn
)
DEFAULT VALUES
GO 2000000

It will take a while to insert all rows. The next step is add this table in the replication.

DECLARE @publication    AS sysname;
DECLARE @table AS sysname;
DECLARE @filterclause AS nvarchar(500);
DECLARE @filtername AS nvarchar(386);
DECLARE @schemaowner AS sysname;
SET @publication = N'SQL01_demo_tb01'; 
SET @table = N'tblMillionsRows';
SET @schemaowner = N'dbo';

EXEC sp_addarticle 
	@publication = @publication, 
	@article = @table, 
	@source_object = @table,
	@source_owner = @schemaowner, 
	@schema_option = 0x80030F3,
	@vertical_partition = N'true', 
	@type = N'logbased',
	@filter_clause = @filterclause;

EXEC sp_articlecolumn 
	@publication = @publication, 
	@article = @table;

EXEC sp_startpublication_snapshot 
	@publication = 'SQL01_demo_tb01', 
	@publisher =  'SQL01'   

With all set, let’s do some tests. First updating the heap table and seeing if something is replicated.

UPDATE tblMillionsRows SET smallColumn = N'DEMO' WHERE id < 10000
GO

After the update, I ran the script below to see the transactions and commands available to replicate and nothing changed.

SELECT * FROM distribution.dbo.MSrepl_Commands 
SELECT * FROM distribution.dbo.MSrepl_Transactions 
EXEC sp_browsereplcmds 

But, when I change one row running the update below I could see a new command to replicate in the another server.

UPDATE tblMillionsRows SET smallColumn = N'DEMO1' WHERE id < 10000
GO

replicationcommands

In conclusion, SQL Server won’t replicate updates that don’t change the value. You can see more about updates in my post.

Be careful when doing updates, because if the table is replicated and you change millions rows the transaction replication will create one command for each updated row.

 

Database migration methods

migration-logo-2389341When 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 restore the database there. This method is simple and the use of compression minimizes the time that is required.
  • Perform a backup to an Azure Blob storage account, and then restore the database from that backup. This method removes the necessity to manually copy the backup file.
  • Detach the database, copy the files to an Azure Blob storage and then attach them to the SQL Server instance. Use this method if you plan to store database files in Azure Blob storage permanently instead of on a hard disk.
  • Log Shipping, use a backup/restore process and the downtime depends on the transaction log file backup copying a small file in the process. This method is simple and is necessary a connection between the machines and permission to copy the backup files.

Methods that not require downtime:

  • AlwaysOn, adding a new secondary replica after replication has completed, you can failover to make the machine the primary replica. For Azure virtual machine use the Add Azure Replica Wizard.
  • Transactional Replication will minimize downtime, but don’t have an AlwaysOn deployment in your SQL Server system.

If you need help to decide which method is better for your scenario, feel free to contact me.

SqlPackage a tool to import/export SQL Server and Azure SQL DB

repair_database-512SqlPackage 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 match the schema in a DACPAC
    file. If the database does not exist on the destination server, the publish operation
    creates it.
  • Export. Exports both schema and data from a SQL Server database or from Azure SQL Database into a BACPAC file.
  • Import. Imports the schema and data from a BACPAC into a new database.
  • DeployReport. Creates an XML report that describes the changes that would be made by a publish operation.
  • DriftReport. Creates an XML report of the changes that have been made to a registered database.
  • Script. Creates a Transact-SQL script that you can use to update the schema of a target database to match the schema of a source database.
    Use the /Action: or /a parameter to specify which action to execute.

SqlPackage example to import from bacpac file to Azure
sqlpackage.exe /Action:Import /tsn:tcp:.database.windows.net,1433 /tdn: /tu: /tp: /sf: /p:DatabaseEdition=Premium /p:DatabaseServiceObjective=P4 /p:Storage=File

More about SqlPackage parameters:
https://msdn.microsoft.com/en-us/library/hh550080%28v=vs.103%29.aspx?f=255&MSPPError=-2147217396

SQL Operations Studio Dashboards

SQL Operations Studio is a free tool that runs on Windows, macOS, and Linux, for managing SQL Server databases.

I have a great experience running this tool on Linux CentOS and you can build server and database management dashboards  like below.dashboard02dashboard03

To create custom dashboards check out my tip https://www.mssqltips.com/sqlservertip/5434/customize-sql-operations-studio-dashboards/

 

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 it seamlessly for existing applications. Stretch Database can reduce on-premises storage requirements both for data and associated backups. Backups of on-premises data are smaller and therefore run quicker than standard backups. Data in the cloud is backed up automatically.

With Stretch Database, cold historic data remains available for users to query, although there might be a small amount of additional latency associated with queries.

img2

Implement a Stretch Database
You can implement Stretch Database entirely within SQL Server Management Studio; you do not need to pre-configure servers or storage within Microsoft Azure.
Implementing Stretch Database involves the following steps:

  1. Start Microsoft SQL Server Management Studio and connect to the instance of SQL Server.
  2. In Object Explorer, expand Databases.
  3. Right-click the database, point to Tasks, point to Stretch, and then click Enable.

scretch

scretch2

scretch3scretch4scretch5Complete the steps in the Enable Database for Stretch wizard to create a  Database Master Key; identify the appropriate tables and configure the Microsoft Azure deployment.

After implementing Stretch Database, you can monitor it from SQL Server  Management Studio.

In Object Explorer, expand Databases, right-click the stretch-enabled database, point to Tasks, point to Stretch, and then click Monitor to open the Stretch Database Monitor. This monitor shows information about both the local and Azure SQL instances, along with data migration status.

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 new location (this step is not necessary for tempdb, as its files are recreated automatically on startup).
  • Restart the instance of SQL Server.

The process for moving the master database is different from the process for other databases. To move the master database, perform the following steps:

  • Open SQL Server Configuration Manager.
  • In the SQL Server Services node, right-click the instance of SQL Server, click Properties, and then click the Startup Parameters tab.
  • Edit the Startup Parameters values to point to the planned location for the master database data (-d parameter) and log (-l parameter) files.
  • Stop the instance of SQL Server.
  • Move the master.mdf and mastlog.ldf files to the new location.
  • Restart the instance of SQL Server