Feeds:
Posts
Comments

Archive for the ‘Sql Server 2008 R2’ Category

Quick one today:

Occasionally, the need to perform DIFFERENTIAL backup arises. The underlying mechanism of Differential backup is one of the coolest things.

 

--
--  Syntax for DIFF backup
--
BACKUP DATABASE SampleDB
TO DISK = 'I:\MSSQL\Backup\DIFF\SampleDB.DIFF'
WITH      DIFFERENTIAL
	, COMPRESSION
	, STATS = 1
GO

 

DIFF_Backup_Syntax

 

Some highlights:

  • Differential backup only captures the data that has changed since the last FULL backup (called base of the differential).
    • Even when we perform multiple DIFF backups, it captures all the data that changed since in the last FULL backup (unlike Transactional log backups, where they only take the changes since the last Transactional backup)
  • The size of DIFF backup files is usually smaller than FULL backups;
    • Obviously, since we are only capturing the changes since last FULL backup.
  • Using Differential Bitmap page, Sql Server maintains a list of extents that were modified since last FULL backup.
    • Using this, Sql is able to quickly run through all the extents that need to be backed-up quickly.
    • See the image from MSDN for clarity
  • bnr-how-diff-backups-work
Hope this helps,
_Sqltimes

Read Full Post »

Interesting one today:

Earlier this month, during Sql Server Cluster set up on a new set of VMs, ran into this interesting warning message.

Network binding Order generated a warning.

The domain network is not the first bound network. This will cause domain operations to run slowly and can cause timeouts that result in failures. Use the Windows network advanced configurations to change the binding order.
Cluster Setup Network Binding Order

Cluster Setup Network Binding Order

Upon further investigation, it became clear that the NIC that connects to the Domain network is not given highest priority (as needed) for Sql Cluster.

Resolution

In Clustered environments, it is recommended to have the network interfaces properly ordered for maximum efficiency.

Go to “Network connections” and open Advanced Settings. See the image below:

Network Connection - Advanced Settings

Network Connection – Advanced Settings

In the resultant window, under Adapters and Bindings tab, make sure the network interfaces are ordered according to the recommendation. Domain network needs to be on the top, then Heartbeat Network and Remote Access Connections. See the image below, for the recommended order.

Network Binding Proper Order

Network Binding Proper Order

After saving the new order, go back to “Install Failover Cluster Rules” and re-run the checks. This blog has more info, if interested about the rest of cluster set up.

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

Every now and then, when good Sql Server resources are available, they are posted here for more people to benefit from them. Continuing on that tradition, today we have a gold mine. Microsoft has released many, many & many e-books open to public to download for free. In the list there are several Sql Server books along with BI, Windows, Office, SharePoint, etc

Happy learning !!

 

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

Recently, ran into this error, about an error with implicit conversion.

Msg 402, Level 16, State 1, Line 45
The data types nvarchar and varbinary are incompatible in the add operator.

This is the query:

--
-- Generate script to CREATE/migrate Sql logins
--
SELECT
		  'CREATE LOGIN [' + L.name
		+ '] WITH PASSWORD = ' + L.password_hash + ' HASHED, sid = ' + 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

On the surface it seems fairly easy to fix, right? Just find the VARBINARY literal in the query and make the conversion EXPLICIT using a CONVERT() function.

In this case, it is ‘L.password_hash‘ & ‘L.sid‘.

As indicated in BoL, password_hash is a VARBINARY(256) dataype and sid is a VARBINARY(85). To concatenate VARBINARY values (represented in hex), with the rest of the query, written in VARCHAR, we need to use an an undocument function called: fn_varbintohexstr. 

Here our goal is to keep the hex representation intact (visual); and just convert the binary to VARCHAR, so they could be concatenated with the rest of the SQL script (written in VARCHAR). We do not want to convert the contents into VARCHAR, just the format to VARCHAR.

--
-- 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

With this function, we are able to convert the contents of VARBINARY variable/column (represented in hexadecimal) to VARCHAR (again represented in hex), so we could generate a proper SQL script.

 

Hope this helps,
_Sqltimes

Read Full Post »

Problem

While gathering replication backlog details, ran into this interesting error. The goal was to run the sp_replmonitorsubscriptionpendingcmds stored procedure and store the output in a table. As we’ve seen in a recent post, redirecting output of a stored procedure execution into a table is possible; But in this case, it throws an error saying that is not allowed.

INSERT INTO #DC1_Repl_Backlog
EXEC  sp_replmonitorsubscriptionpendingcmds
		  @publisher	= 'InstanceName'
		, @publisher_db	= 'DBName'
		, @publication	= 'Publication'
		, @subscriber	= 'Subscriber'
		, @subscriber_db= 'DBName2'
		, @subscription_type = '0'
GO
Msg 8164, Level 16, State 1, Procedure sp_replmonitorsubscriptionpendingcmds, Line 233
An INSERT EXEC statement cannot be nested.

(0 row(s) affected)

With the available information, right now, a clear & coherent explanation fo this behavior is not available from my end. But my guess is this; The code inside this stored procedure must be using a similar INSERT INTO #table EXEC sp_xyz, hence the error “INSERT EXEC statement cannot be nested

Resolution

OPENROWSET helps in getting around this. See the sample code below:

--
--
--
IF OBJECT_ID('tempdb..#DC1_Repl_Backlog') IS NOT NULL
	DROP TABLE #DC1_Repl_Backlog

CREATE TABLE #DC1_Repl_Backlog (
	  pendingcmdcount	BIGINT
	, estimatedprocesstime	BIGINT
)

INSERT #DC1_Repl_Backlog (pendingcmdcount, estimatedprocesstime)
SELECT *
FROM OPENROWSET('SQLOLEDB',
		'Server=InstanceName;Trusted_Connection=yes;',
		'EXEC Ditribution.dbo.sp_replmonitorsubscriptionpendingcmds
					  @publisher = ''PublisherInstance''
					, @publisher_db	= ''DBName''
					, @publication = ''Publication''
					, @subscriber = ''Subscriber''
					, @subscriber_db = ''DBName2''
					, @subscription_type = ''0'''
		) 

SELECT *
FROM #DC1_Repl_Backlog
GO
Hope this helps,
_Sqltimes

Read Full Post »

Older Posts »