Solved

SQL 2012 Add Volumes to Cluster and Maintenance plans

Posted on 2014-02-26
18
479 Views
Last Modified: 2014-02-28
I am in the process of adding new volumes to my SQL 2012 cluster and need to create a new maintenace plan.

1) I have already presented the new volumes to both nodes and configured the volume on the primary node. I see the volumes listed add offline and not initilized on the secondary node should I leave this as is? I want to make sure this part is setup correctly first before I add the volume to SQL.

2) When I create a maintenance plan it creates a job called name.subPlan_1 Is there anyway to rename these jobs?

3) I want to do a full backup on all DB's and have select the option to create a sub folder for all DB's I have also selected the option to backup the transaction log and created a sub folder with I now have two subfolder per DB?

3)My SQL server only host SharePoint 2010 DB's and according the MS Sharepoint maintance Plan recommendations document from 5/12/11 I should be doing a Index Rebuild over a ReOrg. Won't a rebuild take my DB's offline????

4) the past admin created a IndexOptimixe job via command line but I am not sure if it is doing a rebuild or reorg...

"sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[IndexOptimize] @Databases = 'USER_DATABASES', @LogToTable = 'Y'" -b"

5) Why is it so hard to delect a schedule subplan.1 after you created a maintenance. So far the only way I have found to delete a job that isn't even scheduled is to delete the whole plan..

Please help!!!!!!!!!!!!!!!!
0
Comment
Question by:compdigit44
  • 9
  • 6
  • 3
18 Comments
 
LVL 76

Expert Comment

by:arnold
ID: 39891351
The volume has to be added as a resource within the sql group.
When you are using the maintenance plan GUI, you can name/rename the sub-plan which will correspond to the name of the job
Maintenance_plan_name.subplan_name

Within the maintenance plan, you can disable the plan, delete the schedule (hit the X on the right)
You can within the maintenance plan delete the subplan.

Look at the indexoptimize sP under the master DB and see what the code does.
0
 
LVL 19

Author Comment

by:compdigit44
ID: 39891567
Thanks for the response...

IN SQL 2012 does an index rebuild require the databases to bee offline? If not can this be run and still have the DB accessible?
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 39891575
That stored procedure is part of Ola Hallegren maintenance solution, which is one of the best. The way it works by default is this:

Fragmentation%, action

if <10 do nothing
between 1 and 30 do reorganize
>30 do rebuild

You can change that and force rebuild if you use parameters as pr documentation here:

http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

The parameter that says
@LogToTable = 'Y'

means that there is a table in master database, or whatever database that procedure was created that will show you what action has been applied. To see that you have to query that table:

SELECT TOP 1000 [ID]
      ,[DatabaseName]
      ,[SchemaName]
      ,[ObjectName]
      ,[ObjectType]
      ,[IndexName]
      ,[IndexType]
      ,[StatisticsName]
      ,[PartitionNumber]
      ,[ExtendedInfo]
      ,[Command]
      ,[CommandType]
      ,[StartTime]
      ,[EndTime]
      ,[ErrorNumber]
      ,[ErrorMessage]
  FROM [zb_dba_maint].[dbo].[CommandLog]
  where 
	[CommandType] like 'ALTER%'
	and starttime >= dateadd(dd,-7, getdate())

Open in new window

Also you can check the jobs output file which you can find if you check the job, which is probably called IndexOptimize - USER_DATABASES, right click > Properties > Steps > Select the step and click Edit > Advance and you will see where the output file is created.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39891579
In regards to your question about rebuild online the answer is yes. You will actually see in that query result that the rebuild uses the option ONLINE=ON if the server is 2008 and up.

I recommend you to use the stored procedure from that maintenance solution instead of creating a new plan. You can execute the stored procedure directly in a query window or just build a one time job as T-SQL command with the call for that procedure. Change the parameters according to your needs following the documentation:
EXECUTE [dbo].[IndexOptimize] 
	@Databases = 'USER_DATABASES', 
	@LogToTable = 'Y',
	...

Open in new window

0
 
LVL 19

Author Comment

by:compdigit44
ID: 39891584
How can I check for fragmentation?
0
 
LVL 19

Author Comment

by:compdigit44
ID: 39891590
BTW, thank you for all of your help...

By any chance do you have thoughts on my first question....


1) I have already presented the new volumes to both nodes and configured the volume on the primary node. I see the volumes listed add offline and not initilized on the secondary node should I leave this as is? I want to make sure this part is setup correctly first before I add the volume to SQL.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39891609
Just disable it.

You can check the fragmentation with this:
	drop table #idx_frg
SELECT 
	DB_NAME(DB_ID()) AS DatabaseName,
    schemas.[name] AS SchemaName,
    objects.[name] AS ObjectName,
    indexes.[name] AS IndexName,
    objects.type_desc AS ObjectType,
    indexes.type_desc AS IndexType,
    dm_db_index_physical_stats.partition_number AS PartitionNumber,
    dm_db_index_physical_stats.page_count AS [PageCount],
    right('____'+cast(round(cast(dm_db_index_physical_stats.avg_fragmentation_in_percent as decimal(8,2)),2) as varchar),6) AS AvgFagmentationInPercent
into #idx_frg
FROM 
	sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') dm_db_index_physical_stats
	INNER JOIN sys.indexes indexes 
		ON dm_db_index_physical_stats.[object_id] = indexes.[object_id] 
		AND dm_db_index_physical_stats.index_id = indexes.index_id
	INNER JOIN sys.objects objects 
		ON indexes.[object_id] = objects.[object_id]
	INNER JOIN sys.schemas schemas 
		ON objects.[schema_id] = schemas.[schema_id]
WHERE 
	objects.[type] IN('U','V')
	AND objects.is_ms_shipped = 0
	AND indexes.[type] IN(1,2,3,4)
	AND indexes.is_disabled = 0
	AND indexes.is_hypothetical = 0
	AND dm_db_index_physical_stats.alloc_unit_type_desc = 'IN_ROW_DATA'
	AND dm_db_index_physical_stats.index_level = 0
	AND dm_db_index_physical_stats.page_count >= 1000
order by 
	DatabaseName,
	SchemaName,
	ObjectName,
	IndexName
select 
	*, 
	case 
		when replace(AvgFagmentationInPercent,'_','') between 10.00 and 29.99 then 'Reindex' 
		else 'Rebuild' end as 'Action' 
from 
	#idx_frg where replace(AvgFagmentationInPercent,'_','')>10.00 
order by 
	AvgFagmentationInPercent desc

select * from #idx_frg

Open in new window

It is the same the stored procedure does it. I insert the results into a temp table which I select from at the bottom. I do this because the query can take some time to run, 10-15 min even depending of how big your indexes are.

You don't need to check if you want to force the rebuild. Just look in the link I sent you and change the parameter like this:
EXECUTE dbo.IndexOptimize
	@Databases = 'USER_DATABASES',
	@FragmentationLow = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
	@FragmentationMedium = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
	@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
	@UpdateStatistics = 'ALL',
	@LogToTable = 'Y'

Open in new window

I used INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE because in certain situations you can't rebuild indexes online(spatial and/or xml indexes) in which case the OFFLINE will be used.
0
 
LVL 19

Author Comment

by:compdigit44
ID: 39891616
Thanks I once I closed and reopened Management Studio I was able to delete the maintenance plan.

I am running the query you posted now...


I am sorry to both you again but... do you have any thought on my first question...


1) I have already presented the new volumes to both nodes and configured the volume on the primary node. I see the volumes listed add offline and not initilized on the secondary node should I leave this as is? I want to make sure this part is setup correctly first before I add the volume to SQL.
0
 
LVL 19

Author Comment

by:compdigit44
ID: 39891631
I am reading hallengren' website now and I need to edit the follow syntax to allow for backup compression.. Is my follow syntax correct?????

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'D:\backup', @BackupType = 'FULL', @Verify = 'Y',  @CleanupTime = 24, @Compress = Y, @CheckSum = 'Y', @LogToTable = 'Y'" -b
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 26

Expert Comment

by:Zberteoc
ID: 39891646
Yes. The only part you need to edit is between "".

Also you should schedule diff and transaction log backus. You could use something like this:

1. FULL once a week
2. DIFF every week day except for the day when the FULL is executed
3. Transaction log backup every 15 minutes. The interval could be different if you want.

If you run the general script the jobs are created automatically for you and you only need to modify them according to your needs.
0
 
LVL 19

Author Comment

by:compdigit44
ID: 39891650
Thanks I did run the script and need to edit it...

I am do not understand what you are referring to with the "".

Also, in Windows 2008 cluster do I only have to initialize /format the volumes on one node??
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39891655
The job is executed as a windows command using sqlcmd command line for some good reasons. However what that does is actually to execute the stored procedure against the current server and that is wrapped in double quotes, "". That part in your case is:
EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'D:\backup', @BackupType = 'FULL', @Verify = 'Y',  @CleanupTime = 24, @Compress = Y, @CheckSum = 'Y', @LogToTable = 'Y'

Open in new window

That part you can edit if you want but everything else you keep untouched.
0
 
LVL 19

Author Comment

by:compdigit44
ID: 39891661
OK so every value needs quots..

Do you have any thought on my cluster question please
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39891686
One more thing. If you want to force the index rebuild, using the example posted before, you should do it only once. After that just use the procedure with the default parameters because it will automatically apply whatever action is needed depending on fragmentation level.

With cluster issue I have no enough experience to be able to help.
0
 
LVL 76

Expert Comment

by:arnold
ID: 39891716
Is the new volume been added as a storage resource within the sql group?

The following link deals with the storage resource addition to an existing cluster.

http://technet.microsoft.com/en-us/library/hh831414.aspx
http://blogs.msdn.com/b/clustering/archive/2012/06/02/10314262.aspx
http://technet.microsoft.com/en-us/library/cc771350.aspx
0
 
LVL 19

Author Comment

by:compdigit44
ID: 39891726
My question is outside of SQL.

1) I have present the volume to both nodes and it is visible in My Computer..

At this point do I add the volume to SQL or do I have to bring the volume online on the other node?
0
 
LVL 19

Author Comment

by:compdigit44
ID: 39895490
Thank for the help everyone. Late yesterday I added new volumes to my SQL cluster, moved my DB, log and TEMPDB files to new volumes and was able to bring everything back online. I was a little unnerving but I learned a lot thanks to everyone.


I am not a DBA both more of Active Directory / Vmware person so if anyone needs help in the future make sure you post your question and I will do the best that I can to help!!!!
0
 
LVL 76

Expert Comment

by:arnold
ID: 39896222
A few experts assisted you with the 5 questions on different topics that you had.

In the future, it would be best if you have a single topic per question. It would be fairer to all participants.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video discusses moving either the default database or any database to a new volume.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now