Browsing:

Category: SQL Performance

Azure, Linked server, SQL, SQL Performance

SQL2016 Stretch database. Yes, it’s another linked server

September 17, 2016

Your manager always wants to keep all the data...now with bigdata being a thing and algoritmes are used more efficient (IOT), people want to hoard data even more, but it needs to be online to be valuable. If you have to restore the data first, your data is not very valuable. But all this extra data also slows down your database performance...and gives the dba'ers extra challenges....but not anymore.! Well, at least when your boss got a big wallet and don't mind spending it on a SQL buget.

In SQL2016 Microsoft introduced the stretch database. which give you live access to 'archived' data and makes it feel like it's on premise. And  it's a secretly just a linked server! A linked server between mssql and Azure.LS+azure

How does this work, and is it safe?

Yes! since it works on secure linked server technology and it stores an encryption key on the on-premises sqlserver, it's suitable for all your cold data, not hot, because it does slow down the query speed, but it will not clogg your network and your data will be available at all times, even when the data is being stretched over to Azure the data still is fully query-able.

First ask yourself, Do you really need to keep this data? If yes, How do you create a stretchdatabase? Well, this is easy!

Hesitations? use this link for info on the MS Stretch database advisor.

I use a simple test database, the adventureworks2016 ctp3.

Before we can start, check if the data archive function is enabled, if not (0) enable it.

####Check status of Remote data archive####
SELECT @@VERSION AS 'SQL Server Version'; 
EXEC sp_configure 'remote data archive'; 
GO 
####Activate Remote data archive####
EXEC sp_configure 'remote data archive' , '1'; 
GO 
RECONFIGURE; 
GO

Next we can create a master key to encrypt all your data locally, this way, the data can't be read in Azure.
Note: You can also create a masterkey from the stretchdb wizard, it's up to you. I'll show both options.

USE AdventureWorks2016CTP3; 
GO 

CREATE MASTER KEY ENCRYPTION BY PASSWORD='stretchdb2016'; 
GO

Next I will create a simple table  and populate it with some data, to keep the demo quick and simple :

CREATE TABLE dbo.Stretchtable
(
FirstName VARCHAR (50),
LastName VARCHAR (50)
);
GO


Use [AdventureWorks2016CTP3]
GO
INSERT INTO dbo.Stretchtable (FirstName, lastName)
VALUES ('Reed', 'Richards'), ('Benjamin', 'Grimm'), ('Sue', 'Storm'), 
 ('Johnny', 'Storm'), ('Victor', 'Von Doom'), ('Willie','Lumpkin');
 GO

Keep your Azure account ready! I use the SQL logical server resource, you need to create a resource before you can stretch the database.

Azure resource

 

Select the table you will be stretching to Azure. Now enable the table for stretching and the wizard will open up.

Enable stretch database

It's a Hybride archive - You can stretch the whole table or filter out the (c)old data from a massive table.
4_EnableStretchTable

 

Next thing you create a master key, if you didn't make it before, if you made it already, It will ask you to fill in the master password.Masterkey_StretchTable
10_LoginStretchTable

Fill in the IP range for a firewall rule.IP_Azure

12_EnableStretchTable

Hey Ho, let's go, that was easy!

Now the table will start to stretch, if you want to see if it stretched yet, try a select query and check the executionplan.
14_EnableStretchTable

Want your data back? No problem, You can bring back the data to your on-premise server, if you don't want to use Azure. Because, when you delete your azure account, the data will be gone too!

bringbackmydatabringbackmydata2bringbackmydata3

 

Note: Data storage can be expensive on Azure, but this function gives us a positive view on the ever growing databases and it's possibilities.

Code, SQL, SQL Performance, Tooltip

Move SQL Tempdb files – Common errors

October 5, 2015

Do you keep getting errors and timeouts when doing queries with sort?
My first guess wil be to check out the location of my tempdb files, is the disc space running out?db

Run query on the correct database
EXEC sp_helpfile GO
Tip: you can also view it under properties.

USE master; GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'F:\MSSQL\DATA\tempdb.mdf'); GO

 

ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'F:\MSSQL\DATA\templog.ldf'); GO

Restart sql services: 'stop and start'

The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

rerun the sp query to verify the correct location fort he tempdb files
Now delete the old files or in my case rename them before finally remove them.
Next we are gonna change the properties for the autogrowth.
Error cant acces properties of tempdb:

DBCC UPDATEUSAGE(tempdb)
temp_1

Important Note on common ERRORS: SQL Server doesn’t support moving TempDB Database using backup/restore and by using detach database methods.

Error Message Received when you try Backup and Restore Method

Msg 3147, Level 16, State 3, Line 1
Backup and restore operations are not allowed on database tempdb.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Error Message Received when you try Detach Method

Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.

Code, SQL, SQL Performance

Report server log file eating up diskspace – ReportServerTempDb

January 21, 2015

I got a 400Gb log file for my birthday today.

This 400gb made me mad, It's just a temp db. And all processes are down this morning. Looking around on the internet, this seems to be a common known problem, some even  state this problem is acknowledged by Microsoft.i can be.. engineer

After reading some blogs on possible solutions. I decided I need to solve the disk problem first. First checks you need to do before you start the troubleshoot:

You need to rule out that you didn't bring this over yourself, the ReportTempDb needs to be in Simple mode, this means there are no logbackups and the it should automatically reclaim space in your logfiles, well, this is how it should work. But in this case the logspace keeps growing. Indicating something is wrong with the reportserver, when it already is in Simple mode.

Also It could be worthwhile to check which reports are costing you, since not only your log space is invaded by report servers, your CPU is probably a victim too..

SELECT 
	sd.ReportPath,
	COUNT(s.SegmentId) as CountOfSegmentId
FROM 
	[ReportServerTempDB].dbo.Segment s 
INNER JOIN 
	[ReportServerTempDB].dbo.ChunkSegmentMapping m 
ON 
	m.SegmentId = s.SegmentId
INNER JOIN 
	[ReportServerTempDB].dbo.SegmentedChunk c 
ON 
	c.ChunkId = m.ChunkId
INNER JOIN 
	[ReportServerTempDB].dbo.SessionData sd 
ON 
	sd.SnapshotDataID = c.SnapshotDataId 
GROUP BY	
	sd.ReportPath
ORDER BY 
	CountOfSegmentId DESC;

After these checks, we have 2 routes to go, but today we'll will start with the quick and dirty one, solve the disk shortage, since all processes are put to a stop due to this 'Stay Puft' logfile. We need to shrink this beast, I should tell you, shrinking your database is pure evil and risk taking. You should never ever ever do this! ...Unless even Microsoft says it's ok. And it's just tempdata. It should've just deleted itself.

First, we are gonna run a full backup of the ReportserverTempDb, to an external location, ofcourse. Once this is done, we canstart with the clean up of the logfile.

Bring your database level to full mode, this allows you to alter the filegroups.

Right click tthe ReportServerTempDb database Go to the option shrink --> files and check the available free space and check the release unused space button.

This should give you some space, in my case, 400Gb, whoohoo, Dobby is free! Don't forget to put your ReportServertempDb back into simple mode!

You could also shrink the log file with the 'shrink file' option, but like I said earlier, it's better to avoid this option, because it could lead to faulty logfiles when you need to recover. But I case of this ReportServerTempDb the risk is low.

Now that we have got some diskspace back, we need to dig deeper to find out the cause of this log file eating up all your precious disk space. Next topic will be how we can fix this Stay Puft log file growth.

SQL, SQL Performance

Create and restore a database with multiple secondary data files

January 3, 2015

SQL databases are all about performance, if databases grow very large, it might be a good idea to spread your files across multiple disks.

A database can consist of three files, .mdf, .ldf and .ndf, in which .ndf is a user defined secondary data file, these files can be created to split up a fast growing .mdf. For example you create 3 files, Data1.ndf, Data2.ndf, and Data3.ndf spread on three disk drives and assigned to a filegroup e.g.'fg1'. Tables can be created specifically on the filegroup 'fg1'. The queries for data from the table can be spread across multiple disks; this will improve your database performance.

Here's a script to create a database with multiple filegroups:

USE [master]
GO

/****** Object:  Database [AdventurePark]    Script Date: 01/01/2015 21:13:57 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'AdventurePark')
DROP DATABASE [AdventurePark]
GO

USE [master]
GO

/****** Object:  Database [AdventurePark]    Script Date: 01/01/2015 21:13:57 ******/
CREATE DATABASE [AdventurePark] ON  PRIMARY 
( NAME = N'AdventureParkPrimary', FILENAME = N'D:\DATA\AdventurePark.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
 FILEGROUP [Data]  DEFAULT 
( NAME = N'AdventureParkData', FILENAME = N'D:\DATA\AdventurePark_1.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
 FILEGROUP [Files2008] 
( NAME = N'AdventureParkFiles2008_1', FILENAME = N'E:\DATA\AdventurePark_2.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
( NAME = N'AdventureParkFiles2008_2', FILENAME = N'E:\DATA\AdventurePark_3.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
 FILEGROUP [Files2009] 
( NAME = N'AdventureParkFiles2009_1', FILENAME = N'E:\DATA\AdventurePark_4.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
( NAME = N'AdventureParkFiles2009_2', FILENAME = N'E:\DATA\AdventurePark_5.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
 FILEGROUP [Files2010] 
( NAME = N'AdventureParkFiles2010_1', FILENAME = N'E:\DATA\AdventurePark_6.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
( NAME = N'AdventureParkFiles2010_2', FILENAME = N'E:\DATA\AdventurePark_7.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
 FILEGROUP [FIles2011] 
( NAME = N'AdventureParkFiles2011_1', FILENAME = N'E:\DATA\AdventurePark_8.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
( NAME = N'AdventureParkFiles2011_2', FILENAME = N'E:\DATA\AdventurePark_9.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
 FILEGROUP [Files2012] 
( NAME = N'AdventureParkFiles2012_1', FILENAME = N'E:\DATA\AdventurePark_10.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
( NAME = N'AdventureParkFiles2012_2', FILENAME = N'E:\DATA\AdventurePark_11.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
 FILEGROUP [Files2013] 
( NAME = N'AdventureParkFiles2013_1', FILENAME = N'E:\DATA\AdventurePark_12.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
( NAME = N'AdventureParkFiles2013_2', FILENAME = N'E:\DATA\AdventurePark_13.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
 FILEGROUP [Files2014] 
( NAME = N'AdventureParkFiles2014_1', FILENAME = N'D:\DATA\AdventurePark_14.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
( NAME = N'AdventureParkFiles2014_2', FILENAME = N'D:\DATA\AdventurePark_15.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON 
( NAME = N'AdventurePark_log', FILENAME = N'L:\LOGS\AdventurePark_16.ldf' , SIZE = 4896KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [AdventurePark] SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [AdventurePark].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [AdventurePark] SET ANSI_NULL_DEFAULT OFF 
GO

ALTER DATABASE [AdventurePark] SET ANSI_NULLS OFF 
GO

ALTER DATABASE [AdventurePark] SET ANSI_PADDING OFF 
GO

ALTER DATABASE [AdventurePark] SET ANSI_WARNINGS OFF 
GO

ALTER DATABASE [AdventurePark] SET ARITHABORT OFF 
GO

ALTER DATABASE [AdventurePark] SET AUTO_CLOSE OFF 
GO

ALTER DATABASE [AdventurePark] SET AUTO_CREATE_STATISTICS ON 
GO

ALTER DATABASE [AdventurePark] SET AUTO_SHRINK OFF 
GO

ALTER DATABASE [AdventurePark] SET AUTO_UPDATE_STATISTICS ON 
GO

ALTER DATABASE [AdventurePark] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO

ALTER DATABASE [AdventurePark] SET CURSOR_DEFAULT  GLOBAL 
GO

ALTER DATABASE [AdventurePark] SET CONCAT_NULL_YIELDS_NULL OFF 
GO

ALTER DATABASE [AdventurePark] SET NUMERIC_ROUNDABORT OFF 
GO

ALTER DATABASE [AdventurePark] SET QUOTED_IDENTIFIER OFF 
GO

ALTER DATABASE [AdventurePark] SET RECURSIVE_TRIGGERS OFF 
GO

ALTER DATABASE [AdventurePark] SET  DISABLE_BROKER 
GO

ALTER DATABASE [AdventurePark] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO

ALTER DATABASE [AdventurePark] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO

ALTER DATABASE [AdventurePark] SET TRUSTWORTHY OFF 
GO

ALTER DATABASE [AdventurePark] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO

ALTER DATABASE [AdventurePark] SET PARAMETERIZATION SIMPLE 
GO

ALTER DATABASE [AdventurePark] SET READ_COMMITTED_SNAPSHOT OFF 
GO

ALTER DATABASE [AdventurePark] SET HONOR_BROKER_PRIORITY OFF 
GO

ALTER DATABASE [AdventurePark] SET  READ_WRITE 
GO

ALTER DATABASE [AdventurePark] SET RECOVERY FULL 
GO

ALTER DATABASE [AdventurePark] SET  MULTI_USER 
GO

ALTER DATABASE [AdventurePark] SET PAGE_VERIFY CHECKSUM  
GO

ALTER DATABASE [AdventurePark] SET DB_CHAINING OFF 
GO

 

Need a quick restore of a database with the data spread across multiple disks onto a new server, here's a simple script I wrote. Just replace name and location and you're ready to go.

USE master
GO

--ALTER DATABASE AdventurePark
--SET SINGLE_USER
----This rolls back all uncommitted transactions in the db.
--WITH ROLLBACK IMMEDIATE
--GO

RESTORE DATABASE [AdventurePark] FROM  DISK = N'\\Teletraan\sqlbackup$\AdventurePark\FULL_BU20150101_000001.bak' WITH  FILE = 1,  
MOVE N'AdventureParkPrimary'     TO N'F:\SQL AdventurePark\AdventurePark.mdf',  
MOVE N'AdventureParkData'        TO N'D:\ARCHIVEDATA\AdventurePark_1.ndf',  
MOVE N'AdventureParkFiles2008_1' TO N'D:\ARCHIVEDATA\AdventurePark_2.ndf',  
MOVE N'AdventureParkFiles2008_2' TO N'D:\ARCHIVEDATA\AdventurePark_3.ndf',  
MOVE N'AdventureParkFiles2009_1' TO N'D:\ARCHIVEDATA\AdventurePark_4.ndf',  
MOVE N'AdventureParkFiles2009_2' TO N'D:\ARCHIVEDATA\AdventurePark_5.ndf',  
MOVE N'AdventureParkFiles2010_1' TO N'D:\ARCHIVEDATA\AdventurePark_6.ndf',  
MOVE N'AdventureParkFiles2010_2' TO N'D:\ARCHIVEDATA\AdventurePark_7.ndf',  
MOVE N'AdventureParkFiles2011_1' TO N'D:\ARCHIVEDATA\AdventurePark_8.ndf',  
MOVE N'AdventureParkFiles2011_2' TO N'E:\ARCHIVEDATA\AdventurePark_9.ndf',  
MOVE N'AdventureParkFiles2012_1' TO N'E:\ARCHIVEDATA\AdventurePark_10.ndf',  
MOVE N'AdventureParkFiles2012_2' TO N'E:\ARCHIVEDATA\AdventurePark_11.ndf',  
MOVE N'AdventureParkFiles2013_1' TO N'E:\ARCHIVEDATA\AdventurePark_12.ndf',  
MOVE N'AdventureParkFiles2014_1' TO N'D:\SQL DATA\AdventurePark_13.ndf',  
MOVE N'AdventureParkFiles2014_2' TO N'D:\SQL DATA\AdventurePark_14.ndf',  
MOVE N'AdventurePark_log' TO N'F:\SQL LOGS\AdventurePark_15.ldf',  
NOUNLOAD,  REPLACE,  STATS = 10
GO

Categories

Social Profile

TheButtonFactory 2009, rights reserved.

Terms of Use - Privacy Policy

WordPress Di Blog Theme