How to Migrate logins between Sql Server instances

Quick one today:

Surprisingly, there is a frequent need to migrate logins from one lab machine to another. The script below helps migrate logins from Sql Server instance to another (without having to know the password), with all its settings.

Also, when we RESTORE a database from one instance to another, we need to create missing logins & perform necessary login/user mapping.

Sql Logins:

--
-- Generate script to CREATE/migrate Sql logins
--
SELECT
		  'CREATE LOGIN [' + L.name
		+ '] WITH PASSWORD = ' + master.sys.fn_varbintohexstr(L.password_hash) + ' HASHED, sid = ' + master.sys.fn_varbintohexstr(L.sid)
		+ ', CHECK_EXPIRATION = '
		+ CASE WHEN L.is_expiration_checked = 0 THEN 'OFF, ' ELSE 'ON, ' END
		+ 'CHECK_POLICY = '
		+ CASE WHEN L.is_policy_checked = 0 THEN ' OFF, ' ELSE 'ON, ' END
		+ 'DEFAULT_DATABASE = [' + L.default_database_name + '], '
		+ 'DEFAULT_LANGUAGE = [' + L.default_language_name + ']'
FROM sys.sql_logins AS L
WHERE type = 'S' -- SqlLogin
ORDER BY L.name ASC
GO

Windows Logins:

Also, when we need to migrate Windows/Domain based Logins (windows authentication), use the following script:

--
-- Generate script to CREATE/migrate Windows/Domain logins & Groups
--
SELECT
		  'CREATE LOGIN [' + P.name
		+ '] FROM WINDOWS'
		+ ' WITH DEFAULT_DATABASE = [' + P.default_database_name + '], '
		+ 'DEFAULT_LANGUAGE = [' + P.default_language_name + ']'
FROM sys.server_principals AS P
WHERE P.type IN ('U', 'G') -- WindowsLogin & WindowsGroup
ORDER BY P.name ASC
GO

On second though, both the above scripts could be combined into one script. For now, let’s keep them separate.

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server : What are the double pound signs (##) next to logins?

Quick one today:

In Sql Server, under logins, sometimes we see these funny looking logins with pound signs or hash signs (##) on both sides. See the image below:

In Sql Server, when we create logins, we could create Sql Authentication based logins or Windows/Domain based logins; along with this we could also create Certificate based logins. Such Certificate based logins that are internal to Sql Server have these pound ## signs on both sides. Ex: The installation process will create users like:

• ##MS_PolicyEventProcessingLogin##
• ##MS_PolicyTsqlExecutionLogin##
• ##MS_AgentSigningCertificate##
• etc

Hope this helps,
_Sqltimes

Read Full Post »

Relationship between TARGET_RECOVERY_TIME & Recovery Interval

In a recent post, we saw details on one of the advanced setting starting Sql Server 2012, called TARGET_RECOVERY_TIME. When set correctly, this interacts with the server level setting called ‘recovery interval‘.

One is a server-level setting (recovery interval) and the other is a database level setting. So we need to understand the interaction or dynamic between these two settings and how they interact when set on the same database.

By default, recovery interval is set to 0 (1 minute) — which kicks the CHECKPOINT every one minute to flush dirty pages to disk.

Note:1 minute is just a general guideline, the actual interval depends on the amount of traffic on the database system. For higher traffic systems, there will be a lot of transactions each second, so there will be more dirty pages. So, the CHECKPOINT (background writer) kicks off more frequently than once a minute.

By default, both TARGET_RECOVERY_TIME & Recovery Interval is set to 0. so CHECKPOINTs occur approximately every 1 minute.

Recovery Interval could be set to 2 or 3 (minutes) or some other higher number to allow longer recovery times after a crash. So, Sql Server waits a longer period before flushing dirty pages to disk —  which results in longer times for recovery after crash; As it needs to roll-forward & roll-back transactions.

--
-- Set recovery interval on Sql Server 2012
--
SELECT *
FROM sys.configurations
WHERE name = 'recovery interval (min)'
GO

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sp_configure 'recovery interval', 2
GO

EXEC sp_configure 'show advanced options', 0
RECONFIGURE
GO

TARGET_RECOVERY_TIME could be set in seconds or minutes. This determines the duration allowed for recovery time after a crash; This setting overwrites the system-level setting (recovery interval).

--
--  Change TARGET_RECOVERY_TIME
--
ALTER DATABASE SampleDB
SET TARGET_RECOVERY_TIME = 30 SECONDS;
GO

Relationship between the two settings:

• When TARGET_RECOVERY_TIME is set, it overrides recovery interval setting.
• Similarly, when recovery interval setting is configured and TARGET_RECOVERY_TIME is set to 0, then automatic checkpoint (recovery interval) is used.

TARGET_RECOVERY_TIME	recovery interval	Checkpoint Used
0	0	Automatic CHECKPOINT is used, where target recovery interval is 1 minute
0	>0	Automatic CHECKPOINT us used. The setting comes from (>0) number.
>0	N/A	Indirect checkpoint. Setting is based on TARGET_RECOVERY_TIME

Hope this helps,
_Sqltimes

Read Full Post »

SSRS: History of report execution and email (subscribe)

Quick one today:

On SSRS, during one of the troubleshooting efforts, some ReportServer tables came up to surface that seemed interesting. Here is the question:

Is there a place where history of each SSRS report execution is stored? When the SSRS report is emailed or just generated using API or web browser?

Obviously, the answer is yes. Below is the query that could look it up:

--
-- History of a report execution on SSRS
--
SELECT C.name
    , CASE(E.RequestType)
		WHEN 0 THEN 'Interactive'
		WHEN 1 THEN 'Subscription'
		WHEN 2 THEN 'Refresh Cache'
		ELSE 'Unknown'
	  END AS RequestType
	, E.InstanceName
	, E.ReportID
	, E.UserName
	, E.[Format]
	, E.TimeStart
	, E.TimeEnd
	, E.TimeDataRetrieval
	, E.TimeProcessing
	, E.TimeRendering
	, E.[Source]
	, E.[Status]
	, E.ByteCount
	, E.[RowCount]

FROM ExecutionLog AS E
INNER JOIN dbo.catalog AS C
	ON E.reportid = C.itemid

WHERE c.name = N'Replication Latency Common'
ORDER BY E.TimeEnd DESC
GO

Result would look something like this:

SSRS Report Execution History

Most of the columns are self-explanatory; But for source column, details are added below.

Source

1: Live
2: Cache
3: Snapshot
4: History
5: Ad Hoc (Report Builder, etc)
6: Session (some session calling this session, etc)
7: RDCE – Report Definition Customization Extension

;nbsp;

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server Advanced Options: TARGET_RECOVERY_TIME & Checkpoint

Interesting one today:

A few months ago, we saw a point on CHECKPOINT and its counterparts. Now, lets dig a little deeper into it.

Starting Sql Server 2012, we have a new advanced option called TARGET_RECOVERY_TIME; It helps with setting Indirect Checkpoints to change the recovery time after a crash.

Automatic CheckPoints are the default (system level setting in sys.configurations) settings that decide how frequently the dirty pages in buffer pool are written to disk. Usually it is once every minute (generalization), but there are some nunaces to it (see NOTE below).   This helps in reducing the amount of time it takes to bring the system back to working after a crash.

Note:1 minute is just a general guideline, the actual interval depends on the amount of traffic on the database system. For higher traffic systems, there will be a lot of transactions each second, so there will be more dirty pages. So, the CHECKPOINT (background writer) kicks off more frequently than once a minute.

With new Indirect Checkpoints, a database level setting, we could configure a custom checkpoint settings to enable faster & predictable recovery times after crash.

Context:

When we UPDATE/INSERT data into Sql Server, it is written to buffer pool, not disk. Only when (automatic/default) CHECKPOINT occurs, all the dirty pages in buffer pool are written to disk. This occurs at one minute intervals (varies based on workload, but 1 min in a good general guideline). So, approximately every minute, at the 60th second, you’ll see a HUGE spike in I/O to the MDF/NDF files as all the dirty pages are being written to disk. Then it waits for another ~60 seconds for the next CHECKPOINT, to write all the dirty pages to disk again. So, you see a pattern here.

The entire dirty page workload is being written to disk in one shot —  then wait (sit idle) for the next 60 seconds; And then again write the next workload to disk in one shot. As you can see, the I/O subsystem will be more active during these CHECKPOINT periods than at anytime in between.

If your storage is designed to handled, let’s say, 100 MB/sec and you have 1000 MB worth of dirty pages since the last checkpoint (1 min), it might take storage subsystem more than 10 seconds to fully process the workload. This results in unnecessary spikes in I/O metrics.

• Avg. Disk Write Queue Length will spike to abnormal levels
• Disk Writes/sec will spike to the full capacity of the LUN
• Avg. Disk Sec/Transfer will also spike, showing bad performance

See the image below, where it shows the Maximum reading on the amount of dirty pages written to disk.

This presents an incorrect picture that there is something wrong with your storage. While staying idle the remaining 50 (to 45) seconds of the minute.

--
--	Change TARGET_RECOVERY_TIME
--
ALTER DATABASE SampleDB
SET TARGET_RECOVERY_TIME = 30 SECONDS;
GO

Advantages

• I/O bottlenecks:
  • Now, if we could write more frequently, then the same workload could be accomplished without triggering off any false positive metrics (and also reducing recovery time after a crash).
  • In our above example, if the same 1000 MB dirty page workload per minute, could be written 2 times within a minute, we’ll have ~500MB workload every 30 seconds.
  • Now, the same storage metrics will show much better picture.
  • Then we could tweak the design of storage to the requirements of Sql Server dirty page workload.

• Indirect Checkpoints enable you to control recovery time after a crash to fit within your business requirements

Caution:

• Adjustment Period:
  • When we first change this setting, Sql Server makes some changes to the amount of dirty pages in memory. This may result, initially, in sending some pages to disk immediately to achieve the new Recovery time.
  •  During this adjustment period, we’ll see the above disk activity as Sql Server is going through the adjustment period.
  • Once the adjustment period is completed, Sql Server will go back to a predictable schedule in its attempts to send pages to disk.
  • So, do not be alarmed during this adjustment period.
• Preparation:
  • For OLTP workloads, sometimes this setting could result in performance degradation. Looks like the background writer, that writes dirty pages to disk, increases total write workload for server instance.
  • If different databases have different settings, the instance ends up doing more work, which might result in performance degradation.
  • So, this setting needs to be tested in performance environment before enabling it in Production environments.

In the next post, we’ll see the interaction between ‘recovery interval‘ & TARGET_RECOVERY_TIME setting.

Hope this helps,
_Sqltimes

Read Full Post »