SQL Server 2017 on Linux

I have been a DBA on IBM UDB DB2 longer than on SQL Server. So with great pleasure I would like to announce that today I installed SQL Server 2017 on Linux.

I am going to finally able to utilize my Linux skills to administer SQL Server.

I will be writing more blogs about my experience with SQL Server on Linux.

If you guys want to install SQL on Linux then this is a good starting point.

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup

I installed Ubuntu 16.04 on a virtual machine. It took me a couple of attempts before I could figure out what I needed. Hint: after installing Ubuntu, I chose to install 'Ubuntu Desktop'.

Then installed Open-SSH and started the SSH service. Later I installed Putty on my Windows host and using the IP address of the Linux machine I SSHed into it.

This way I would have not to fool around with the virtual machine interface and just work in Putty. For those of you who don't work with Putty, I highly recommend getting used to that because unlike Windows, in Linux environment you will be working via a SSH client like Putty. You will never have a desktop environment for Linux and certainly no RDP.

But the good thing is that once you the server running you can use SSMS or sqlcmd from any other machine to work with SQL Server on Linux.

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication
SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon attempt failed


This error has troubled me so many times. Each time I have to modify my connection string to use LOCALHOST or the port number to resolved but today I finally found a permanent fix.
Thanks to Pinal Dave who wrote this blog post.

https://blog.sqlauthority.com/2017/04/18/sql-server-login-failed-login-untrusted-domain-cannot-used-windows-authentication/

As you know I write this blog for my own documentation so here the fix as per Pinal's instructions.

• Edit the registry using regedit. (Start –> Run > Regedit )
• Navigate to: HKLM\System\CurrentControlSet\Control\LSA
• Add a DWORD value called “DisableLoopbackCheck”
• Set this value to 1

SQL Server 2012/2014: Extended Events to audit statements

Recently I had a request to set up audit for a SQL Server database to capture any DDL statements and any updates to specify tables. For this I could not run a profiler trace all the time so upon some investigation, I decided to use Extended Events to capture these events.

CREATE EVENT SESSION [DDL_Changes] ON SERVER 
ADD EVENT sqlserver.sp_statement_completed(
    ACTION(package0.event_sequence,sqlos.task_time,sqlserver.client_app_name,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_principal_sid,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.username)
    WHERE ((([sqlserver].[username]=N'Theusername') AND ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%ALTER TABLE%'))) AND ([sqlserver].[database_name]=N'YourDBName'))) 
ADD TARGET package0.event_file(SET filename=N'H:\MSSQL$MSSQLSERVER\AuditLogs\DDL_Changes.xel',max_file_size=(100),max_rollover_files=(10))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

The above CREATE statement is to capture any ALTER TABLE commands that are executed against the database. You can explore to check how you can capture multiple types of SQL_TEXT events.

I would open the Extended Event under 'Management' in SSMS and edit the properties on this event.
You can then right click on the event and start it, to capture all the events.

To can read from the Extended event using the following commands
1) First read the xel file and write the contents to a temp table

IF OBJECT_ID('tempdb..#ExEvent') IS NOT NULL DROP TABLE #ExEvent

SELECT IDENTITY(INT,1,1) AS RowId, object_name AS event_name, CONVERT(XML,event_data) AS event_data
    INTO #ExEvent
FROM sys.fn_xe_file_target_read_file(N'H:\MSSQL$MSSQLSERVER\AuditLogs\DDL_Changes.xel', null, null, null);

2) Then read from this temporary table.

SELECT  RowId, event_name, [sql_text], [username], [database_name], [transaction_id],  [task_time]                  
    FROM (
            SELECT RowId
                    , event_name
                    , T2.Loc.query('.').value('(/action/@name)[1]', 'varchar(max)')AS att_name
                    , T2.Loc.query('.').value('(/action/value)[1]', 'varchar(max)')AS att_value
            FROM   #ExEvent
            CROSS APPLY event_data.nodes('/event/action') as T2(Loc)
            WHERE T2.Loc.query('.').value('(/action/@name)[1]', 'varchar(max)')
                IN ('sql_text', 'username', 'database_name', 'transaction_id',  'task_time')

        ) AS SourceTable
            PIVOT(
                MAX(att_value)
                FOR att_name IN ([sql_text], [username], [database_name], [transaction_id],  [task_time])

        ) AS PivotTable 
order by  [task_time]  desc


You can open the properties of the Event and then play with it to change some of these settings. The most important to remember how you read from the file.

PowerShell script to stop and Start SQL Server Services

As most of you know PowerShell is an amazing tool to perform a lot of Database Administration tasks.
Since last year I have been trying to create PS scripts to make my life easy. One of the tasks that I perform during our annual DR testing is stopping and starting SQL Server Services.
Before you had to login to the DB server, open the SQL Server configuration manager, change the Service Startup method (AUTO or MANUAL) and then stop/start the service. If you had SSRS, SSAS, SSIS or multiple instances then this could take time. Not to forget only the DBAs would do this.

Just like all other DBAs I am pretty lazy. That is why I wanted all my tasks automated so I can work on new features and upgrade my environment. So I wrote this script to Start SQL Server Services. You will have to modify this if you want a script to STOP the services. Also, edit the Get-Service command to display the servicenames in Descending order if you are stopping the services. This is to ensure that you stop the Agent before the engine.

Note: I found a script to change the account that runs the SQL Server services from the following link. I have modifed that script to create this one.
http://blogs.msdn.com/b/amantaras/archive/2014/12/10/powershell-script-to-change-windows-service-credentials.aspx



#This script requires the hostname as the input.
[CmdletBinding()]
Param( [Parameter(Mandatory=$True,Position=1)][string]$Server )

function PowerShell-Wait($seconds)
{
#This function will cause the script to wait n seconds
   [System.Threading.Thread]::Sleep($seconds*4000)
}

$host.PrivateData.VerboseBackgroundColor = "DarkMagenta"

$host.PrivateData.WarningBackgroundColor = "DarkMagenta"

$services=Get-Service -ComputerName $Server -Displayname "SQL*" | Sort-Object -Property Name
write-host "----------------------------------------------------------------"  
write-host "REMEMBER TO RUN THE SCRIPT AS ADMINISTRATOR"  -foregroundcolor "RED" -backgroundcolor "yellow"

write-host "`n Status of SQL Services" -foregroundcolor "green"
Get-Service -ComputerName $Server -Displayname "SQL*" | Sort-Object -Property Name
write-host "`n Services found:"  $services.Count 
Foreach ($servicename in $services)

{
if ($servicename.Name -eq "SQLWriter" -or $servicename.Displayname -eq "SQL Active Directory Helper Service") 
{ 
write-host "Skipping service : " $servicename.Displayname -foregroundcolor "RED"
Continue 
}


$startmode = gwmi win32_service -computername $Server | where {$_.Displayname -like $servicename.Displayname} | select StartMode
if ( $startmode.startmode -eq "Disabled")
{
write-host "Service "$servicename.Displayname " is DISABLED so skipping service." -foregroundcolor "RED"
Continue
}


Write-Host "Do you want to Start the service "$servicename.Displayname" on "$Server -foregroundcolor "Yellow" -backgroundcolor "Blue"
$choice = Read-Host -Prompt '[Y/N] :'
if ($choice -eq "Y" -or $choice -eq "YES" -or $choice -eq "Yes" -or $choice -eq "yes" ) 
{
write-host "Starting service: "   $servicename.name -foregroundcolor "green"
write-host "    Attempting to Start de service..." -foregroundcolor "green"
Set-Service -InputObject $servicename -startuptype "Automatic" -Verbose 
Start-Service -InputObject $servicename -Verbose 
PowerShell-Wait (1)
}
Else
{
write-host "Skipping service : " $servicename.Displayname -foregroundcolor "RED"
}
}


write-host "`n Status of SQL Services" -foregroundcolor "green"
Get-Service -ComputerName $Server -Displayname "SQL*" | Sort-Object -Property Name | Format-Table -Property Status, Name , DisplayName -auto  | Out-String

write-host "PROCESS COMPLETED"  -foregroundcolor "RED" -backgroundcolor "yellow"

Error 2601 Severity 14 State 6 Server (SYBASE) Attempt to insert duplicate key row in object 'sysusages' with unique index 'csysusages'

Number (2601) Severity (14) State (6) Server (SYBASE) Attempt to insert duplicate key row in object 'sysusages' with unique index 'csysusages'

As you might notice lately I have been writing a few blogposts related to Sybase errors. Having worked as a DB2 DBA for a long time, I do have good expertise on Linux/UNIX servers. So in my current environment I am the Sybase DBA because the database features is very close to SQL Server.

Anyways, for the last two weeks I have been building a new Sybase server and hence all these errors and posts about them.

Today I was trying to add a new device to the database and alter the database. But I got the above error.
Along with that when I try to find the database size using a query against sysdatabases and sysusages, I get the following error.

And I could not extend the existing database devices. After a lot of troubleshooting I executed the following and fixed my database device issue.

sp_dbremap dbname

Sybase : Volume validation error: bad magic number EOF1, expected USTH.

Oct 11 22:36:58 2017: Backup Server: 6.35.2.2: Volume validation error: bad magic number EOF1, expected USTH.

Oct 11 22:36:58 2017: Backup Server: 6.32.2.3: compress::/sybase/dump/Database.20171011.14.DB::013: volume not valid or not requested (server: , session id: 72.)

Currently, I am working on migrating a Sybase server to a new host and I installed a higher version of Sybase on that host. When I tried to LOAD the Database DUMP from the current server I got the above error.

All the help that I got online pointed towards the cause being that the version where I took the backup was at a higher level than where I was loading to. But that was not the case.

Then I started working on a uncompressed dump file that seemed to do the trick. However, the production database was so large that I didn't have enough disk space to take an uncompressed dump file.
So next I tried to use the native option for compression in the LOAD command

dump database mydb "/sybase/dump/database.dmp"
with compression = "9"

This seemed to do the trick. I had actually dumped to multiple stripes so I had to modify the above command.

To test this further I tried it without the compression option that too seemed to work.
So try this too

dump database mydb "/sybase/dump/database.dmp"

Sybase: Change SA password

Sometimes, when DBAs don't follow proper documentation they end up in situations where they don't know the SA password. This has to be the most fatal mistake for a DBA. Fortunately, in Sybase there is a way to change SA password if you don't have it.

1.      Stop the Sybase Server.

2.      Locate the RUN_SPS_XXXXXX file. It is typically located in the $SYBASE/ ASE-15_0/INSTALL/ directory.

3.      Open the RUN_SPS_XXXXXX file.

4.      Add the following command line option to the end of the command line  “ -psa”

Note: This option is case sensitive, should be lowercase, and entered without quotes.
New SSO password for sa:XXXXXXXXXXXXXX
Note: You may have to scroll up to find this line.

5.      Run startserver -f RUN_SPS_XXXXXX to start the Sybase Server. Startup messages you would normally see in the errorlog will be printed to the screen.

6.      After a few minutes, when the startup messages have stopped printing to the screen, look for a line similar to the following within the startup messages:

7.      Attempt to log into the Sybase Server using Sybase Central or SQL Advantage with the new password. You should be able to login without any errors. If not, please contact the SPS Help Desk.

8.      Once you are logged in, update the password for the sa account.

9.      Logout of Sybase Central or SQL Advantage.

10.    Shutdown the Sybase Server.

11.    Edit the RUN_SPS_XXXXXX again to remove the -psa option then save it.

12.    Restart the Sybase Server.