Thursday, August 30, 2012

Install product update during SQL 2012 installation

SQL Server 2012 setup program has 2 new installation parameter
1. /UpdateEnabled:
Specify whether SQL Server setup should discover and include product updates. The valid values are True and False or 1 and 0. By default, SQL Server setup will include updates that are found.

2. /UpdateSource
Specify the location where SQL Server setup will obtain product updates. The valid values are “MU” to search Microsoft Update, a valid folder path, a relative path such as .\MyUpdates or a UNC share. By default, SQL Server setup will search Microsoft Update or a Windows Update Service through the Windows Server Update Services.

Here is sample. we saved the CU in c:\temp\sp1

Start the setup process with command line
setup /ACTION=Install /UpdateEnabled=True /UpdateSource=c:\temp\sp1

in the next setup window, setup program will search proper CU or SP automatically in the path  c:\temp\sp1.

you can select including this update or not.

If there are many CU or SP files in the updatesource path, sql server setup program will only display the latest SP+Latest CU .

you can also set UpdateSource=MU which use microsoft update service to get the product updates.you need to make sure update serve is up and firewall is setup correctly. then setup program will search Microsoft update.


This feature is helpful if you have a lot of server need to setup, you can put all service pack and CU in central place.

Tuesday, August 21, 2012

Restore Database In SQL 2012

In the post below I mentioned a store procedure which  can generate the database restore script based on the msdb history table
http://jamessql.blogspot.com/2012/04/generate-restore-script-automatically.html


Obviously, I had not tried restore in SQL Server 2012 Management Studio at that time. Now we have SQL 2012, which can provide more powerful ways to restore database. let's run the test script first.
==================================
--Create Test database
CREATE DATABASE [Test]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'Test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\Test.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'Test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\Test_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [Test] SET RECOVERY FULL
GO
ALTER DATABASE [Test] SET PAGE_VERIFY CHECKSUM 
GO
ALTER DATABASE [Test] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
SET NOCOUNT ON
GO
USE [Test]
GO
--Create TABLE
create table mytest (a int, b char(10))
go
--Create full backup
Print convert(varchar(101),getdate(),113)
Print '--Created full backup 1'
backup database [Test] to disk = 'C:\temp\backup\Test_full_1.bak'
go
waitfor DELAY '0:01:00'
Print convert(varchar(101),getdate(),113)
Print '--Insert batch - 1'
go
insert into mytest values(1, '1')
go 5
waitfor DELAY '0:01:00'
Print convert(varchar(101),getdate(),113)
Print '----Create diff backup 2'
backup database [Test] to disk = 'C:\temp\backup\Test_diff_2.bak' WITH  DIFFERENTIAL
go
waitfor DELAY '0:01:00'
Print convert(varchar(101),getdate(),113)
Print '--Insert batch - 2'
go
insert into mytest values(2, '2')
go 5
Print convert(varchar(101),getdate(),113)
Print '------Create Log backup 3'
BACKUP LOG  [Test] TO  DISK = N'C:\temp\backup\Test_log_3.trn'
go
waitfor DELAY '0:01:00'
Print convert(varchar(101),getdate(),113)
Print '--Insert batch - 3'
go
insert into mytest values(3, '3')
go 5
Print convert(varchar(101),getdate(),113)
Print '--Created full backup 4'
backup database [Test] to disk = 'C:\temp\backup\Test_full_4.bak'
go
waitfor DELAY '0:01:00'
Print convert(varchar(101),getdate(),113)
Print '--Insert batch - 4'
go
insert into mytest values(4, '4')
go 5


Print convert(varchar(101),getdate(),113)
Print '------Create Log backup 5'
BACKUP LOG  [Test] TO  DISK = N'C:\temp\backup\Test_log_5.trn'
go
waitfor DELAY '0:01:00'
Print convert(varchar(101),getdate(),113)
Print '--Insert batch - 5'
go
insert into mytest values(5, '5')
go 5
Print convert(varchar(101),getdate(),113)
Print '----Create diff backup 6'
backup database [Test] to disk = 'C:\temp\backup\Test_diff_6.bak' WITH  DIFFERENTIAL
go
waitfor DELAY '0:01:00'
Print convert(varchar(101),getdate(),113)
Print '--Insert batch - 6'
go
insert into mytest values(6, '6')
go 5
Print convert(varchar(101),getdate(),113)
Print '------Create Log backup 7'
BACKUP LOG  [Test] TO  DISK = N'C:\temp\backup\Test_log_7.trn'
go
waitfor DELAY '0:01:00'
Print convert(varchar(101),getdate(),113)
Print '--Insert batch - 7'
go
insert into mytest values(7, '7')
go 5
Print convert(varchar(101),getdate(),113)
Print '------Create Log backup 8'
BACKUP LOG  [Test] TO  DISK = N'C:\temp\backup\Test_log_8.trn'
go
waitfor DELAY '0:01:00'
Print convert(varchar(101),getdate(),113)
Print '--Insert batch - 8'
go
insert into mytest values(8, '8')
go 5
SET NOCOUNT OFF
GO
==================================

We created database "Test", inserted data and generated full/diff/log backup. In SSMS,  right click database "Test", and select "Task"->"Restore"->"Database",  the new UI popup

1. At the top of the window, there is message which notice you that a tail-log backup will be taken by default. That's a nice option, if you want to recover database to current point, a tail-log backup of current active database is necessary.
In the "Option" page, you can set the path of the tail-log backup file.

2. You can select restore source from

  • Database: which get the restore information from msdb backup history tables.
  • Device: which get the  restore information from backup file header
3. in "Destination" section, you can set the new database name, and restore database to any time point, which is a really nice feature. here let's change the dbname to "TestDB"

4. According to the source you select, the restore window will auto list all backup files which meet your requirement( which time point you want to restore).
In our sample, it listed last full backup +diff+2 log backup.

5. before you restore, you can click "Verify Backup Media" button to verify if backup file is corrupted. That's really helpful when you try to restore many files, but if the size of the files are big, it will take long time to verify.

6. In Files page, here is a enhancement, you can change the location of all files, it is convenient if you have multi-files.

7. In "options" page, we can set the path of tail-log backup file. since we restore test database to a new database TestDB, so we unchecked the "Leave source database in the restoring state" option.

Click OK, the new database testdb will be restored, however, you will not find data batch 8 in the mytest table, because we didn't restore trail-log backup.

Next, let's try restore to time point.
1. Click the "TimeLine..." button to open the Backup timeline window. here you can set the specific date and time.


2. Click OK and return to the restore window, in the restore plan section, it will list all backup files auto. that's really nice.

Last, let's try to restore database with the backup files directly.

1. Drop the Test Database with all backup history in msdb

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'Test'
GO
USE [master]
GO
ALTER DATABASE [Test] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [Test]
GO



2. Click "Restore Databases" to open the restore db window, and select "Device" in Source section.

3. add all backup files, click ok

4. in the restore plan section, it will list the restore plan with the files we need.
if any backup file is missing, the tool will report error that the restore chain is broken.
I think if the file name could be listed in the restore plan as well, that will be great! without the file name, I can not know which files I will use to restore. the only way I can use is script the restore action, and check the script.


Except for improvement of the restore database, there is new UI for page restore as well. I like these improvement for the db restore :)






Sunday, August 12, 2012

Monitor Deadlock in SQL 2012

Do you still use trace flag 1204 and 1222 to monitor Deadlock? or using profile to capture deadlock? Now we are in SQL Server 2012!  One of the biggest improvement of SQL 2012 is Extended Events.

Extended Events can replace SQL Profiler, and it is more powerful with less performance impact than SQL Profiler. Extended Events has been introduced in SQL Server world from SQL2008, and in SQL2012, it has been integrated into SQL Server Management Studio(SSMS), see the pic below:

Now you can use SSMS to manage your Extends Events session. By default, there are 2 session created, "AlwaysOn_health" and "system_health", and "system_health" is started when SQL Service startup. You can script the session and check the defination:
CREATE EVENT SESSION [system_health] ON SERVER
ADD EVENT sqlclr.clr_allocation_failure(
    ACTION(package0.callstack,sqlserver.session_id)),
ADD EVENT sqlclr.clr_virtual_alloc_failure(
    ACTION(package0.callstack,sqlserver.session_id)),
ADD EVENT sqlos.memory_broker_ring_buffer_recorded,
ADD EVENT sqlos.memory_node_oom_ring_buffer_recorded(
    ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack)),
ADD EVENT sqlos.scheduler_monitor_deadlock_ring_buffer_recorded,
ADD EVENT sqlos.scheduler_monitor_non_yielding_iocp_ring_buffer_recorded,
ADD EVENT sqlos.scheduler_monitor_non_yielding_ring_buffer_recorded,
ADD EVENT sqlos.scheduler_monitor_non_yielding_rm_ring_buffer_recorded,
ADD EVENT sqlos.scheduler_monitor_stalled_dispatcher_ring_buffer_recorded,
ADD EVENT sqlos.scheduler_monitor_system_health_ring_buffer_recorded,
ADD EVENT sqlos.wait_info(
    ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([duration]>(15000) AND ([wait_type]>(31) AND ([wait_type]>(47) AND [wait_type]<(54) OR [wait_type]<(38) OR [wait_type]>(63) AND [wait_type]<(70) OR [wait_type]>(96) AND [wait_type]<(100) OR [wait_type]=(107) OR [wait_type]=(113) OR [wait_type]>(174) AND [wait_type]<(179) OR [wait_type]=(186) OR [wait_type]=(207) OR [wait_type]=(269) OR [wait_type]=(283) OR [wait_type]=(284)) OR [duration]>(30000) AND [wait_type]<(22)))),
ADD EVENT sqlos.wait_info_external(
    ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([duration]>(5000) AND ([wait_type]>(365) AND [wait_type]<(372) OR [wait_type]>(372) AND [wait_type]<(377) OR [wait_type]>(377) AND [wait_type]<(383) OR [wait_type]>(420) AND [wait_type]<(424) OR [wait_type]>(426) AND [wait_type]<(432) OR [wait_type]>(432) AND [wait_type]<(435) OR [duration]>(45000) AND ([wait_type]>(382) AND [wait_type]<(386) OR [wait_type]>(423) AND [wait_type]<(427) OR [wait_type]>(434) AND [wait_type]<(437) OR [wait_type]>(442) AND [wait_type]<(451) OR [wait_type]>(451) AND [wait_type]<(473) OR [wait_type]>(484) AND [wait_type]<(499) OR [wait_type]=(365) OR [wait_type]=(372) OR [wait_type]=(377) OR [wait_type]=(387) OR [wait_type]=(432) OR [wait_type]=(502))))),
ADD EVENT sqlserver.connectivity_ring_buffer_recorded(SET collect_call_stack=(1)),
ADD EVENT sqlserver.error_reported(
    ACTION(package0.callstack,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack)
    WHERE ([severity]>=(20) OR ([error_number]=(17803) OR [error_number]=(701) OR [error_number]=(802) OR [error_number]=(8645) OR [error_number]=(8651) OR [error_number]=(8657) OR [error_number]=(8902)))),
ADD EVENT sqlserver.security_error_ring_buffer_recorded(SET collect_call_stack=(1)),
ADD EVENT sqlserver.sp_server_diagnostics_component_result(SET collect_data=(1)
    WHERE ([sqlserver].[is_system]=(1) AND [component]<>(4))),
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'system_health.xel',max_file_size=(5),max_rollover_files=(4)),
ADD TARGET package0.ring_buffer(SET max_events_limit=(5000),max_memory=(4096))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

So "system_health" will monitor the deadlock event by default. let's try the script below to generate deadlock scenario
====================================================
--create table
create table a1(a int)
create table b1(b int)
insert into a1 values(1)
insert into b1 values(1)

--run in first session
select @@SPID
begin tran
update  a1 set a=2
update  b1 set b=2
--run in second session
select @@SPID
begin tran
update  b1 set b=2
update  a1 set a=2
====================================================

Congrat, you got deadlock and saw the error below















Go back to SSMS,


 double click the "package0.event_file" under "system_health", you can review all the event just like below:



Double Click "Value" to check the deadlock detail, here you can find the process and resource info for the deadlock
==============================================
<deadlock>
 <victim-list>
  <victimProcess id="process2ed016558" />
 </victim-list>
 <process-list>
  <process id="process2ed016558" taskpriority="0" logused="248" waitresource="RID: 6:1:169:0" waittime="3029" ownerId="54473" transactionname="user_transaction" lasttranstarted="2012-08-12T18:59:15.827" XDES="0x2f8252d28" lockMode="U" schedulerid="3" kpid="4852" status="suspended" spid="56" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-08-12T18:59:23.397" lastbatchcompleted="2012-08-12T18:59:15.830" lastattention="1900-01-01T00:00:00.830" clientapp="Microsoft SQL Server Management Studio - Query" hostname="V-XUJ1230" hostpid="5688" loginname="FAREAST\v-xuj" isolationlevel="read committed (2)" xactid="54473" currentdb="6" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="16" sqlhandle="0x020000006377082c50d69d2e5f1de789330d2a1e2eda81960000000000000000000000000000000000000000">
UPDATE [b1] set [b] = @1    </frame>
    <frame procname="adhoc" line="1" sqlhandle="0x0200000055304113cb84f9da843e5bdb59f3c2ace4f8aadd0000000000000000000000000000000000000000">
update  b1 set b=2    </frame>
   </executionStack>
   <inputbuf>
update  b1 set b=2
   </inputbuf>
  </process>
  <process id="process2ed0170c8" taskpriority="0" logused="248" waitresource="RID: 6:1:166:0" waittime="6233" ownerId="54474" transactionname="user_transaction" lasttranstarted="2012-08-12T18:59:18.503" XDES="0x2f82523a8" lockMode="U" schedulerid="3" kpid="1456" status="suspended" spid="52" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-08-12T18:59:20.210" lastbatchcompleted="2012-08-12T18:59:18.503" lastattention="1900-01-01T00:00:00.503" clientapp="Microsoft SQL Server Management Studio - Query" hostname="V-XUJ1230" hostpid="5688" loginname="FAREAST\v-xuj" isolationlevel="read committed (2)" xactid="54474" currentdb="6" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="16" sqlhandle="0x020000005cdb030dd161d461be83dc620591979030bbf17f0000000000000000000000000000000000000000">
UPDATE [a1] set [a] = @1    </frame>
    <frame procname="adhoc" line="1" sqlhandle="0x020000008278b7001a4bf6c0edd6eb92e71651f531b4b9da0000000000000000000000000000000000000000">
update  a1 set a=2    </frame>
   </executionStack>
   <inputbuf>
update  a1 set a=2
   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <ridlock fileid="1" pageid="169" dbid="6" objectname="CDBTEST.dbo.b1" id="lock2f4b46480" mode="X" associatedObjectId="72057594039107584">
   <owner-list>
    <owner id="process2ed0170c8" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process2ed016558" mode="U" requestType="wait" />
   </waiter-list>
  </ridlock>
  <ridlock fileid="1" pageid="166" dbid="6" objectname="CDBTEST.dbo.a1" id="lock2f4b49180" mode="X" associatedObjectId="72057594039042048">
   <owner-list>
    <owner id="process2ed016558" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process2ed0170c8" mode="U" requestType="wait" />
   </waiter-list>
  </ridlock>
 </resource-list>
</deadlock>
==============================================
or you can click the "Deadlock" TAB to see the diagram.