About Me - Who is DR DBA?

Hello. My name is Rudy Panigas and I am a Production Senior Microsoft SQL Server Database Administrator (DBA) with over 14 years of experience. Have published articles with SQL Central, am a leader for the my local PASS chapter in Toronto (TORPASS) and love to automate anything SQL including disaster recovery. I created this blog to share my views, knowledge and to discuss Microsoft SQL server in order to help out others in this field. Please test these scripts before using. I do not warranty and do not take any responsibility for it, its misuse or output it creates. You can reach me at this email address: sqlsurgeon@outlook.com

Monday, February 23, 2015

Quick SQL Server Configuration Summary Report

Hello everyone,


I have written this script to detect and display a quick summary of your SQL Server installation/environment. 

Download this script here Detail Configuration Script

To view the report, open the script in SSMS and execute.There are temp tables created and dropped at end of execution.

Here is a list of information produced in the reported.

Display SQL Server name\Instance name
Display Installation Date
Display Machine Name         
Display Instance Name
Display Edition and BIT Level
Display Production Service Pack Level         
Display Production Name            
Display Production Version
Display Logical CPU Count
Display Maximum Memory (Megabytes)
Display Minimum Memory (Megabytes)
Display IP Address
Display Port Number
Display Default Domain Name
Display Service Account name
Display Clustered Status
Display Kerberos
Display Security Mode
Display Audit Level
Display User Mode
Display Collation Type
Display SQL Server Errorlog Location
Display SQL Server Default Trace Location
Display Number of Link Servers
Display SysAdmin Members
Display ServerAdmin Members
Display Configuration setting
Display code that automatically executes on startup
Display SQL Service Status
Display Location of Database files
Display Link Servers
Display Database Collation type
Display Database Hard Drive Space Available
Display Database Information
Display Database Backup Information
Display SQL Job Status
Display SQL Mail Information
Display Database Mirroring Status
Display Database Log Shipping Status
Display Report Server (SSRS) Reports Information

Please execute on your test/development environment and verify results. 
I will be adding more to this script in the future so watch for updates. 

Please let me know you comments.

Thanks,

Rudy 

Tuesday, January 13, 2015

Auto Generating of Service Principal Name (SPN) Commands

Hello All,

Have you had to use Kerberos authentication and needed to create the commands for the Service Principal Name (SPN)? Then the script in this blog will help. Execute the script on the SQL Server with SQL Server Reporting Services (SSRS) and it will automatically generate the commands needed. 

If you need more details on Kerberos have a look at Rob Carrol blog. It is very good and covers how to enable Kerberos authentication for reporting services. Here is his link.
http://blogs.technet.com/b/rob/archive/2011/11/23/enabling-kerberos-authentication-for-reporting-services.aspx

Now on to my script.

First, load this script on to your server (please verify on test servers) and execute. It only creates temp tables, does not write anything to SQL Server and the temp tables are deleted once executed.

The script will automatically detects the SQL Server settings

******** Settings that have been automatically detected **************

Result for detection of SQL Server name --> SrvName
Result for detection of Service Account name -->MyDomain\svcAccount
Result for detection of Port Number --> 1433
Result for detection of default Domain Name --> MyDomain
Result for detection of cluster --> SQL Server is not clustered
Result for detection of Kerberos --> TCP is using Kerberos
Result for detection of IP Address --> 192.168.54.1


Next, the script will take the information found and create the SETSPN commands 


cls
echo '*** Automatic SQL Server Service Principal Name (SPN) ***'
echo ' '
echo 'Setting SQL Server Database SPN...please wait'
echo ' '
  
SetSPN -s "MSSQLSvc/SQLServerName:1433" "MyDomain\svcAccount"
SetSPN -s "MSSQLSvc/SrvName.MyDomain.com:1433" "MyDomain\svcAccount"


echo ' '
echo 'Setting SQL Server Reporting Services SPN...please wait'
echo ' '

SetSPN -s "http/SrvName" "MyDomain\svcAccount"
SetSPN -s "http/SrvName.MyDomain.com" "MyDomain\svcAccount"


echo ' '
echo 'Setting SQL Server Analysis Services SPN...please wait'
echo ' '

SetSPN -s "msolapsvc.3/SrvName" "MyDomain\svcAccount"
SetSPN -s "msolapsvc.3/SrvName.MyDomain.com" "MyDomain\svcAccount"

echo ' '
SetSPN -l MyDomain\svcAccount"
echo ' '

           ==> Auto SPN generation is now complete <==

Finally, copy the SetSPN statements and execute then with a Domain Admin account.  Verify that the account "MyDomain\svcAccount" has the following check on "Trust this user for delegation to any service [Kerberos only]

Note that once the SetSPN command is executed it may take several hours (once mine took approx. 8) before it takes effect.

Here is the link for my script
https://drive.google.com/file/d/0B07PMQYOhF2DMEJMcnpGVDg3eFE/view?usp=sharing

Thanks,

Rudy