Powershell - Copy .bak files

Copy .bak files from Local drive in SQL server to Central share

If you like command line scripting, you will like powershell. We have .bak files located on a backup drive on SQL server. They need to be copied to central share location for disaster recovery. I created the following powershell code. It is scheduled in SQL Agent. One tricky part is if  SQL job agent does not throw errors due to the central share does not exist or files do not not exit. So, we need to add error handling to let us know if the job fails.

cd c:
$originalpath = "S:\SQL\backup\"
$Destinationpath = "\\backups\bak\"
$filespath = "$Destinationpath\backup"
if (!(test-path $filespath))
{
    
    Write-Error "File Path error" -ea Stop
}

if(test-path $filespath)
{
    Remove-item $filespath -recurse -ea STOP
    Try
    {
        Copy-Item -Path $originalpath -filter *.bak -Destination $Destinationpath -force -Recurse -ea STOP -Errorvariable myError
    }
    Catch
    {
        Write-Error "Job Failure with $myError" -ea Stop
    }
   
}

Logins / Credentials / Proxies

Logins/Credentials/Proxies enforce the principle of least privilege.

Recently, we have built three production servers (SQL Server 2012) for Datawarehouse Team.

• SQL Server 2012 Enterprise Edition
• CU3
• SSIS Catalog is installed and configured

Datawarehouse Team built SSIS packages and imported to SSIS Catalog. SSIS packages which are imported in SSIS Catalog are scheduled in SQL Agent. They are run as a windows Integrated Security. Note :A service account running SQL Server service and SQL Agent has sys admin right.

We, DBAs need to change the SSIS job. We will need to use a proxy account which is mapped to a credentital.

I received my information from Jason Strate's web site.

http://www.jasonstrate.com/2013/07/security-questions-logins-credentials-and-proxies/

The next question in the list is:

What is the relationship between logins, credentials, and proxies? And why they were introduced?

Before we get too deep, though, lets defined each of these objects. They are:

• Login: A login is any principal that is granted access to a SQL Server instance. The access can be granted to domain users, domain group, or SQL Server authenticated accounts.
• Credential: A credential provides a mechanism to store login information for a domain account within SQL Server. The credential can then be used to pass that authentication information from into another login or a proxy to grant it permissions to resources external to SQL Server.
• Proxy: A proxy is used by SQL Server Agent to map credentials to SQL Server Agent subsystems (i.e. PowerShell or SSIS).

Ok. In our case,

• Create a login which is a domain account and has a public role to server. It will have db_reader, db_writer, db_ddl rights to database level. (It depends on Data FLow)
• Create a credential which maps to the domain login
• Add the credential to the domain account in login
• Create a proxy which is mapped to the credential in SSIS Package Execution under SQL Agent

SQL Server Management Studio

 Create a Login

First, a domain account login needs to be created. Second, a credential is created using the domain login. Last, add the credential to the login.

 Give public role to the domain account login to SSISDB

Create a Credential

Create a Proxy

 Create Principals

 Msdb role principals are added to the proxy account.  

1. SQLAgentReaderRole
2. SQLAgentUserRole
3. SQLAgentOperatorRole

 

A new proxy account is created

SSIS Catalog Configuration

We gave a public role to the domain account login to connect msdb and SSISDB databases. In order to give access to the proxy account from SQL Agent to SSIS packages stored in SSIS Catalog, we will need to add permission on SSIS packages in SSIS Catalog. In order to do this, Click on a project folder in SSIS Catalog.

Give the folowing permission to the domain account.

• Read, Read Object, Execute Object

Creating a job and run a job as a proxy account

• Create a job in SQL Agent
• In steps, create a step and choose run as a proxy account.

Let the job ran, open a new query window and execute the following.

usessisdb

select  * from catalog.executions

In the query result, look at a column " executed_as_name" which shows the domain account used in credential. So we can confirm that the job is executed by a proxy account which is mapped to the domain account.

SSIS Catalog Reports Permission

SSIS Catalog reports permission

SSIS developers who do not have ssis_admin access are given db_datareader to SSISDB and public role to server. Still they are not able to view ssis execution reports in SSIS Catalog because ssis_Admin access is required to view them. The access right is limited in Catalog views by Microsoft.

 In order to fix the issues, I will need to alter the following views in SSIS catalog.

·         Catalog.event_messages

·         Catalog.executions

To alter catalog.event_messages and catalog.executions,  click on script view as > alter to > new query editor window.

Then , comment  out the following part of the script.

--comment it to give access to reports

--WHERE      opmsg.[operation_id] in (SELECT [id] FROM [internal].[current_user_readable_operations])

--           OR (IS_MEMBER('ssis_admin') = 1)

--           OR (IS_SRVROLEMEMBER('sysadmin') = 1)

After updating views, developer can view executions and drill down messages. Connect to server and you can be able to view reports.

AlwaysOn- PreferredBackup

Which one is a preferred backup replica?

If sys.fn_hadr_backup_is_preferred_replica( @dbname ) <> 1
BEGIN
-- If this is not the preferred replica, exit (probably without error).
END
-- If this is the preferred replica, continue to do the backup.

AlwaysOn Monitoring

Secondary Replica Health

0-NOT SYNCHRONIZING,1-partially healthy,2-healthy

select  synchronization_health secondaryreplicahealth
from  sys.dm_hadr_availability_replica_states
where  role_desc = 'secondary'

Primary Replica Health

--preferred secondary backup

declare @DBName as varchar(150)
declare @preferredReplica as int
SET  @DBName = (select top 1 name from sys.databases where database_id > 4)
SET  @PreferredReplica =(Select master.sys.fn_hadr_backup_is_preferred_replica(@DBName))

 --print @preferredReplica

IF
(@preferredReplica = 0 )
BEGIN
select
synchronization_health Primaryreplicahealth from sys.dm_hadr_availability_replica_states
where role_desc = 'primary'
END

 IF (@preferredReplica = 1 )
BEGIN
select
 99
END

Replica Custernode status
0 = Not joined
1 = Joined, standalone instance
2 = Joined, failover cluster instance

select
distinct  min(join_State) from
sys.dm_hadr_availability_replica_cluster_states
where  join_state_desc ='JOINED_STANDALONE'

--Listener Status

--IP resource ONLINE/OFFLINE state from the WSFC cluster, one of:

-- 0= Offline. IP resource is offline.

-- 1 = Failed. IP resource was being brought online but failed.

-- 2 = Online Pending. IP resource is offline but is being brought online.

-- 3 = Online. IP resource is online.

　

select  'state' = case 
when  state_desc = 'OFFLINE' then 0
when  state_desc = 'FAILED' then 1
when  state_desc = 'ONLINE PENDING' then 2
when  state_desc = 'ONLINE' then 3
end  from
sys.availability_group_listener_ip_addresses

 

Data Loss Time (RPO)

 

AlwaysOn CheckList

Windows Server 2012 and SQL Server 2012 AlwaysOn availability group

SQL Server 2012 AlwaysOn availability group is HOT!!!!
-Use Windows Server 2012
-DO NOT Use Windows Server 2008 R2 ( BAD BAD BAD Idea!)

 Before we start on availability groups, we need to prepare for the following checklist.

Ø  Windows Server Operating system version & edition

Ø  Processor

Ø  Memory

Ø  Data/Log/Temp/Backup drives Configuration for SQL Server

Ø  Central backup share for both primary and secondary replicas

Ø  Windows recommended hot fixes  For Windows 2012: http://support.microsoft.com/kb/2784261

Ø  Public static ip addresses for private network in primary and secondary replica

Ø  Private ip address for public network in primary and secondary replica

Ø  Virtual cluster name and virtual cluster ip address

Ø  Number of nodes on a cluster

Ø  Quorum model and CIF share configuration

Ø  SQL server Enterprise Edition

Ø  SQL Server Cu updates

Ø  Availability groups Names

Ø  Reserved availability group Listener names and listener static IP Addresses

Ø  Number of availability groups on primary replica

Ø  Number of databases on each availability group

Please read the following links before we start AlwaysOn.
Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQLServer)
http://msdn.microsoft.com/en-us/library/ff878487.aspx#RestrictionsAG

Note : AlwaysOn availability groups do not work with distributed transactions. (MSDTC)

Inside AlwaysOn

Monitor Performance for AlwaysOn Availability Groups

I am referring to http://technet.microsoft.com/en-us/library/dn135338.aspx
Availability groups in SQL server 2012 is a hot topic. Recently, I have set up and configured availability groups at my work. I have SQL server 2012 enterprise version on Windows 2012 Data Center Edition.

We will need to know how primary and secondary servers are communicating to each other in order to monitor performance of availability groups.


 Data Synchronization Process







1 . Log Flush

Log data is flushed to disk. This log must be replicated to the secondary replicas. The log records enter the send queue.


select * from sys.dm_os_performance_counters
where counter_name like '%log bytes Flushed/sec%' 

2. Log Capture

Logs for each database is captured and sent to the corresponding partner queue (one per database-replica pair). This capture process runs continuously as long as the availability replica is connected and data movement is not suspended for any reason, and the database-replica pair is shown to be either Synchronizing or Synchronized. If the capture process is not able to scan and enqueue the messages fast enough, the log send queue builds up.

select * from sys.dm_os_performance_counters where counter_name like '%bytes sent to Replica/sec%' 

3. Send

The messages in each database-replica queue is dequeued and sent across the wire to the respective secondary replica.

select  * from sys.dm_os_performance_counters where
counter_name like '%bytes sent to transport/sec%'


4. Received and Cached

Each secondary replica receives and caches the message.
select  * from sys.dm_os_performance_counters where
counter_name like '%log bytes received/sec%' 

5. Harden
Log is flushed on the secondary replica for hardening. After the log flush, an acknowledgement is sent back to the primary replica. Once the log is hardened, data loss is avoided. 


select  *  from sys.dm_os_wait_stats
where  wait_type = 'HADR_LOGCAPTURE_SYNC'
select  * from sys.dm_os_performance_counters
where  counter_name like '%log bytes Flushed/sec%'


6. Redo

Redo the flushed pages on the secondary replica. Pages are kept in the redo queue as they wait to be redone.


select  * from sys.dm_os_performance_counters
where  counter_name like '%redone bytes/sec%'

select  * from sys.dm_os_wait_stats  where  wait_type = 'REDO_THREAD_SYNC'


Flow Control Gates

AlwaysOn Availability Groups is designed with flow control gates on the primary replica to avoid excessive resource consumption, such as network and memory resources, on all availability replicas. These flow control gates do not affect the synchronization health state of the availability replicas, but they can affect the overall performance of your availability databases, including RPO.

how many times flow control was activated and how much time was spent waiting on flow control. Higher wait time on the flow control translate to higher RPO.


select  * from sys.dm_os_performance_counters  where  counter_name like '%Flow control%'






sp_MSforeachtable

Today, Grant Fritchey tweeted about sp_MSforeachtable . What will we see if we run the following statement on Azure database?

EXEC sp_msforeachtable 'select ''?'', count(*) from ?'

It retruns
Could not find stored procedure sp_msforeachtable.

It is deprecated on Azure. Then, I tried to run on local development database. It returns total record count in each table in database. It will be useful for a DBA in future. Next step, I looked up sp_Msforeachtable in Google. I found two more useful scripts.

Checking integrity of tables in database

USE AdventureWorks;
EXECUTE sp_MSforeachtable 'DBCC CHECKTABLE ([?])';

Checking space used of each table in database

USE AdventureWorks;
EXECUTE sp_MSforeachtable 'EXECUTE sp_spaceused [?];';


Checking space used of each database

declare @cmd varchar(500)
set @cmd='use [?];exec sp_spaceused '

exec sp_MSforeachdb @cmd

Powershell - Remote

Powershell to remote to computers to get SQL server versions

$($servers = Import-Csv c:\centers.csv
$errorlog = "C:\Error.txt"
foreach($server in $servers){
Try
{
$con = ("server=" + $server.Host_Name + "\sqlexpress;database=master;Integrated Security=sspi")
$cmd = "select serverproperty('servername') as Name,serverproperty('productversion') as Version,serverproperty('productlevel') ServicePack,serverproperty('edition') as Edition"
$da = New-Object System.Data.SqlClient.SqlDataAdapter($cmd,$con)
$dt = New-Object System.Data.DataTable
$da.fill($dt) | out-null
$dt
}
Catch
{
"Fail to remote to $server :$_" |add-Content $errorlog
}
})| Export-csv C:\SQLServerVersion.csv -NoTypeInformation

Daily DBA Cheatsheet

1. Creating linked server with local name

EXEC master.dbo.sp_addlinkedserver @server = N'mylocalname', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'servername'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'mylocalname,@useself=N'False',@locallogin=NULL,@rmtuser=N'dblink',@rmtpassword='########'

2. SQL Server Data Type Conversion Chart

http://www.microsoft.com/en-us/download/details.aspx?id=35834

3. Filtered Index

I want to create a unique key with multiple null. To fulfil this, I can create unique filtered index.

Create Unique NonClustered Index NonClustomer_Emailaddress on Customer
(emailaddress)where emailaddress is not null

4. sys.processes -  Find how many processes running on a instance

SELECT DB_NAME(dbid) as 'Database Name', COUNT(dbid) as 'Total Connections' FROM master.dbo.sysprocesses WITH (nolock)WHERE dbid > 0GROUP BY dbidSELECT
@@MAX_CONNECTIONS AS 'Max Allowed Connections'

5. Server Edition & Version

select
SERVERPROPERTY('Edition'),
SERVERPROPERTY('ProductLevel'),
SERVERPROPERTY('BUildClrVersion'),
SERVERPROPERTY('ProductVersion')


6. Row Count ( The following code is from http://www.sqlservercentral.com/articles/T-SQL/67624/)

-- Shows all user tables and row counts for the current database 
-- Remove is_ms_shipped = 0 check to include system objects 
-- i.index_id < 2 indicates clustered index (1) or hash table (0) 
SELECT o.name, 
 ddps.row_count 
FROM sys.indexes AS i 
 INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID 
 INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID 
 AND i.index_id = ddps.index_id 
WHERE i.index_id < 2 
 AND o.is_ms_shipped = 0 
ORDER BY o.NAME 

7. DBCC

The following DBCC, DMVs and sp commands help me.

dbcc inputbuffer(SPID)

dbcc sqlperf(logspace)

sp_who2

sp_configure

sp_spaceused

select * from sys.sysprocesses

SELECT file_id, name, physical_name, (size * 8 /1024.0) AS SizeMB FROM sys.database_files

select * from sys.masterfiles

msdb.dbo.sp_help_job

exec master.dbo.xp_sqlagent_enum_jobs 1,garbage 

SQL Server 2012 on Wins 2012

SQL Server 2012 standard edition on Windows Server 2012 Data Center

Today, I received a brand new virtual machine installed with Windows Server 2012 Data Center edition from network administrator.

Here is specs of the new machine:

Operating system : 64 - bit Windows server 2012 Data Center

Processor: 2 Dual core processors (4 virtuals CPUS, 2 Sockets)

Memory : 12 GB

Local Drive : 80 GB

Data Drive : 200 GB (RAID 5)

Log Drive : 100 GB (RAID 10)

I was surprised by windows server 2012 UI.Window Server 2012 looks like it was truly made for the computer illiterate. Sad!  To make my life easy on the box, I pinned all tools/apps I needed on Task bar.

First, I will need to install SQL Server 2012 standard edition in my new server.
Before installing SQL Server 2012, I installed .net 3.5 from add features via server manager. net.3.5 installation is very important step prior to SQL Server 2012 is installed. Because it will fail SQL Server installation.

Install .net 3.5 from add and remove features. We will need to have window 2012 installer to point for installation. After installing .net 3.5, run windows update. Then, restart the server.

The server came back up. I have maunted SQL Server 2012 iso and it is ready for me to install. So, I clicked on the setup.exe and I recived .net error.

An error occurred creating the configuration section handler for userSettings/Microsoft.SqlServer.Configuration.LandingPage.Properties.Settings: Could not load file or assembly 'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies.

Repairing the .NET 4.0 framework didn't solve the issue. Neither did removing all SQL stuff through 'Add or Remove Programs'. Looking into the error a bit further...
 The system cannot find the file specified. (C:\Documents and Settings\_USERNAME_\Local Settings\Application Data\Microsoft_Corporation\LandingPage.exe_StrongName_ryspccglaxmt4nhllj5z3thycltsvyyx\10.0.0.0\user.config line 5) ---> System.IO.FileNotFoundException: Could not load file or assembly 'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. The system cannot find the file specified.

So the problem probably wasn't that System.dll couldn't be found, but some user.config file for the landingpage executable.

The solution is as simple as it is radical: remove the entire (temporary) folder 'C:\Documents and Settings\_USERNAME_\Local Settings\Application Data\Microsoft_Corporation' (where USERNAME_ is, of course, the current username). After this the setup should start up without any problems.

After deleting Microsoft _Corporation folder, the error is fixed.

SQL Server 2012 installation continues...

I left reporting service at last since I want to make sure Database engine is installed successfully. After DB engine is done successfully, reporting service installation continues..

To test client connection, try to connect from your local machine to database server. I recived the error.

To fix this, enable TCP port 1433 in windows firewall.

SQL server 20012 SP1

Service pack 1 can be downloaded from microsoft web site.

http://www.microsoft.com/en-us/download/details.aspx?id=35575

So, I downloaded service pack1 from microsoft and applied it. Now, I will need to restart the server. Go to settings, and click on power button to restart the server.

The server came back up. To check the database is applied service pack1 , I run the following script.

Next, I want to make sure all the services are running on service account  and automatic start up. I added the service account in administrator group.

Last, I am ready to migrate my database from development to this brand new server. I would like to thank to network administrator to rebuild this machine with windows server 2012 data center edition.