SQL RNNR

1 DBA's Professional Blog

Lost that Single-User Connection?

Categories: Corner, News, Professional, SQLServerPedia Syndication, SSC

Tags: SQL Administration, SQL Script, SQL Server

Comments: No Comments

Published on: May 6, 2013

You have changed your database to single_user mode to do a task. As you go about your business, you lose track of which connection was the single_user connection. You start closing connections and mistakenly close the session that was your single_user connection. Now you are unable to start a new single_user session. It would seem that somebody or something has taken your connection.

Today, I am going to discuss some things you may do to get around this problem.

The first thing that may come to mind when you encounter this is “Oh crap!” Well, no need to get too terribly worried (not unless you really hosed something up and you are trying to fix it real quick before the boss notices).

The next thing you may think of trying is how to circumvent the single_user mode. And during that thought process you may be thinking that single_user does not really mean single_user so you might try something like start a DAC session. Well, let’s go through that and see what would happen in a DAC session if your single_user session is stolen.

I am going to skip the part of setting a database into single_user mode because we are presuming that the condition already exists. To start a DAC session, I am going to point you to a previous article I did on the topic – here.

To ensure I am using a DAC session, I am going to issue the following query. This will ensure I am in the right session and that DAC is in use.

Code block

SELECT s.group_id,e.name, CAST(g.name AS NVARCHAR(20)) AS ResourceGroup, s.session_id 
		, s.login_time, CAST(s.HOST_NAME AS NVARCHAR(20)) AS HostName 
		, CAST(s.program_name AS NVARCHAR(20)) AS ProgramName 
		,s.original_login_name 
		,s.is_user_process 
		,s.STATUS 
	FROM sys.dm_exec_sessions s 
		INNER JOIN sys.dm_resource_governor_workload_groups g 
			ON g.group_id = s.group_id 
		INNER JOIN sys.dm_exec_connections ec 
			ON s.session_id = ec.session_id 
		LEFT OUTER JOIN sys.endpoints e  
			ON ec.[endpoint_id]=e.[endpoint_id] 
WHERE s.session_id = @@SPID 
ORDER BY g.NAME; 
GO

In my case, this results in an endpoint with the name “Dedicated Admin Connection” and a spid of 84. Good, I am in the correct session for the rest of this test. Next, I will issue a Use database statement. I have created a test database called ClinicDB. So I will issue the following.

Code block

USE ClinicDB;
GO

I get the following result.

Code block

Msg 924, LEVEL 14, STATE 1, Line 1
DATABASE 'ClinicDB' IS already OPEN and can ONLY have one USER AT a TIME.

So, that blows that idea right out of the water. It shouldn’t really have been a consideration in the first place because single_user really means just that – single_user.

Now What?

Well, what do you think we could do now to circumvent this little problem and get that single_user session back?

That requires a little investigative work. It is time to find out who has taken the single_user session and politely ask them to give it up. To make that task a little easier, we could modify the previous query to find out who has that single_user session (thus limiting how many people we have to ask). I have modified the following query to use sys.sysprocesses so I could limit the results to the ClinicDB. This is a limitation of SQL 2008 R2 and older versions. Getting the database reliably means using sysprocesses. Despite the database_id being available in other related DMVs, it’s just not that easy. One would think you could use sys.dm_exec_requests. But if a request is not active, an entry won’t exist for that session. This problem is fixed in SQL 2012 since the sys.dm_exec_connections DMV now has the database_id field. Enough of that birdwalk and on to the query.

Code block

SELECT s.group_id,e.name, CAST(g.name AS NVARCHAR(20)) AS ResourceGroup, s.session_id ,DB_NAME(r.dbid) AS DBName
		, s.login_time, CAST(s.HOST_NAME AS NVARCHAR(20)) AS HostName
		, CAST(s.program_name AS NVARCHAR(20)) AS ProgramName 
		,s.original_login_name 
		,s.is_user_process 
		,s.STATUS 
	FROM sys.dm_exec_sessions s 
		INNER JOIN sys.dm_resource_governor_workload_groups g 
			ON g.group_id = s.group_id 
		INNER JOIN sys.dm_exec_connections ec 
			ON s.session_id = ec.session_id 
		INNER JOIN sys.sysprocesses r
			ON r.spid = s.session_id
		LEFT OUTER JOIN sys.endpoints e  
			ON ec.[endpoint_id]=e.[endpoint_id]
	WHERE DB_NAME(r.dbid) = 'ClinicDB'
ORDER BY g.NAME; 
GO

I chose not to do an entirely new query to simply demonstrate that it was possible with a very small tweak to what has been already used.

Now that you know (in my case I can see that I have a session open with ID = 80 that is connected to that single_user database), I can walk over to the person (knowing his/her login id and computer name) and politely ask them to disconnect.

In the end, this is really an easy thing to resolve. Sure it may take some people skills – but that doesn’t make the task too terribly difficult. Next time this happens to you, just remember you can run a quick query to find who has sniped that single_user session.

T-SQL Tuesday #040: File and Filegroup Wisdom

by Jason Brimhall

Categories: Corner, News, Professional, SSC, TSQL Tuesday

Tags: FullText, SQL Administration, TSQL Tuesday

Comments: 3 Comments

Published on: March 11, 2013

Backstory

Each month the SQL community comes together for an important party. This is the blog party that was the brain child of Adam Machanic (Twitter) known as T-SQL Tuesday.

The party is a very good collaboration among data professionals on a pre-determined topic. This month, for TSQL Tuesday #40, the topic is on Files and Filegroups. The host du mois is Jen McCown (Twitter).

This month, I had the luck of encountering something this past week that is right up the alley of this topic. I love it when sysadmins help create learning opportunities for me (e.g. blog material).

Production Down

I was recently given the following concerning a client server issue:

The log file for database ‘xxx’ is full. Back up the transaction log for the database to free up some log space.

That was followed by a short description stating that the sysadmin had tried to expand the log file and that they also tried to run a full backup. The output of the full backup was as follows.

BACKUP DATABASE [xxx] To Disk=’blah’ WITH NOFORMAT, NOINIT, NAME = N’blah’, SKIP, REWIND, NOUNLOAD, STATS = 10
” failed with the following error: “The backup of the file or filegroup “sysft_FTS” is not permitted because it is not online.
BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.

Now this makes things more interesting. The sysadmin at least tried to do a full backup and then handed off when it got too deep.

The client server is a SQL 2005 box. Fulltext was enabled for the database on that box. And we have seen plenty of issues related to Fulltext in SQL 2005. Somehow, I feel that none of them really pertained to this opportunity. From all appearances, there was either a disk issue (no history in the logs but client said there was) or somebody deleted the directory (there was a login at the time the issue started and there was a service restart at that time). In either case, the folder for the fulltext filegroup was no longer present. But I am getting a little ahead of myself.

When querying the sys.database_files catalog view, I was able to confirm the directory path that should have been in place for the filegroup and that the filegroup was indeed OFFLINE. Results and query to follow, with filepaths redacted intentionally.

Code block

SELECT FILE_ID,type_desc,name
		,physical_name --intentionally omitted in result set
		,state_desc
	FROM sys.database_files;

file_id	type_desc	name	state_desc
1	ROWS	Somefile	ONLINE
2	LOG	Somefile_log	ONLINE
3	ROWS	Somefile_data	ONLINE
4	ROWS	Somefile_index	ONLINE
65537	FULLTEXT	sysft_FTS	OFFLINE

So, indeed I do have a problem with the filegroup and I need to get it back online in order to resume backups and get this database back online and able to perform backups.

Some suggestions out there would be to rebuild the fulltext catalog in order to bring it back online. Well, the files are no longer present on the filesystem, so this didn’t work too well.

Code block

ALTER FULLTEXT CATALOG [FTS] REBUILD
/*
Full-text catalog 'FTS' is in an unusable state. Drop and re-create this full-text catalog.
*/

The notes in the code block represent the outcome. And the output makes sense if you ask me. But when trying to drop and recreate, I ran into some more fun.

Code block

DROP FULLTEXT CATALOG [FTS]
--or
SP_FULLTEXT_DATABASE @ACTION= 'disable'
 
/*
Cannot drop full-text catalog 'FTS' because it contains a full-text index.
*/

Once again, that makes sense. I had hoped that it would drop everything for me. So, time to try dropping the indexes and recreating them. For this, I took screenshots of each index in question. Then tried to drop them. Once again – another error.

property fulltextindexsize is not available

Despite that error, the indexes were gone and the catalog dropped. Since I had disabled FT on the database, I needed to re-enable it in order to recreate the catalog and indexes (I had scripts for the catalog and screenshots for the indexes).

Code block

SP_FULLTEXT_DATABASE @ACTION= 'enable'

Now issuing a rebuild against that catalog works as expected. Additionally, backups work as expected. And to confirm that all is well, query sys.database_files once again to see that the filegroup is online.

file_id	type_desc	name	state_desc
1	ROWS	Somefile	ONLINE
2	LOG	Somefile_log	ONLINE
3	ROWS	Somefile_data	ONLINE
4	ROWS	Somefile_index	ONLINE
65537	FULLTEXT	sysft_FTS	ONLINE

Between the Lines

I breezed through what got this filegroup back online so database activity could resume. One thing that I skipped over was a step I took trying to recover without dropping and recreating. Since the directory was not present, and there was a full backup from the same day that had the filegroup in a working state, I tried to recover the filegroup manually. Restore the database, copy the folder structure into the appropriate filepath and run an alter database statement. Since it didn’t work, I am not going into deep details on it. The short of it is that since the structure disappeared off disk, there was some corruption related to it internally in the database. That needed fixed and in this case it meant to drop the indexes and catalog in order to recreate it.

Is your LOG backed up?

by Jason Brimhall

Categories: Corner, News, Professional, SQLServerPedia Syndication, SSC

Tags: Database Maintenance, SQL Administration, SQL Backup

Comments: No Comments

Published on: February 11, 2013

You have been doing a fantastic job of late. You have all of your databases being backed up on a regular schedule. Now you get an alert that your transaction log just keeps growing. Why is that?

You decide to do a little investigation and find that you have your recovery model set to full and you are performing full backups. That should cover it right? No, that is not right!

What is this? Now you have to do something more? Yes, that is correct. Performing full backups is not always enough to recover your critical data. You need to know the recovery requirements for the database / server in question. But since you have the recovery model set to full, let’s just talk about what else you should be doing.

The first step should be to run a quick script to determine what databases you have in full recovery and which of those databases do not have a LOG backup. You see, when a database is in full recovery, you should also backup your transaction log on a regular schedule too. That schedule is to be determined as a part of the second step (and I will only talk about the first two and only briefly about the second step).

In that first step, you can query your msdb database to help generate a report of which databases have had a transaction log backup. That should be easy enough to do. Despite the ease, it should not lessen the importance by any degree. Here is the script that I wrote recently to help determine which databases were in need of a log backup.

Code block

SELECT T1.Name AS DatabaseName
		,ISNULL(t2.database_name, 'No Backup Taken') AS LogBackupAvail
		,T1.recovery_model_desc
		,'Log' AS BackupType 
		,ISNULL(CONVERT(VARCHAR(23), CONVERT(DATETIME, MAX(T2.backup_finish_date), 131))
				, CASE WHEN T1.recovery_model_desc = 'Simple'
						THEN 'N/A'
						ELSE 'Backup Not Taken'
						END
		) AS LastBackUpTaken
	FROM sys.databases T1
		LEFT OUTER JOIN msdb.dbo.backupset T2
			ON t1.name = t2.database_name
			AND t2.type = 'L'
	GROUP BY T1.Name,T2.database_name,T1.recovery_model_desc

You will likely notice that I am querying both sys.databases out of the master database as well as dbo.backupset out of msdb. Look more closely and you will see the employ of a Left Outer Join with two conditions on the Join. In this case, both conditions are required to produce the Outer Join effect that I was seeking. Had I used a script like the following:

Code block

SELECT T1.Name AS DatabaseName
		,ISNULL(t2.database_name, 'No Backup Taken') AS LogBackupAvail
		,T1.recovery_model_desc
		,'Log' AS BackupType 
		,ISNULL(CONVERT(VARCHAR(23), CONVERT(DATETIME, MAX(T2.backup_finish_date), 131))
				, CASE WHEN T1.recovery_model_desc = 'Simple'
						THEN 'N/A'
						ELSE 'Backup Not Taken'
						END
		) AS LastBackUpTaken
	FROM sys.databases T1
		LEFT OUTER JOIN msdb.dbo.backupset T2
			ON t1.name = t2.database_name
		WHERE t2.type = 'L'
	GROUP BY T1.Name,T2.database_name,T1.recovery_model_desc

You would see a considerably different result set. The reason for this different result set is tied to the predicate used and the Join conditions. And when one examines the execution plan, the difference becomes a little more evident.

That WHERE predicate converted our Left Outer Join to an Inner Join. Now, if I had only wanted to return results for databases that had log backups, that might be fine. I want to report on all databases and find not only the last log backup for a database, but I also want to find if a log backup is missing. Therefore, I need to ensure that both conditions are declared as part of my Left Outer Join.

Running a query such as this will now provide us with some knowledge as to the database recovery models as well as which databases have had a log backup, have not had a log backup, and which do not need a log backup (based on recovery model).

Armed with this information, it is imperative to do a bit more digging now. Knowing what the business deems as an acceptable data loss is important. Find that out and then create transaction log backups accordingly so the business can recover appropriately in the event of a disaster.

Audit Configuration Changes

by Jason Brimhall

Categories: Corner, News, Professional, Scripts, SQLServerPedia Syndication, SSC

Tags: Audit, Database Maintenance, SQL Administration, SQL Script

Comments: 2 Comments

Published on: January 24, 2013

Do you know the last time a Server Property was changed on your instances of SQL Server?

Are you wondering when the setting for max degree of parallelism was changed?

Do you know who changed the setting?

In some environments there are a lot of hands trying to help mix the pot. Sometimes more hands can make light work. This is not always the case though. More hands in the mix can be a troublesome thing. Especially when things start changing and the finger pointing starts but nobody really knows for sure who made the change or when the change was made.

I know, that is why there is this concept called change management. If you make a change to a setting, it should be documented, approved and communicated. Unfortunately the process does not always dictate the work flow and it may be circumvented. This is why auditing is a tool that is in place and should be in place – like it or not.

Auditing can be a very good tool. Like many things – too much of a good thing is not a good thing. Too much auditing can be more of a hindrance than help. You don’t want to cause interference by auditing too many things. You also don’t want too much data that the facts get blurred. I hope that this script strikes more of a balance with just the right amount of data being captured to be of use.

The basic principle to auditing server configs is to find what values changes, when they were changed and by whom. There are ancillary details that can be helpful in tracking the source of the change such as the hostname of the source computer making the change. These are all things that we should capture. But if a setting hasn’t changed – then we need not necessarily report that the setting was unchanged (it should go without saying).

So for this, I created a couple of tables and then a script that I can add to a job to run on a regular basis. I can put the script in a stored procedure should I desire. I’ll leave that to you as an exercise to perform.

Code block

USE AdminDB;
GO
SET NOCOUNT ON;
 
DECLARE @ConfigLastUpdateDate	DATETIME
		,@PreviousPollDate		DATETIME
		,@MaxPollDate			DATETIME
		,@PATH					NVARCHAR(260);
 
SELECT @PATH = REVERSE(SUBSTRING(REVERSE([PATH]), 
						CHARINDEX('\', REVERSE([path])), 260)) + N'LOG.trc'
	FROM sys.traces 
	WHERE is_default = 1;
 
IF OBJECT_ID('tempdb..#DBCCConfig') IS NOT NULL DROP TABLE #DBCCConfig;
	CREATE TABLE #DBCCConfig(
		[ParentObject] VARCHAR (100),
		[Object]       VARCHAR (100),
		[Field]        VARCHAR (100),
		[Value]        VARCHAR (100)); 
 
INSERT INTO #DBCCConfig (ParentObject, Object, Field, Value)
	EXECUTE ('DBCC CONFIG WITH TABLERESULTS');
 
WITH cte AS
(
SELECT cfgupddate = MAX(CASE WHEN t1.Field = 'cfgupddate' THEN t1.Value ELSE NULL END),
        cfgupdtime = MAX(CASE WHEN t1.Field = 'cfgupdtime' THEN t1.Value ELSE NULL END)
FROM #DBCCConfig t1
WHERE   Field IN ('cfgupddate', 'cfgupdtime')
)
SELECT  @ConfigLastUpdateDate = CONVERT(DATETIME,t3.configure_upd_dt)
FROM    cte t1
        CROSS APPLY (SELECT cfgupddate = DATEADD(DAY, CONVERT(INT, t1.cfgupddate), '1900-01-01')) t2
        CROSS APPLY (SELECT configure_upd_dt = DATEADD(ms, CONVERT(INT, t1.cfgupdtime)*3.3, t2.cfgupddate)) t3;
 
IF NOT EXISTS (SELECT Name 
					FROM sys.objects 
					WHERE name = 'SysConfigAudit')
	CREATE TABLE SysConfigAudit (
		configuration_id	int
		,name	NVARCHAR(256)
		,value	sql_variant
		,minimum	sql_variant
		,maximum	sql_variant
		,value_in_use	sql_variant
		,description	NVARCHAR(MAX)
		,is_dynamic	bit
		,is_advanced	BIT
		,PollDate		DATE
		,LastConfigUpdtDate	DATETIME)
 
IF NOT EXISTS (SELECT Name 
					FROM sys.objects 
					WHERE name = 'SysConfigChangeLog')
	CREATE TABLE SysConfigChangeLog (
		configuration_id	int
		,name	NVARCHAR(256)
		,CurrValue	SQL_VARIANT
		,PrevValue	SQL_VARIANT
		,description	NVARCHAR(MAX)
		,PollDate		DATE
		,LastConfigUpdtDate	DATETIME
		,PrevConfigUpdtDate	DATETIME
		,ChangeDate	DATETIME
		,ChangeBy	NVARCHAR(256)
		,HostName	NVARCHAR(256)
		,ChangeSPID	SQL_VARIANT
		,Changedata	NVARCHAR(2000)
		,ApplicationName NVARCHAR(256)
		,Severity INT
		,ERROR SQL_VARIANT
		,ChangeBySessionLogin	NVARCHAR(256))
IF NOT EXISTS (SELECT Name 
					FROM sys.key_constraints 
					WHERE name ='PK_SysConfigChangeLog' 
						AND OBJECT_NAME(parent_object_id) = 'SysConfigChangeLog')
BEGIN
ALTER TABLE dbo.SysConfigChangeLog ADD CONSTRAINT
	PK_SysConfigChangeLog PRIMARY KEY CLUSTERED 
	(
	configuration_id,
	ChangeDate DESC
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
		ON Admin_Data
 
END
 
SET @MaxPollDate = ISNULL((SELECT TOP 1 PollDate
								FROM dbo.SysConfigAudit 
								ORDER BY PollDate DESC),'1/1/1900')
 
/* Write the configurations out to audit table 
*/
IF CONVERT(DATE,GETDATE()) <> @MaxPollDate
BEGIN
	INSERT INTO SysConfigAudit
			( configuration_id ,
			  name ,
			  value ,
			  minimum ,
			  maximum ,
			  value_in_use ,
			  description ,
			  is_dynamic ,
			  is_advanced,
			  PollDate,
			  LastConfigUpdtDate
			)
	SELECT configuration_id,name
			,value
			,minimum,maximum
			,value_in_use
			,description
			,is_dynamic,is_advanced
			,GETDATE(),@ConfigLastUpdateDate
		FROM master.sys.configurations;
END
 
/* Recast MaxPollDate */
SET @MaxPollDate = ISNULL((SELECT TOP 1 PollDate
								FROM dbo.SysConfigAudit 
								ORDER BY PollDate DESC),'1/1/1900')
 
SET @PreviousPollDate = ISNULL((SELECT TOP 1 PollDate 
									FROM dbo.SysConfigAudit 
									WHERE Polldate <> @MaxPollDate 
									ORDER BY PollDate DESC),'1/1/1900');
 
/*	A configuration has changed and a reboot has occurred 
	causing the updtdate to be written to the config block of the page 10
	The configuration may not be written to the page, but we will write it to
	the table anyway and then compare to the default trace file in all cases.
	If there are any values changed, then write those to the change log with
	the login of the person who changed the value
*/
 
BEGIN
 
WITH presel AS (
SELECT df.LoginName,df.TextData,df.StartTime,df.HostName,df.Severity,df.DatabaseName,df.SPID,df.ERROR
		,df.SessionLoginName,df.ApplicationName
		,SUBSTRING(df.TextData
					,CHARINDEX('''',df.TextData)+1
					,CHARINDEX('''',df.TextData
								,CHARINDEX('''',df.TextData)+1)-CHARINDEX('''',df.TextData)-1)
				AS ConfigName
		,ROW_NUMBER() OVER (PARTITION BY SUBSTRING(df.TextData
													,CHARINDEX('''',df.TextData)+1
													,CHARINDEX('''',df.TextData
															,CHARINDEX('''',df.TextData)+1)-CHARINDEX('''',df.TextData)-1) 
								ORDER BY df.StartTime DESC) AS RowNum
	FROM ::fn_trace_gettable( @path, DEFAULT )  df
	WHERE 1=1
		AND df.TextData LIKE '%Configuration option%'
		AND df.ApplicationName NOT IN ('SQLAgent - Initial Boot Probe','SQLAgent - Enabling/disabling Agent XPs')
		AND df.Severity IS NOT NULL
)
 
INSERT INTO SysConfigChangeLog (configuration_id,name,CurrValue,PrevValue,description,PollDate	
							,LastConfigUpdtDate	,PrevConfigUpdtDate	,ChangeDate,ChangeBy
							,HostName,ChangeSPID,Changedata,ApplicationName,Severity
							,ERROR,ChangeBySessionLogin)
SELECT CUR.configuration_id,cur.NAME,cur.VALUE AS CurrValue,Prev.VALUE AS PrevValue,cur.description
		,cur.polldate, cur.LastConfigUpdtDate,prev.LastConfigUpdtDate AS PrevConfigUpdtDate
		,df.StartTime AS ChangeDate,df.LoginName AS ChangeBy,df.HostName,df.SPID AS ChangeSPID
		,df.TextData AS ChangeData,df.ApplicationName,df.Severity,df.ERROR
		,df.SessionLoginName AS ChangeBySessionLogin
	FROM AdminDB.dbo.SysConfigAudit CUR
		INNER JOIN AdminDB.dbo.SysConfigAudit Prev
			ON CUR.configuration_id = Prev.configuration_id
			AND CUR.PollDate = @MaxPollDate
			AND Prev.PollDate = @PreviousPollDate
		CROSS APPLY presel  df
	WHERE df.RowNum = 1
		AND df.ConfigName = CUR.NAME
		AND CUR.VALUE <> Prev.VALUE	
		AND NOT EXISTS (SELECT configuration_id
							FROM SysConfigChangeLog
							WHERE Name = Cur.NAME
								AND CurrValue = CUR.VALUE
								AND PrevValue = Prev.VALUE
								AND ChangeDate = df.StartTime
								AND ChangeBy = df.LoginName)
		;
END
 
SELECT *
	FROM dbo.SysConfigChangeLog
	ORDER BY ChangeDate DESC;

Here I am trapping the config settings on a daily basis (as the script is written for now). I then cross reference the current settings against the previous settings. Then I check the default trace to see if any of those settings exist in the trace file.

The default trace captures the data related to configuration changes. On busy systems, it is still possible for these settings to roll out of the trace files. For those systems, we may need to make some alterations to the script (such as running more frequently and changing the comparisons to account for smaller time intervals than a day break).

To ensure proper comparison between the audit tables and the trace file, note the substring function employed. I can capture the configuration name and then join to the audit tables on configuration name.

This has proven useful to me so far in tracking who did what to which setting and when they did it.

I hope you will find good use for it as well.

Last Time CheckDB was Run

by Jason Brimhall

Categories: Corner, News, Professional, SSC

Tags: CheckDB, SQL Administration, TSQL

Comments: 8 Comments

Published on: January 17, 2013

Back in November 2012, I shared a story about checking the last known good checkdb in the boot page of a database. You can read that story here.

This is an important enough topic that it is worth repeating frequently if I wanted to do that. If for no other reason than to continue to hammer at how important it is to both run checkdb and know the last time that checkdb was run successfully.

Alas, I am writing to fix a few things with the script that I shared in that last past.

I run this script on every server I touch to get a report for the last known good checkdb for every single database. I had been running the script flawlessly across many servers without error. Then it happened. The script failed with a nasty error.

After a bit of looking, it became apparent my flaw in the script. I had not written the script with CS (case sensitivity) in mind. I touch so few CS servers, that I sometimes forget to check for that. Slap my hands and I will work on that going forward.

So here is the update to the script.

Code block

CREATE TABLE #temp (
       Id INT IDENTITY(1,1), 
       ParentObject VARCHAR(255),
       [OBJECT] VARCHAR(255),
       Field VARCHAR(255),
       [VALUE] VARCHAR(255)
)
 
CREATE TABLE #DBCCRes (
       Id INT IDENTITY(1,1)PRIMARY KEY CLUSTERED, 
       DBName sysname ,
       dbccLastKnownGood DATETIME,
       RowNum	INT
)
 
DECLARE
	@DBName SYSNAME,
	@SQL    VARCHAR(512);
 
DECLARE dbccpage CURSOR
	LOCAL STATIC FORWARD_ONLY READ_ONLY
	FOR SELECT name
		FROM sys.databases
	WHERE 1 = 1
		AND STATE = 0
		--And name NOT IN ('tempdb')
	;
 
OPEN dbccpage;
FETCH NEXT FROM dbccpage INTO @DBName;
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @SQL = 'Use [' + @DBName +'];' + CHAR(10)+ CHAR(13)
	SET @SQL = @SQL + 'DBCC Page ( ['+ @DBName +'],1,9,3) WITH TABLERESULTS;' + CHAR(10)+ CHAR(13)
 
	INSERT INTO #temp
		EXECUTE (@SQL);
	SET @SQL = ''
 
	INSERT INTO #DBCCRes
			( DBName, dbccLastKnownGood,RowNum )
		SELECT @DBName, VALUE
				, ROW_NUMBER() OVER (PARTITION BY Field ORDER BY VALUE) AS Rownum
			FROM #temp
			WHERE Field = 'dbi_dbccLastKnownGood';
 
	TRUNCATE TABLE #temp;
 
	FETCH NEXT FROM dbccpage INTO @DBName;
END
CLOSE dbccpage;
DEALLOCATE dbccpage;
 
SELECT DBName,dbccLastKnownGood
	FROM #DBCCRes
	WHERE RowNum = 1;
 
DROP TABLE #temp
DROP TABLE #DBCCRes

A Firm Foundation

by Jason Brimhall

Categories: Corner, News, Professional, SQLServerPedia Syndication, SSC, TSQL Tuesday

Tags: Database Maintenance, SQL Administration, TSQL Tuesday

Comments: 4 Comments

Published on: January 7, 2013

Last week I sent out an invite for the monthly TSQL Tuesday party.

The theme for the party is a take on the words resolve or resolution. I was hoping the theme would encourage some reflection and sharing of real life experiences that have led to a difference being made.

I have resolved on two stories to share. Both are rather short and simple in nature.

This arch (in Arches National Park, Ut.) has stood RESOLUTE for milennia

Story the First

Near the end of the year in 2012, I inherited a database that had not had a consistency check done on it – ever! In checking the page_verify setting, I found that it was set to none as well. Both of these should be alarming to any DBA – unless you are completely unconcerned by corrupt data and the potential for corrupt data. Never-mind the potential business repercussions of having corrupt or lost data.

To find what level of page verification you have enabled, it is a matter of a quick script like the following.

Code block

SELECT name, page_verify_option_desc
	FROM sys.databases;

You can have any one of three settings for your page_verify. The recommended option is to have CHECKSUM enabled. If you see NONE or TORN_PAGE_DETECTION, you really need to consider changing that. Keep in mind if you are still running SQL 2000, CHECKSUM is not an option and the query provided will fail.

Changing the verify option is very simple as well. It only requires an Alter Database to be run such as the following.

Code block

ALTER DATABASE [msdb]
	SET PAGE_VERIFY CHECKSUM;

You will probably notice that I am using the msdb in my sample script. There is a reason for this that will be shown later. Just keep in mind that msdb should not need to be changed because it should already be using the CHECKSUM option.

What if you have numerous databases that are not using the CHECKSUM method? It can become rather tedious to change each of those manually. That is why we might come up with a cursor such as the following.

Code block

DECLARE
	@DBName SYSNAME,
	@SQL    VARCHAR(512);
 
DECLARE dbchecksum CURSOR
	LOCAL STATIC FORWARD_ONLY READ_ONLY
	FOR SELECT name
		FROM sys.databases
		WHERE name not in ('tempdb')
			AND state_desc = 'online'
			AND page_verify_option_desc <> 'Checksum';
 
OPEN dbchecksum;
FETCH NEXT FROM dbchecksum INTO @DBName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'ALTER DATABASE [' + @DBName +'];' +CHAR(10)+CHAR(13)
SET @SQL = @SQL + 'SET PAGE_VERIFY CHECKSUM;' +CHAR(10)+CHAR(13)
 
EXECUTE (@SQL);
SET @SQL = ''
 
FETCH NEXT FROM dbchecksum INTO @DBName;
END
CLOSE dbchecksum;
DEALLOCATE dbchecksum;

This script is only checking for databases that are not using CHECKSUM. Then it loops through and changes the setting to use CHECKSUM.

I strongly caution about running this in production without an outage window! I make that recommendation for very simple reasons. First, the change is to a production system. Second, the change can have a temporary adverse effect. Now before you get too excited about it, I have a short demonstration.

Here is a script broken out into three sections.

Code block

SELECT TOP(10) [type] AS [Memory Clerk Type], SUM(single_pages_kb) AS [SPA Mem, Kb] 
FROM sys.dm_os_memory_clerks WITH (NOLOCK)
GROUP BY [type]  
ORDER BY SUM(single_pages_kb) DESC OPTION (RECOMPILE);
SELECT DB_NAME(database_id) AS [DATABASE Name],
COUNT(*) * 8/1024.0 AS [Cached SIZE (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id <> 32767 -- ResourceDB
--AND database_id > 4 -- system databases
GROUP BY DB_NAME(database_id)
ORDER BY [Cached SIZE (MB)] DESC OPTION (RECOMPILE);
 
SELECT DB_NAME(dbid) AS DbName,dbid,SUM(size_in_bytes)/1024/1024 AS TotalPlanCacheSize_in_MB
FROM sys.dm_exec_cached_plans cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) st
GROUP BY dbid

Code block

ALTER DATABASE [msdb]
		SET PAGE_VERIFY CHECKSUM;

Code block

SELECT TOP(10) [type] AS [Memory Clerk Type], SUM(single_pages_kb) AS [SPA Mem, Kb] 
FROM sys.dm_os_memory_clerks WITH (NOLOCK)
GROUP BY [type]  
ORDER BY SUM(single_pages_kb) DESC OPTION (RECOMPILE);
SELECT DB_NAME(database_id) AS [DATABASE Name],
COUNT(*) * 8/1024.0 AS [Cached SIZE (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id <> 32767 -- ResourceDB
--AND database_id > 4 -- system databases
GROUP BY DB_NAME(database_id)
ORDER BY [Cached SIZE (MB)] DESC OPTION (RECOMPILE);
 
SELECT DB_NAME(dbid) AS DbName,dbid,SUM(size_in_bytes)/1024/1024 AS TotalPlanCacheSize_in_MB
FROM sys.dm_exec_cached_plans cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) st
GROUP BY dbid

Sections one and three are the same. This script is used to measure various memory components within SQL Server. The second section is the change we will make to the msdb database. The queries in the first and third section perform the following: retrieve memory clerk usage (aggregated to memory clerk type), retrieve total data pages stored in cache (aggregated by database), and retrieve the plan cache use (aggregated by database).

Now on to some pre and post change results. First with what my results were prior to the change.

Memory Clerk Usage
Memory Clerk Type	SPA Mem, Kb
CACHESTORE_SQLCP	156184
CACHESTORE_PHDR	45904
CACHESTORE_OBJCP	20664
USERSTORE_DBMETADATA	8472
USERSTORE_SCHEMAMGR	6376

Pages in Cache
Database Name	Cached Size (MB)
msdb	12.265625

Plan Cache
DbName	dbid	TotalPlanCacheSize_in_MB
NULL	32767	42
NULL	NULL	150
msdb	4	13
ReportServer$ADMIN	5	0
MDW	28	8
AdminDB	14	0

And the following are the post change results.

Memory Clerk Usage
Memory Clerk Type	SPA Mem, Kb
CACHESTORE_SQLCP	109160
CACHESTORE_PHDR	36744
CACHESTORE_OBJCP	9152
USERSTORE_DBMETADATA	8472
USERSTORE_SCHEMAMGR	6296

Data Pages in Cache
Database Name	Cached Size (MB)
msdb	12.265625

Plan Cache
DbName	dbid	TotalPlanCacheSize_in_MB
NULL	32767	36
NULL	NULL	104
ReportServer$ADMIN	5	0
MDW	28	8
AdminDB	14	0

First observation I want to point out is with the second result for both the pre and post run. Making this change will not affect the pages in cache. This goes along with what we have been taught by Paul Randal – that a CHECKSUM is not performed immediately (I paraphrased). You can read more about the CHECKSUM and some misconceptions about it here.

If we now turn our attention to the first and third result sets, we will see that there are changes in the memory clerks used and the plan cache. Starting with the the third result set (both pre and post) we see that the ResourceDB decreased in total plan cache size. The NULL item (adhoc queries not associated to a specific database) also decreased. After that, the only change in size is the msdb database – disappeared from the results due to no plan cache in use associated to this database. (Starting to see why I chose the msdb database for this demo?)

If you now look closer at the results for the first query on both sides of the change, you will see correlating changes to the plan cache. Notice that CACHESTORE_SQLCP dropped by about 46MB (correlates to the null entry from query 3). But of those clerks listed, you will see that only USERSTORE_DBMETADATA did not change in size.

Looking at these results should demonstrate why this change should be performed during a maintenance window. There will be an effect on performance and I would rather you let the business know what is coming down the pipe. This change is akin to running DBCC FLUSHPROCINDB(<db_id>);. There are other database settings that will have the same effect. You can read a little about that from Kalen Delaney – here.

Story the Second

This story is far less interesting and a whole lot shorter. This falls into the category of professional development and fine tuning my skills. I took the MCM lab exam during the PASS Summit. I failed, not unlike many who have attempted it. That is all fine and well. I learned some things about myself and I learned some areas that may need some resolution (sharpened focus).

So as more of a resolution upon which I have greater resolve than a New Years resolution, I will be retaking the Lab exam. And I will be getting my MCM in the near future. Just sayin’!

SSRS Subscriptions Report

by Jason Brimhall

Categories: News, Professional, SQLServerPedia Syndication, SSC

Tags: Database Maintenance, SQL Administration, SQL Script, SSRS, SSRS Administration, TSQL

Comments: No Comments

Published on: January 7, 2013

As a part of my series leading up to Christmas 2012, I shared a script to Report on SSRS Subscriptions. It was soon found to have a bug with SQL Server 2008 R2 SP2. IN the comments on that post, I promised to post an updated script. Here is that update – without the bug.

Code block

DECLARE @ReportName VARCHAR(100)
SET @ReportName = NULL;
 
CREATE TABLE #morepower (MonthDate BIGINT,N BIGINT,PowerN BIGINT PRIMARY KEY CLUSTERED
							,NameofMonth VARCHAR(25),WkDay VARCHAR(25))
;
 
WITH powers(powerN, n) AS (
	SELECT POWER(2,number), number 
		FROM master.dbo.spt_values 
		WHERE type = 'P' AND number < 31)
 
INSERT INTO #morepower ( MonthDate ,N,PowerN ,NameofMonth ,WkDay)
	SELECT ROW_NUMBER() OVER (ORDER BY N) AS MonthDate,N,PowerN
			,CASE WHEN N BETWEEN 0 AND 11 
				THEN DATENAME(MONTH,DATEADD(MONTH,N+1,0)-1)
				ELSE NULL
				END AS NameofMonth
			,CASE WHEN N BETWEEN 0 AND 6
				THEN DATENAME(weekday,DATEADD(DAY,n+1,0)-2)
				ELSE NULL
				END AS WkDay
		FROM powers
 
SELECT DISTINCT s.ScheduleID,Ca.PATH AS ReportManagerPath,Ca.Name AS ReportName
		, U.UserName AS SubscriptionCreator
		,Su.Description AS SubscriptionDescription,S.StartDate,Su.LastRunTime
		,CASE 
				WHEN s.RecurrenceType = 1 THEN 'One Off'
				WHEN s.RecurrenceType = 2 THEN 'Hour'
				WHEN s.RecurrenceType = 4 THEN 'Daily'
				WHEN s.RecurrenceType = 5 THEN 'Monthly' 
				WHEN s.RecurrenceType = 6 THEN 'Week of Month' 
			END AS RecurrenceType
		,s.EventType
		,ISNULL(REPLACE(REPLACE(STUFF(
					(SELECT ', ['+CONVERT(VARCHAR(20),MonthDate)+']' AS [TEXT()] 
						FROM #morepower m1 
						WHERE m1.powerN < s.DaysofMonth+1 
							AND s.DaysofMonth & m1.powerN <>0 
						ORDER BY N FOR XML PATH(''), TYPE).VALUE('.','VARCHAR(MAX)')
			   , 1, 2, ''),'[',''),']','')
			,'N/A') AS DaysofMonth
		,ISNULL(c1.NameOfMonth,'N/A') AS MonthString
		,ISNULL(c2.WkDays,'N/A') AS DaysofWeek
		,CASE MonthlyWeek
				WHEN 1 THEN 'First'
				WHEN 2 THEN 'Second'
				WHEN 3 THEN 'Third'
				WHEN 4 THEN 'Fourth'
				WHEN 5 THEN 'Last'
				ELSE 'N/A'
			END AS MonthlyWeek
		,ISNULL(CONVERT(VARCHAR(10),s.DaysInterval),'N/A') AS DaysInterval
		,ISNULL(CONVERT(VARCHAR(10),s.MinutesInterval),'N/A') AS MinutesInterval
		,ISNULL(CONVERT(VARCHAR(10),s.WeeksInterval),'N/A') AS WeeksInterval
	FROM #morepower mp, dbo.Schedule s
		INNER JOIN ReportSchedule RS
			ON S.ScheduleID = RS.ScheduleID
		INNER JOIN CATALOG Ca
			ON Ca.ItemID = RS.ReportID
		INNER JOIN Subscriptions Su
			ON Su.SubscriptionID = RS.SubscriptionID
		INNER JOIN Users U
			ON U.UserID = S.CreatedById
			OR U.UserID = Su.OwnerID
	CROSS APPLY (SELECT s.ScheduleID,REPLACE(REPLACE(STUFF(
							(SELECT ', ['+ NameofMonth + ']' AS [TEXT()] 
								FROM #morepower m1 ,dbo.Schedule s1
								WHERE m1.NameofMonth IS NOT NULL 
									AND m1.powerN & s1.MONTH <>0 
									AND s1.ScheduleID = s.ScheduleID
								ORDER BY N FOR XML PATH(''), TYPE).VALUE('.','VARCHAR(MAX)')
							, 1, 2, ''),'[',''),']','') AS NameOfMonth)c1
	CROSS APPLY (SELECT s.ScheduleID,REPLACE(REPLACE(STUFF(
							(SELECT ', [' + WkDay + ']' AS [TEXT()] 
								FROM #morepower m1 ,dbo.Schedule s2
								WHERE m1.WkDay IS NOT NULL 
									AND DaysOfWeek & m1.powerN <>0
									AND  s2.ScheduleID = s.ScheduleID
								ORDER BY N FOR XML PATH(''), TYPE).VALUE('.','VARCHAR(MAX)')
							, 1, 2, ''),'[',''),']','') AS WkDays) c2
	WHERE Ca.Name = ISNULL(@ReportName,Ca.Name);
 
DROP TABLE #morepower;

The inline code seen above likes to reformat and and will throw an error due to capitalization of the function value and text(). Download the script here: SSRS_SubscriptionsV1_5

On the Twelfth Day…

by Jason Brimhall

Categories: Corner, News, Professional, SQLServerPedia Syndication, SSC

Tags: Lab, SQL, SQL Administration, Training, Virtual Machines, VirtualBox

Comments: No Comments

Published on: December 24, 2012

Bacon wrapped frog legs (twelve of them) for Christmas. No more drumming for these guys!!

What could be better than bacon wrapped frog legs? Oh yeah, more Virtual lab setup.

We will delve into setting up a SQL Cluster today. We will also cover some high level tips for dealing with virtual box. This will be good information and the type of stuff I would have like to have known heading into setting up a Virtual Lab.

Season Cleaning First.

Domain Setup – 11^th Day
Virtual Storage – 10^th Day
Death and Destruction, err Deadlocks - 9^th Day
SSRS Schedules – 8^th Day
Runaway Jobs – 7^th Day
Maintenance Plan Gravage – 6^th Day
Table Compression – 5^th Day
Exercise for msdb – 4^th Day
Backup, Job and Mail History Cleanup – 3^rd Day
Service Broker Out of Control – 2^nd Day
Maint Plan Logs – 1^st Day

On the Twelfth Day of pre-Christmas…

My DBA brought to me a Virtual SQL Cluster. And with that cluster, we have a a few tidbits for Using VirtualBox.

The first tidbit is an administration aid. Occasionally it is good to have similar machines grouped together. At the same time, it is also necessary to start multiple virtual machines at the same time. This is done through groups in VirtualBox.

Here you can see some of the groups that I have created. If I right-click on a machine name, I will be presented a menu that has the Group option.

Once I have a group created, I can get a few different options if I were to highlight the group name I would get different options as shown in the following image.

The notable options here are to “Ungroup”, “Rename Group”, and “Add Machine.” Another option is “Start.” Though this option is present for the machine menu, the behavior is different. This option allows you to start the entire group. This can be a handy tool when dealing with a cluster for instance.

The next handy tidbit is the snapshot. A snapshot allows point in time image of the VM to be taken so different configurations can be tested – and then quickly reverted i necessary. Here is what I have for one of my VMs in the snapshot manager.

From this very same screen you can also see one of the many methods available to create a clone of a virtual machine. The clone icon is the little button above the right hand pane that looks like a sheep. Cloning a VM is a quick way to create several machines for various purposes. As you will hear from many people – you should build a base image first, then run sysprep against it. Sysprep is necessary in order to help prevent problems down the road.

The next tidbit for today is in regards to the file locations for virtual machines and virtual disks. I recommend changing the default path for the VM files. This can be done through the preferences option on the file menu. Shown in the attachment is what it may look like if you have not changed it. Notice that the default path goes to your user profile directory.

Ignore the red text on this page for now. We will not be discussing the Proxy.

The last tip is in the network settings within the preferences that we already have open. In the network settings, we can find an option to configure DHCP settings for the Host-Only Ethernet Adapter. These are settings you may want to configure to ensure you have more control over the environment. It is also helpful when looking to configure those IP settings for the FreeNAS that we have already discussed.

As I wrap up these tidbits, I have decided that this is a lot of information to soak in at this point. So in the spirit of Christmas, I have decided to finish off the clustering information in a 13th day post. This final post may or may not be available on Christmas day. Worst case it will be available on the 26th.

Part of that reason is I want to rebuild my lab following the instructions I will be posting and I need time to test it. I want the instructions to be of great use.

Please stay tuned as we conclude this series very soon.

On the Eleventh Day…

by Jason Brimhall

Categories: Corner, News, Professional, SQLServerPedia Syndication, SSC

Tags: Lab, SQL, SQL Administration, Training, Virtual Machines, VirtualBox

Comments: No Comments

Published on: December 23, 2012

Yesterday we had an introduction into setting up a virtual lab to help the DBA learn and test new technologies while improving his/her own skill set.

Today we will continue to discuss the building of a virtual lab. Today we will get a little closer to the SQL portion of things as we will be installing a familiar operating system to SQL Server.

The Operating System will be 2008. And the version of SQL Server will be 2008 R2. I chose these specifically because at the time that I built out my lab, I was setting up the environment to help me study for the MCM exams.

As a sidebar, I was just informed by a friend of another blog series that is also currently discussing setting up Virtual Machines in Virtual Box. Fortunately, his series is based on Windows 2012 and SQL 2012 – so there is a bit of a difference. The author of that series is Matt Velic and you can read his articles on the topic here.

I’ll be honest, upon hearing that news I had to go check out his articles to make sure I wasn’t doing the exact same thing. And while there may be a little overlap, it looks like we have different things that we are covering.

And now that brings us to recap time.

Virtual Storage – 10^th Day
Death and Destruction, err Deadlocks - 9^th Day
SSRS Schedules – 8^th Day
Runaway Jobs – 7^th Day
Maintenance Plan Gravage – 6^th Day
Table Compression – 5^th Day
Exercise for msdb – 4^th Day
Backup, Job and Mail History Cleanup – 3^rd Day
Service Broker Out of Control – 2^nd Day
Maint Plan Logs – 1^st Day

On the Eleventh Day of pre-Christmas…

The next pre-requisite for this lab is to install a Domain Controller and Active Directory. For this Domain Controller, I have the following Virtual Box settings.

A single Dynamic Virtual Disk of 20GB
2 Network Adapters (1 NAT and 1 Internal)
1024 MB memory

To install the operating system, we will mount the iso image the same as we did for the FreeNAS in yesterdays post. This is a Windows setup, and I will not cover that.

Once you have installed the operating system, the first thing to do is to install the guest additions for Virtual Box.

With guest additions installed, next we will turn to the network adapters. I have two adapters installed for good reason. One adapter is visible to the virtual network and will be used for the VMs to talk to each other. The second adapter is installed so I can get windows validated and so patches can be downloaded and installed.

Talking about patches, this is where we want to make sure the operating system is patched. Run windows update, finish all of the requisite reboots, and then come back to the network control panel. Prior to installing the domain, disable the external NIC. We will do this to limit the potential for errors when joining the subsequent machines to the domain.

For the Internal adapter, I will also configure a static IP address as shown here.

Let’s now setup the domain and domain controller on this machine. From Server Manager, right click roles and select Add Roles. From the new screen, select Active Directory Domain Services and DNS Server.

You are now ready to configure your domain. I am going to allow you to use your favorite resource for the directions on configuring a domain in Windows 2008. After the domain has been configured, then enable the external network adapter.

The final step is to configure DNS. The main concern in DNS to configure is the reverse lookup zones. I have three subnets (network address ranges) that I will configure. The relevance of these three zones will become apparent in the final article of the lab setup mini-series. The configurations will be along the lines as seen in this next screenshot.

This gets us to where we can start building our SQL Cluster. We will cover that in the next installment.

On the Tenth Day…

by Jason Brimhall

Categories: Corner, News, Professional, SQLServerPedia Syndication, SSC

Tags: Lab, SQL, SQL Administration, Training, Virtual Machines, VirtualBox

Comments: 1 Comment

Published on: December 22, 2012

Silver and Gold have a way of really bringing the look and feel of the Christmas season.

Silver and Gold also seem to represent something of greater value.

We are now into the final three articles of the 12 Days of pre-Christmas. And with these three articles, I hope to bring something that is of more value than anything shared so far.

Of course, the value of these articles is subjective. I have my opinion as to why these are more valuable. I hope to convey that opinion as best as possible to help bring out as much value as can be garnered from these articles.

Let’s first recap what we have to date.

Death and Destruction, err Deadlocks – 9^th Day
SSRS Schedules – 8^th Day
Runaway Jobs – 7^th Day
Maintenance Plan Gravage – 6^th Day
Table Compression – 5^th Day
Exercise for msdb – 4^th Day
Backup, Job and Mail History Cleanup – 3^rd Day
Service Broker Out of Control – 2^nd Day
Maint Plan Logs – 1^st Day

On the Tenth Day of pre-Christmas…

My DBA gave me an education. Sure, everyday so far in this series could possibly be an education. This is an education via a lab. Every DBA should have a lab to be able to test features and hone skills. A lab is a better place to do some of the testing that needs done than the DEV, QA, or even Production environments.

Think about it, do we really want to be testing the setup of clustering in the DEV environment and potentially impact the development cycle? I’d dare so no.

Unfortunately, reality does not always allow for a lab environment to be accessible to the DBA. So the DBA needs to make do with other means. It is due to these types of constraints, that I am devoting the next three days to the setup of a lab. This lab can even be created on a laptop. I created this lab on my laptop with only 8GB of ram. I was quite pleased to see that it performed well enough for my testing purposes.

We will begin with an introduction to the technology used – VirtualBox. I will also discuss the creation of enough virtual machines to create a SQL Cluster (domain controller, two sql boxes, and a NAS) along with the configuration steps to ensure it will work.

For this lab, we will be using Virtual Box. You can download Virtual Box here. And yes, the tool is one that is provided by Oracle. Two of the reasons I want to use Virtual Box is the ability to install multiple operating systems, and the tool is currently free. Another benefit is that I can easily import virtual machines created in VMWare as well as Microsoft Virtual Server/Virtual PC (I have not tested any created in Hyper-V).

While you are downloading the Virtual Box app, download the Extension Pack as well. Links are provided for the extension pack on the same page as the application download. Be sure to download the Extension Pack for the version of Virtual Box you download.

The version of VirtualBox I will be using for this article is 4.2.2. As of the writing of this article a new version has been released – 4.2.6. The differences in versions may cause the instructions in these articles to be inaccurate for 4.2.6. You can use whichever version you deem appropriate. I just won’t be covering version 4.2.6 and don’t know if the screens are different or it the settings are different.

You can check your version in the Help.About Menu.

For this lab, we have a few things that will be required prior to setting up the SQL Cluster. Two big components of this infrastructure are Storage and a Domain. We are going to simulate shared storage through the use of FreeNAS. We will be discussing FreeNAS today.

For starters, we can download FreeNAS from here. You might be able to find a few configuration guides online for FreeNAS. Most of them seemed to be for really old versions and were less than usable for the version that I had downloaded. All settings to be discussed today are pertinent to FreeNAS-8.3.0-RELEASE-x64 (r12701M).

To setup FreeNAS, we will need to have a Virtual Machine configured with the following settings.

A BSD VM with FreeBSD as the version.
Ensure the motherboard settings only has the “Enable IO APIC” setting checked.
Three Virtual Disks (1 for NAS OS, 1 for SAN Storage, and another for a Quorum)
512 MB memory
2 Network Adapters (1 Internal and 1 connected to the Host-Only Adapter)

Despite the FreeNAS actual disk requirements being rather small, any fixed disk size less than 2GB causes mount errors. Any amount of memory less than 512MB also causes a mount problem. These settings are the minimum configurations to save the hair on your head.

The Network Adapters is more of a strong suggestion. I was able to get it to work with only one adapter, but it was more hassle than it was worth. I found it easier to configure for use by the cluster later if I had two adapters. The two adapter configuration also allows me easier administration from within the VM environment as well as from the host machine.

One other thing to do is to mount the FreeNAS ISO that has been downloaded to the CD drive that is created by default with the VM creation. I mount the ISO before booting by opening the settings for the VM within Virtual Box. On the storage screen, highlight the “Empty” CD Icon in the middle then click on the CD Menu Icon on the far right as shown below.

Navigate to the folder where the FreeNAS ISO is saved and then click ok until you are back at the Virtual Box manager screen. You are now ready to start the machine and finish the install and then configure.

Once powered on, you should eventually come to the following screen.

Select to Install/Upgrade. From here, you will see a few more prompts such as the next screen to select the installation location.

This should be pretty straight forward installation options for the IT professional. I will not cover all of the installation prompts. Once the install is finished, you will need to reboot the VM and un-mount the installation media. The system will then come to the following screen.

Now that we are at the console screen, the next step is to configure the Network Interfaces. You can see that I have already done this based on the IP addresses seen at the bottom of the screen. I will leave the configuration of the IP addresses to you. Both the internal network and the host-only network will need to be configured. The host network should be the second adapter. Keep track of the IP addresses that have been configured. We will need to use them later.

In a browser window we will now start configuring the storage to be used by our Lab. In the address bar, we will need to input the address we configured for the host network. In my case, 192.168.56.103. When that page loads, the first thing we need to do is change the Admin password.

The default password is empty. Pick a password you will remember and that is appropriate. With that done, we can configure the storage.

The Next setting, I want to configure is the iSCSI setting. In order to use the volumes that we create, we must enable the iSCSI service. In the top section, click the Services button. This will open a new tab in the web browser. On the Services tab, we need to toggle the slider for iSCSI to the “ON” position as shown in the image.

Once toggled, we can configure the iSCSI settings for the volumes we will now create. From here, we click on the storage tab. Next, click on the Volume Manager Button. In order for the disks to be imported, we have to use volume manager. The Import Volume and Auto Import Volume must serve other purposes – but they don’t work for importing a new volume. Here is a screenshot demonstrating what needs to be configured.

With the Volume created, a ZFS volume must next be created from within the storage management. We do this by clicking the “Create ZFS Volume” icon next to the main volume we just created. This icon is illustrated as the icon on the far right in the next image.

Once that icon is clicked, you will be presented with a new dialog. The dialog is demonstrated in the above image. Give the Volume a Name and then give it a size. Note that you must specify a storage unit (m or g for example) or you will receive a pretty red error message.

Now go back to the Services tab where we enabled iSCSI. There is a wrench icon next to the toggle to enable the service. Click on this wrench and a new tab will be opened (again within the FreeNAS webgui) and the focus will be switched to this new tab. On the “Target Global Configuration” ensure that Discovery Auth Method is set to “Auto.” If it is not, make the change and click save at the bottom.

Next is the Portals. The portals should be empty so we will need to add a portal. By default, only one IP address is displayed for configuration on a new Portal entry. We want to configure two IP addresses. First, select 0.0.0.0 from the IP Address drop down on the new window that opened when clicking on “Add Portal.” Then select “Add extra Portal IP”.

Next is to configure an Initiator. For this lab, I created on Initiator specifying ALL for the Initiators and Authorized Network as shown here.

With an initiator and a portal in place, we now proceed to the configuration of the Targets. I have configured three targets and the main difference is in the name. They should be configured as shown here.

Almost done with the setup for the storage. It will all be well worth it when we are done. We need to configure Device Extents and then Associate the targets, then we will be done.

Like with the Targets, I have three device extents configured. The configuration for each is the same process. I want to give each a name that is meaningful and then associate the extent to a disk that we imported earlier.

Last for this setup is the Target to Extent association. This a pretty straight forward configuration. I named my targets the same as extents so there was no confusion as to which should go with which.

That wraps up the configurations needed to get the storage working so we can configure a cluster later on. Just getting through this configuration is a pretty big step in getting the lab created for use in your studies and career enhancement.

Next up in this series is to show how to configure (in limited detail) a domain and DNS, and then to install and configure a cluster. Stay tuned and I will even through in a few tidbits here and there about Virtual Box.

I didn’t include every screenshot possible throughout the setup of FreeNAS and the configuration of iSCSI. Part of the fun and education of a lab is troubleshooting and learning as you go. If you run into issues, I encourage you to troubleshoot and research. It will definitely strengthen your skill-set.

page 1 of 4»

SQL RNNR

Lost that Single-User Connection?

Now What?

T-SQL Tuesday #040: File and Filegroup Wisdom

Backstory

Production Down

Between the Lines

Is your LOG backed up?

Audit Configuration Changes

Last Time CheckDB was Run

A Firm Foundation

Story the First

Story the Second

SSRS Subscriptions Report

On the Twelfth Day…

On the Twelfth Day of pre-Christmas…

On the Eleventh Day…

On the Eleventh Day of pre-Christmas…

On the Tenth Day…

On the Tenth Day of pre-Christmas…

Now Reading

Planned books:

Current books:

Recent books:

Popular Posts

Blogroll

Categories

SQLHelp

May 2013
M	T	W	T	F	S	S
« Apr
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31