Windows Powershell: Useful commands for the SQL DBA

What is Powershell?
The tool is available on Windows Servers by default and is using to scripting purposes as well as many other functionalities such as Automating Windows / SQL Server functions.

How to Access:
Powershell can be accessed through this icon on desktop area Pshell1 or  through executable file on the root drive (2.0 is the version here, there are versions 1.0,2.0 and 3.0). –  %SystemRoot%\system32\WindowsPowerShell\v2.0\powershell.exe

Useful Commands for SQL Server DBA

For a database administrator is very useful to have an utility to run remote commands to query data from multiple servers across the same domain. This can be done also using native DOS prompt (SQLCMD), but powershell has some interesting features as well and allows more customization. Here is a way to deploy health check scripts to route the commands to any other Server on the same domain.

1. Make sure the environment has RDP connection to other servers (such as Production to Dev/Test and back/forth).
2. Save your script on any location. Example C:\Scripts\SQL\Health_check.sql
3. On this same directory, create a new empty text file and enter this piece of code. The first 2 lines are to make Powershell able to use SQLCMD cmdlets, otherwise the string fails to start.
Then, $sql is a variable which receive user input (the SQL Instance name)  and once this data is given, the invoke-sqlcmd will execute the script.

Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue
Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinue
$sql = Read-Host ‘MSSQL Job Failure Check: Enter SQL Instance Name’
invoke-sqlcmd -inputfile “F:\MSSQL\jobfailure.sql” -serverinstance $sql -Verbose

Save the file with .ps1 extension so Powershell can recognize it as a Script. This is the result (example using sqlhc.ps1 file)

Pshell2

Here we input the SQL Instance name for a quick Instance health check.
Note:  This may different from each client environment: If the single instance name does not resolve, try using with full qualified domain name + port or VIP Address + Port or Cluster Group name + Virtual Server Name.

Result:

Pshell3.jpg

The same script can be adapted to hold another variable. Very useful when you want to run scripts agains a specific SQL Server database. Example below is for a User Database Space Check script.  The $sql variable is the input for SQL instance name, and $db the Database name.

Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue
Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinue
$sql = Read-Host ‘MSSQL Database Files Check: Enter SQL Instance Name’
$db = Read-Host ‘MSSQL Database Files Check: Enter Database Name’
invoke-sqlcmd -inputfile “F:\MSSQL\dbspace.sql” -serverinstance $sql -database $db  -Verbose

Pshell4

Power_prompt_space.jpg

With Powershell we can also manipulate the Services Console and Event Viewer:

Querying a remote server for running SQL Instances (any status)
get-service MSSQL*,SQLAGENT*  -computername <servername>  | Format-Table -AutoSize -Wrap

Pshell5

Querying a remote server for running SQL Instances (stopped or started status)

get-service MSSQL*,SQLAGENT*  -computername <servername>  | where-object {$_.Status -eq “Running”}  | Format-Table -AutoSize -Wrap
get-service MSSQL*,SQLAGENT*  -computername <servername>  | where-object {$_.Status -eq “Stopped”}  | Format-Table -AutoSize -Wrap

Querying a list of Servers for running SQL Instances:
notepad comp.txt
get-service MSSQL*,SQLAGENT* -computername (gc .\servers.txt) | where-object {$_.Status -eq “Running”}  | Format-Table -AutoSize -Wrap

Pshell6.jpg

Retrieving Event Viewer Logs data from remote servers:

In the -logname parameter, change to System, Application or Security. You can also modify the amount of data to be displayed on screen.
get-eventlog -logname System -entrytype Error -newest 20 -computername <insert_server_here> | Format-Table -AutoSize -Wrap

Finding 10 recent reboot commands on a computer
get-eventlog -logname System -source User32 -newest 10 -computername <insert_server_here> | Format-Table -AutoSize -Wrap

Advertisements

SQL Server – How to view the link between the Transaction Log x Data Page

One of the first things that a DBA learn is that on SQL Servers, the data that is displayed in the Tables are in fact internally stored in the Data Pages. Data Pages are the smallest storage unit and have 8KB max. Using the below script, we are able to actually see a transaction doing an insert and that data being produced directly in the data page.

Check it out:

 

Step 1 . Create a Test database and a user table.

USE [master]
GO

CREATE DATABASE PageTest
GO

USE [PageTest]
GO

CREATE TABLE Names
    (fname varchar(10)
    ,lname varchar(10)
      )
GO

Step 2. Change the recovery model to “Simple” and issue a manual Checkpoint – then insert sample data. The reason for the simple recovery model is because it generates less data in the transaction log , and by entering the manual checkpoint it forces SQL Server to synchronize the current records in the T-log to the data file, so there will be less rows to show in the fn_dblog undocumented function.

ALTER DATABASE [PageTest] SET RECOVERY SIMPLE WITH NO_WAIT
GO

CHECKPOINT
GO

USE [PageTest]
GO

INSERT INTO NamesVALUES (‘Walter’,’White’)
GO

Step 3.  Now, we are inspecting the function fn_dblog that keeps actual data from recent data changes. In this case we are looking for footprints on the INSERT statement we just did.And since it was entered after the checkpoint, it should be there.

SELECT a.operation
        ,a.[Current LSN]
        ,a.[Minimum LSN]
        ,a.[Format LSN]
        ,a.[Oldest Active LSN]
        ,a.[Page ID] FROM fn_dblog(NULL,NULL) a
        WHERE operation LIKE ‘%INSERT%’

The result should be similar to this (values may vary according to your environment and columns length).

lsn_1

Step 4.  Enter the following string to browse the data page information

DBCC TRACEON(3604)
DBCC IND (N’PageTest’,N’Names’,-1)

 

Output should be similar to this:

lsn2

Pick up the number of the PagePID for the row where PageType = 1 (Data Page)

Now, enter this DBCC to browse the actual data page content:

DBCC PAGE (N’PageTest’,1,296,1)

You will then see a lot of data that is not in a readable fashion. What we are looking for, is the m_lsn

lsn3

36:121:29

Convert this value from Decimal to Hexadecimal:

36 = 24
121 = 79
29 = 1D

Sounds familiar? Yes – this was our LSN found on step 3

00000024:00000079:001d

The m_lsn is the Log Sequence Number of the Last log record that  changed the data page. The INSERT statement from step 2.

If we scroll down a little bit, and look at the “Primary Record” information, Walter White will be there too:

lsn4

You can read more about Data Pages and Log Records on Paul Randal’s blog:

http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-page/

http://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/

SQL Server Overview Training

This SQL Server Overview Training was created by me originally to help IBM professionals from another support areas, such as SAP, Production Control and Storage, to have a good first look at the SQL Server fundamentals and the day to day tasks of a Database Administrator.  Now, I have updated the content and published on YouTube so it can be helpful for the DBA community worldwide. If you have no experience with SQL Server or are a professional that works with another technology in the RDBMS world, and are seeking for fundamentals on SQL Server, this material might be useful for you. I hope you enjoy the content! Send me any feedback or questions you have.

You can also view the Playlist and browse the Training by each Module according to your interest.

SQL Server Overview Training Space

Best Practices for SQL Server Decommission

In this post I would like to share some Best Practices about SQL Server Decommission:

1. Be aware of your Client IT Policy

The process of uninstalling / removing permanently a SQL Server Instance(s) goes beyond the technical scope. The stakeholders must be aware of what will happen and how their business will be impacted. Depending on the client’s IT policy, even minor changes on test envrionments must have a clear documentation and technical timeline of the events. This is usually done through a Change Management Tool. Some other factors to consider are:

  • Extra caution is required when removing a single SQL Server Instance on a Cluster environment with another Instances in production that will not be part of the decommission;
  • Make sure to understand if the Client is willing to decommission only the SQL Server product or the whole Windows Server infrastructure, and if another teams are involved;
  • Understand the big picture of why the SQL Server is being removed. Despite it may look obvious ( nowadays most of the times it is cost-saving related), does the client know for sure what he is doing? As the DBA, part of your duties should be a trusted advisor for the client in case you notice they are doing something wrong.

Example: You identify the client need only to remove a single user database instead of uninstalling everything (Consider the non-technical business users) because they do not the difference between a SQL Server instance name, User database name, or even DNS name which they use to connect remotely.

2. Backups

This is a common quote from many SQL Server books: “If you don’t have backups, then you do not have a database at all”. This applies to Decommission scenarios as well.  Chances of the client requesting back the disposed databases might be minimal. Let’s imagine they are of 1% only.  Can you think of the consequences of telling the client their MSSQL is gone for good and and can no longer be restored due to lack of backups? This could cost your Job.

Ensure they are complete before uninstalling MSSQL. If for any reason you are unable to guarantee 100% of backups, it is better to suspend than to move forward to avoid pressure from above. Having the client to pay for some more time for that server running (assuming the decommission is cost-saving related) is preferable over the harsh task of bringing bad news later.

3. Collect useful data

Another tip is to collect useful data that can be helpful in the event of a rebuild. This type of information is commonly used on the client technical documentations (inventory), but it is worth to run some T-SQL scripts to capture actual information from the server prior the change. Such as: Configuration data (Data and Transaction Log locations, Service Pack /Cumulative Update level) are just a few examples.

4. Always use the standard removal method

Microsoft has specific procedures about uninstalling SQL Server Instances from versions 2000 to 2012, either through Standard setup (Add/Remove Programs) and manual ones, where you may need to deal with the Registry Hive. Be careful with this second option and always back up your registry prior changing anything, as it can damage the whole Operating System if something goes wrong there. If the standard removal setup is failing, consider reviewing the Setup Bootstrap Logs and identify what is causing the error and troubleshoot from there on.

These are 4 simple tips. Feel free to add your own comments or any questions you may have about this subject.

Best Regards!

Cluster Quorum Registries pointing to the wrong SQL Server Disk

This situation happened in a Windows Server 2003 SP2 environment on a Clustered Microsoft SQL Server 2005 SP4 instance.

First off, this article helps to understand a little bit more about what is the Quorum resource on a Failover Clustering environment and it’s primary functions.

http://msdn.microsoft.com/en-us/library/windows/desktop/aa371819%28v=vs.85%29.aspx

The SQL Server Instance was not starting and the following error was recorded at the windows application log:

Could not open error log file ‘K:\i2_SystemDb\Microsoft SQL Server\MSSQL.2\MSSQL\LOG\ERRORLOG’. Operating system error = 32(The process cannot access the file because it is being used by another process.).

However, it was normally starting through the Services console (services.msc) from outside the Cluster Administrator. This was a strong indicator that something was definetly wrong with some of the cluster settings.

Since the ERRORLOG file was in use by another process, it was obviously another SQL instance on the cluster. In this case, it was the named one. From SQL Server Configuration Manager it was determined that both  existing instances, the default (MSSQLSERVER) and the named, were pointing to the exact same data drive and files.

Image

-dR:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf;-eR:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lR:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mastlog.ldf

-d (Master data file path), -e (ERRORLOG file path), -l (Master log data file path)

The registries also showed this same setting:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\Parameters

Image

But, for this scenario, changing the registry directly on this above screen would not work, as the Cluster configurations for the SQL paths resides also in another registry – for the Quorum disk.

This KB article from Microsoft has the solution. The method 1 was used.

http://support.microsoft.com/kb/953504

Image

Image

Note that there is one GUID(unique identifier)  entry for each Cluster resource, such as SQL Fulltext, Volume manager,DTC,Network name, to mention some.

Image

The 000000004 is the checkpoint file name. With these details in hand, the KB steps 5 to 9 (for method 1) must be followed so you can update the registries accordingly (SQLArg keys) with the correct drives path and SQL will be able to start with success.

How can I track what could have caused this?

To reach out the reason when this situation occurs, your starting point should be looking at recent changes to all related resources from the Cluster environment, specially to drives and quorum configuration.

Thank you for viewing this post. Should you have any concern, question or suggestions, feel free to comment.