PDW Database Backup

The backup is stored in the G:\Backups directory on the appliance 

There are two type of backup 

Full Backup : A full database backup is a backup of an entire  PDW database similar to SQL Server Backup.

Differential Backup :A differential database backup only includes changes made since the last full PDW Database backup 

Syntax :  FULL BACKUP 

use master

backup database AdventureWorksPDW2012 to disk = '\\10.10.10.10\PDWBackups\FULL\05052015\AdventureWorksPDW2012.bak' 

The above statement creates the full backup of of the AdventureWorksPDW2012 database . The backup will be stored in \\10.10.10.10\PDWBackups\FULL\05052015\ location. To grant access on this shared location please use sp_pdw_add_network_credentials  command 

Syntax :  DIFFERENTIAL BACKUP 

backup database AdventureWorksPDW2012 to disk = '\\10.10.10.10\PDWBackups\DIFFERENTIAL\05052015\AdventureWorksPDW2012.bak with differential' 

 

You can not perform differential backup on master database

You can copy a database to a different appliance by copying the backup files to the G:\Backups directory on the Backup node of the target appliance and then performing a restore. However, copying backup files to a different location can negatively impact restore performance. This can occur when the logical disk storage for the backup files varies from the original backup directory.
The target appliance for restoring the backup must have at least as many Compute nodes as the source appliance. The target can have more Compute nodes than the source appliance, but cannot have fewer Compute nodes.

Execute queries in PDW

After login into PDW Server ( How to Login into PDW )

 Right click on the server name and click on New Query option in the pop-up window

It will open new window in right side 

Type the query to execute in right side window 

to execute query use below option 

 Mark the query which you want to execute and use CTRL + SHIFT+ E

or Mark the query which you want to execute and click on the below button

it use master database and execute the query and shown below results 

use below options to show the results in grid,text, file

How to connect PDW

You can login pdw using MS Visual Studio 2012/SQL Data Tools  or Nexus Query Chameleon

Lets try using Visual Studio 2012 

Goto Visual Studio 2012 icon and click 

Click on Run the program without getting help

you can see 

Click on View and select SQL Server Object Explorer 

now you can see below screen right click on the SQL Server 

Select Add SQL Server 

Provide the PDW server and Login & password details ( you can login using SQL Authentication or using Windows Authentication) 

and click on connect 

How to grant access on backup shared in PDW

How to grant access on backup shared in PDW

To backup the database we have to assign access permissions to a windows account that has write access to a file share on a target server.

sp_pdw_add_network_credentials '10.10.10.10','corp\adAcctLogin', 'PassWord'

To remove access to shared path  

sp_pdw_add_network_credentials '10.10.10.10','corp\adAcctLogin', 'PassWord'

To check who are all having permissions 

select * from sys.dm_pdw_network_credentials

What is Trace Flag? Enable / Disabling Trace Flag

Trace flag is a directive used to " set specific server characteristics or switch off a perticular behavious" -- is a setting that in some way or another alter the behaviour of various SQL Server functions

Trace flags are used to generate internal information regarding SQL Server's activities, primarily so that Microsoft developers can troubleshoot their code.

Trace flags are enbled in SESSION or GLOBAL levels

How to check whcih trace flags are enabled in the server globally

DBCC  TRACESTATUS(-1) 

Above command will display the status of all trace flags that are currently enabled globally.

How to check whcih trace flags are enabled in the server for current session

DBCC  TRACESTATUS()

Above command will display the list of all trace flags that are enabled for the current session 

You can enable/implement Trace Flags in several ways

• using SQL Server Configuration Manager
• using Registry Editor
• using Query Analyser

Implementing Trace Flag using SQL Configuration Manager 

This is also know as Enabling Trace Flag using –T Startup Option

Adding trace flag in Startup Parameters 

-T startup option to specify that the trace flag be set on during startup. Saparate each trace flag by semi-colon(;). -T startup option enables a trace flag globally 

You can't enable a session level trace flag  by using start up option

  

Implementing Trace Flags using Registry (REGEDIT.MSC)

This is not a recomended method. please avoid using it in production box.

• Goto Run and type regedit.msc and press endter
• Goto below location in registry
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQL2008\MSSQLServer\Parameters
• Add the trace flag by right click on the Parameters folder and click on New and then String Value
• Provide the name as SQLArg3 and then right click on the key value to modify the value.
• Now provide the value as -T1222 

Implementing Trace Flags using Query Analyser 

Using DBCC TRACEON command you can enable the trace flags.All trace flags can't be enabled using DBCC TRACEON e.g. DBCC TRACEON (835), this can be enabled during the server startup only.

You can enable Session Scope or Global Scope using this command

Session Scope : Using below command we can enable session level trace flag

DBCC TRACEON(1222)

Global Scope :  Using below command we can enable global level trace flag

DBCC TRACEON(1222,-1)

Disabling Trace flag

Remove from start-up parameters 

• Go to SQL Server configuration manager and to sql server properties 
• Go to Advanced Tab and click on drop box of start-up parameter and remove the parameter -T1222
• Restart SQL Server services 

Remove from Registry 

• Go to regedit.exe and 
• Browse HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQL2008\MSSQLServer\Parameters
• Remove the string value which has -T1222
• Restart the SQL Server services 

Remove using Query Analyser 

use below command to turn off session level trace flag

DBCC TRACEOFF(1222)

use below command to turn off global level trace flag

DBCC TRACEOFF(1222,-1)

Caution : 

Do not use trace flags in production environment without testing it on non production environments, trace flags should be used under the guidance of Microsoft SQL Server support.

Installing SQL Server – reboot required check fails

Not able to install SqlServer 2008 says Restart computer failed

SQL Server Installation Rules and "System Reboot Required" Error

I get the error message the prerequisites  "Restart the computer is required".

When installing SQL Server some times you may run to situation for "Reboot required check failed" failed issue in prerequisites validation screen.  To skip this failure without restarting machine. 

Goto run and type regedit.exe to open registry 

in registry 

Goto:  HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SessionManager , and instead of "EXPANDING" Session Manager, "SELECT" it and you will see the entry called, PENDING FILE RENAME OPERATION 

Take backup of registry value first.

Then, If PENDING FILE RENAME OPERATION has any values just remove it and clear the value

Reboot the System and again go to PENDING FILE RENAME OPERATION and see if there is any values still available.

--

Thanks & Regards
Venkat Sangu

How to check the database size in PDW

Using below DBCC command via NEXUS query editor or SQL Server DATATOOL or Visual Studio

USE DATABASE_NAME

dbcc pdw_showspaceused

You can view the size in Mega Bytes