MySQL – How to Generate Random Number

In MySQL, UUID() function returns Universal Unique Identifier that generates 36 characters long value which is 5 part hexadecimal numbers. If you want to create random password, you can make use of this function.

SELECT UUID() ;

Returns the string 85aeb064-8f73-11e5-85ef-02fcc4101968 (Note that this is random, when you execute you will get different value). As the total length is 36, you can make use of the result to get a random password with any length.

Suppose you want to generate 8 character length, you can generate it using

SELECT LEFT(UUID(),8) random_password ;

If you want to generate 10 character length, you can generate it using

SELECT RIGHT(UUID(),10) random_password ;

This way you can generate a random password. As UUID () returns the hexadecimal number with five parts separated by hyphen, your word will have numbers, alphabets and hyphen depends on the length you use.

There can be many ways of doing this, but this is the simplest one.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #056 – How to fix Installation Failure – Rule “Setup account privileges” Failed in SQL Server

Sometimes in the interviews I observe that users are able to answer all the theoretical questions correct but when it is about doing practical they fail. A while ago, when I was helping a large organization with interview, suddenly their DBA reported that one of their server has installation error. We found this as a great opportunity to test a new candidate and gave him problem to solve. He was successfully able to solve the problem.

Question: How will you fix the installation failure error setup account privileges error in SQL Server?

setup rule 01 Interview Question of the Week #056   How to fix Installation Failure – Rule Setup account privileges Failed in SQL Server

Answer:

When I clicked on “failed” hyperlink, here is the message.

setup rule 02 Interview Question of the Week #056   How to fix Installation Failure – Rule Setup account privileges Failed in SQL Server

Here is the text of the message.

—————————
Rule Check Result
—————————
Rule “Setup account privileges” failed.
The account that is running SQL Server Setup does not have one or all of the following rights: the right to back up files and directories, the right to manage auditing and the security log and the right to debug programs. To continue, use an account with both of these rights. For more information, see http://msdn.microsoft.com/en-us/library/ms813696.aspx, http://msdn.microsoft.com/en-us/library/ms813959.aspx and http://msdn.microsoft.com/en-us/library/ms813847.aspx.
—————————
OK
—————————

I always look at setup logs to see the exact issue. I opened “SystemConfigurationCheck_Report.htm” and below is the failure.

setup rule 03 Interview Question of the Week #056   How to fix Installation Failure – Rule Setup account privileges Failed in SQL Server

Then I looked into “Detail.txt” and searched for “HasSecurityBackupAndDebugPrivilegesCheck” as shown in above screenshot. Failure is listed below.

(09) 2016-01-28 19:58:11 Slp: Initializing rule      : Setup account privileges
(09) 2016-01-28 19:58:11 Slp: Rule is will be executed  : True
(09) 2016-01-28 19:58:11 Slp: Init rule target object: Microsoft.SqlServer.Configuration.SetupExtension.FacetPrivilegeCheck
(09) 2016-01-28 19:58:11 Slp: Rule ‘HasSecurityBackupAndDebugPrivilegesCheck’ Result: Running process has SeSecurity privilege, has SeBackup privilege and does not have SeDebug privilege.
(09) 2016-01-28 19:58:11 Slp: Evaluating rule        : HasSecurityBackupAndDebugPrivilegesCheck
(09) 2016-01-28 19:58:11 Slp: Rule running on machine: PINALVM1
(09) 2016-01-28 19:58:11 Slp: Rule evaluation done   : Failed

As highlighted above, the account which was running setup was missing second one.

SeSecurity: Manage auditing and the security log

SeDebug: Debug Programs

SeBackup: Back up files and directories

To see the “Local Security Policy”, we can go to Start > Run > SecPol.msc or open it from Administrative Tools.

setup rule 04 Interview Question of the Week #056   How to fix Installation Failure – Rule Setup account privileges Failed in SQL Server

“Debug programs” doesn’t have any user. By default, “Administrators” group is part of it which was removed by me during some hardening. Once I added default account, I have to restart the computer to get forward from that error.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Script: Finding Cumulative IO Per Database File

Sharing SQL Server related scripts is something I tend to do from time to time. At the SQLPass one of the attendees for my session around 42 tips asked, if I have any script that is handy to find the IO utilization per database file because they had couple of scenario’s where such information was useful. I got curious to know what are those scenarios. During the break while on the line I asked, and got the response.

“Pinal, I actually work for a financial institute and we have large SQL Server installations. For the question I asked, I thought you might have something handy that I can take a look at. There are actually 2 scenario’s that get enabled here:

  1. First is a classic place where I have an SQL Server where I have consolidated a number of databases and want to make sure the experience of users accessing different databases (aka different applications) is not compromised because I can go ahead and distribute IO hungry files on different files.
  2. The second scenario is wherein I have done heavy partitioning on my database and now I have far too many files. Though the thought process remains the same as 1st I want the IO stats per file so that I can again look at distributing them around.”

Trust me, this made complete sense and I came back to this blog to check if I had something similar for this requirement. Well, then I thought I must write something simple to get this sorted. Here is a simple script that I made and feel free to modify the same for your requirements:

SELECT f.database_id, DB_NAME(f.database_id) AS database_name, f.name AS logical_file_name, f.[file_id], f.type_desc,
  
CAST (CASE
      
-- Handle UNC paths (e.g. '\\fileserver\mydbs\sqlauthority_dw.ndf')
      
WHEN LEFT (LTRIM (f.physical_name), 2) = '\\'
          
THEN LEFT (LTRIM (f.physical_name),CHARINDEX('\',LTRIM(f.physical_name),CHARINDEX('\',LTRIM(f.physical_name), 3) + 1) - 1)
          
-- Handle local paths (e.g. 'C:\Program Files\...\master.mdf')
          
WHEN CHARINDEX('\', LTRIM(f.physical_name), 3) > 0
          
THEN UPPER(LEFT(LTRIM(f.physical_name), CHARINDEX ('\', LTRIM(f.physical_name), 3) - 1))
      
ELSE f.physical_name
  
END AS NVARCHAR(255)) AS logical_disk,
  
fs.size_on_disk_bytes/1024/1024 AS size_on_disk_Mbytes,
  
fs.num_of_reads, fs.num_of_writes,
  
fs.num_of_bytes_read/1024/1024 AS num_of_Mbytes_read,
  
fs.num_of_bytes_written/1024/1024 AS num_of_Mbytes_written,
  
fs.io_stall/1000/60 AS io_stall_min,
  
fs.io_stall_read_ms/1000/60 AS io_stall_read_min,
  
fs.io_stall_write_ms/1000/60 AS io_stall_write_min,
   ((
fs.io_stall_read_ms/1000/60)*100)/(CASE WHEN fs.io_stall/1000/60 = 0 THEN 1 ELSE fs.io_stall/1000/60 END) AS io_stall_read_pct,
   ((
fs.io_stall_write_ms/1000/60)*100)/(CASE WHEN fs.io_stall/1000/60 = 0 THEN 1 ELSE fs.io_stall/1000/60 END) AS io_stall_write_pct,
  
ABS((sample_ms/1000)/60/60) AS 'sample_Hours',
   ((
fs.io_stall/1000/60)*100)/(ABS((sample_ms/1000)/60))AS 'io_stall_pct_of_overall_sample' -- Number of milliseconds since the machine was started.
FROM sys.dm_io_virtual_file_stats (DEFAULT, DEFAULT) AS fs
INNER JOIN sys.master_files AS f ON fs.database_id = f.database_id AND fs.[file_id] = f.[file_id]
ORDER BY 18 DESC
GO

The sample output (partially shown) is as:

 SQL SERVER   Script: Finding Cumulative IO Per Database File

Have you had similar requirements and have you used scripts similar to these before? I think necessity is the mother of all scripts in the SQL Server world. Hope to see some of your scripts via the comments section.

Reference: Pinal Dave (http://blog.sqlauthority.com)

MySQL – Date and Time Related System Functions

MySQL supports lot of functions to get current date and time. The following are some of the system functions.

There are two functions to get current date

SELECT curdate(),current_date();

There are two functions to get current time

SELECT curtime(),current_time();

There are two functions to get current date along with time

SELECT now(),current_timestamp();
SELECT utc_date();
SELECT utc_time();
SELECT utc_timestamp();

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER 2016 – Comparing Execution Plans

New tools bring new capabilities and sometimes these are ingrained deep into the product that we can easily miss them out. And SQL Server 2016 is no different. After installing the CTP, I was pleasantly surprised to see this addition to help our DBA’s who are doing performance tuning and query tuning activities.

So what am I talking about? Think of a scenario wherein you are doing some sort of testing in your QA environment. You seem to be troubleshooting a production scenario and found that very scenario to be working very well in your test environment. Though you have taken the data from the production database to the test QA server less than a month back, you are still baffled with the question – why this difference?

The next requirement everyone looks at is to start comparing the execution plans between your production and test environment for the same query. Some of the execution plans on a production server can run to pages sometimes and are difficult to find difference visually.

SQL Server 2016 – Plan comparison

If you have the execution plan from your production environment, then go ahead and open it in SQL Server 2016 Management Studio and right click to get the following option:

compare plan 00 SQL SERVER 2016   Comparing Execution Plans

You can see that an interesting addition. The “Compare Showplan” needs to be selected. Now, select the plan that was generated from your test environment. This will make bring both the execution plans on a side-by-side view as shown below. Look at the Purple color on the nodes.

compare plan 01 SQL SERVER 2016   Comparing Execution Plans

This represents the fact that both the nodes are same but have some differences in some properties. Now it is upto us to right click and bring the properties tab.

compare plan 02 SQL SERVER 2016   Comparing Execution Plans

Here we can go ahead and compare each of the node properties to find where the discrimination between the plans are. This is awesome capabilities added in the new version.

I am sure you will find this feature of great use in query tuning exercises in your environments. Having said that, one thing I have seen interesting is, if you take a live “Actual Execution Plan” and then try to compare it with a saved .sqlplan file – it will come up with an error as shown below:compare plan 03 SQL SERVER 2016   Comparing Execution Plans

Do let me know if you would use this feature and if you have used this feature – what is some of the enhancements you wished this comparison operator had? I would like to learn from you for sure.

Reference: Pinal Dave (http://blog.sqlauthority.com)

MySQL – Get Latest Identity Value by Inserts

In SQL Server, whenever you add data to a table that has an identity column, you can get the lastly generated identity value using @@identity or scope_identity(). Similarly, in MySQL you can make use of LAST_INSERT_ID () function

Let us create the following dataset


Create table test(id int AUTO_INCREMENT NOT NULL,name varchar(100));
INSERT INTO test(name) select 'MySQL' union all
INSERT INTO test(name) select 'SQL Server' union all
INSERT INTO test(name) select 'Oracle' ;

After executing the above code, if you want to know the lastly generated identity value, use the following code


SELECT LAST_INSERT_ID() AS id_value;

The result is 3

Note that this will work only in the current session and it is advisable to use the code immediately after the INSERT statement. If you want to make use of the value at a later point within the block of code, you can assign it to a variable.


SET @id_val:=(SELECT LAST_INSERT_ID());

Now you can use the value of @id_val at the later point within a block of code or a procedure.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Installation failed with error – Wait on the Database Engine recovery handle failed

You might have seen on Facebook that I have completed the new setup of my machines. Since I use VMs to do my tests. To have a domain, I made a VM as domain controller and another as a member server. Next step was to install SQL Server and by mistake, I have run the setup of SQL on a domain controller. As per Microsoft documentation they don’t recommend installing SQL on the domain controller and there is a warning in SQL Setup.

setup dc 01 SQL SERVER   Installation failed with error   Wait on the Database Engine recovery handle failed

—————————
Rule Check Result
—————————
Rule “Computer domain controller” generated a warning.
Installing SQL Server 2016 CTP2.4 on a domain controller is not recommended.
—————————
OK
—————————
I proceeded further and it failed at the end with below error
Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.
Here is the complete message in summary.txt file.
Feature:                       SQL Server Replication
Status:                        Failed: see logs for details
Reason for failure:            An error occurred for a dependency of the feature causing the setup process for the feature to fail.
Next Step:                     Use the following information to resolve the error, uninstall this feature, and then run the setup process again.
Component name:                SQL Server Database Engine Services Instance Features
Component error code:          0x851A001A
Error description:             Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.
I followed the error message as it asked to check ERRORLOG. Here is message in ERRORLOG
Error: 17190, Severity: 16, State: 1.
Initializing the FallBack certificate failed with error code: 1, state: 20, error number: 0.
Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.
Error: 17182, Severity: 16, State: 1.
TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.
Error: 17182, Severity: 16, State: 1.
TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.
Error: 17826, Severity: 18, State: 3.
Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
Error: 17120, Severity: 16, State: 1.
SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

I searched on the internet and found that this could be issued with a SQL Service account. I selected all default configuration and it was NT Service\MSSQLServer

I uninstalled SQL Server and this time I selected Local System account for SQL Service in setup wizard and voilà it went fine.

Have you ever faced any such error on a regular machine? What was the solution?

Reference : Pinal Dave (http://blog.SQLAuthority.com)

MySQL – Different Methods to Know MySQL Version

In SQL Server, to know the current version of the server, you can use system variable @@VERSION

How many of you know that the same can be used in mysql to know the version?

Also there are many ways to know the version of MySQL

Method 1:

Select @@version as version

It will display the current version of the MySQL Server

There is another function named version()

Method 2:

Select version() as version

The above produces the same result as @@version

You can also use another method to know this

Method 3:

Show variables like '%version%'

The above returns a resultset with two columns variable_name and value. You can find the version where variable_name says version.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Trick – Running SSMS With Different Windows Account

Recently in one of my interaction with a DBA inside a big financial organization, I was surprised to see that the administrator was logging into a desktop / server using a different credential. What baffled me was the fact that he was logging off. I immediately asked, why are you doing this? He said, since he was using windows authentication, which was different from his normal credentials that for DB related activities he used to create a different session. After a bit of research, I found this to be a common practice in a number of organizations.

Many companies provide two different windows account to any Administrator. One “regular” account (example SQLAuthority\Pinal) is used for general work like emails, login to laptop etc. And another “admin” account (example SQLAuthority\adm_pinal) for administrative level tasks to be done on the server. The regular account would have no access to SQL Servers, but the admin – level account would. To make security stronger, the companies use a Windows account to connect to SQL Server.

This makes thing little interesting. If one has to connect to SQL Server from the laptop where the DBA has logged in with regular account, he/she has to do “Right Click” with Shift key pressed and then use “Run as Different user” to use the admin account.

ssms runas 01 SQL SERVER   Trick   Running SSMS With Different Windows Account

Pressing shift key is important otherwise we would not see that option. After choosing that, it would pop-up with user name and password window. If you notice, you would notice ssms.exe path which is used.

ssms runas 02 SQL SERVER   Trick   Running SSMS With Different Windows Account

Another way, which I prefer is using Run as command. The program runas.exe allows to let us tell Windows to run a program using a different user’s current network environment instead of the local environment. The full details and switches of the run as the program can be found in TechNet article.

I would normally create shortcut on desktop. Right Click and choose a new shortcut as shown below

ssms runas 03 SQL SERVER   Trick   Running SSMS With Different Windows Account

and give location/parameter as below

C:\Windows\System32\runas.exe /noprofile /env /user:SQLAuthority\adm_Pinal “C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\Ssms.exe”

You need to change two things, user name and the correct path for SSMS.exe

Once it’s saved, you can double click and provide password on the command prompt. I have given a shortcut name as Admin SSMS in below screenshot. As soon as I double click, I see below

One credential is provided, SSMS would open with that windows account.

I must conclude by saying that if you are using SQL Authentication then you do not need to do this because Windows credentials are not passed to SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How to Change Server Name?

In recent past I have renamed my VM and wanted to make sure that everything is fine. This renaming process is also needed for changing the server names called inside of SQL Server too. So I searched to know various places from where we can get host name and SQL Server name. Finally, I formed this query which can gather same details from various sources:

SELECT  HOST_NAME() AS 'host_name()',
@@servername AS 'ServerName\InstanceName',
SERVERPROPERTY('servername') AS 'ServerName',
SERVERPROPERTY('machinename') AS 'Windows_Name',
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS 'NetBIOS_Name',
SERVERPROPERTY('instanceName') AS 'InstanceName',
SERVERPROPERTY('IsClustered') AS 'IsClustered'

Most of these server properties have been there for a while and sometimes I think it requires a refresher like this to remember them again. When we run this query on the non-clustered machine, we should see only two distinguished names. Machine name and Instance name. If you have default instance, then Instance Name would be NULL in the query.

ren sql 01 SQL SERVER – How to Change Server Name?

If there was a rename of host, then you would see host_name would be different and that’s not something which should exist.

Note that Instance Name rename is unsupported. In named instance the server name is in format of ServerName\InstanceName. So, you can change it to NewName\InstanceName but NOT to ServerName\ NewInstanceName. I am sure this is a fantastic trivia to know.

If you find any mismatch, then you need to follow below steps:

  1. execute below to drop the current server name

EXEC sp_DROPSERVER 'oldservername'

  1. Execute below to add new server name. Make sure local is specified.

EXEC sp_ADDSERVER 'newservername', 'local'

  1. Restart SQL Services.
  2. Verify the new name using:
    1. SELECT @@SERVERNAME
    2. SELECT * FROM sys.servers WHERE server_id = 0

I must point out that you should not perform rename if you are using:

  1. SQL Server is clustered.
  2. Using replication.
  3. Reporting Service is installed.

I would like to know if anyone has ever tried doing this in your environment? What is the typical reason for you doing the same? I hope this helps incase you get into a tangle like this. Let me know your thoughts via comments.

Reference: Pinal Dave (http://blog.sqlauthority.com)