Tuesday, 8 March 2016

Cleanly Uninstalling Stubborn SQL Server Components

Problem
There are scenarios where SQL Server is difficult or impossible to uninstall, upgrade, or replace (and these can block you from installing a new version or using a specific named or default instance):
  1. An expired Evaluation Edition:
    Evaluation period has expired. For information on how to upgrade your evaluation software please go to http://www.microsoft.com/sql/howtobuy

  2. An expired Management Studio:
    Your Microsoft SQL Server Management Studio evaluation period has expired.

    You can get a key to extend your trial by registering this copy of Microsoft Management Studio online. You may also purchase a key to activate the product.

  3. Unsupported operating system (after an OS upgrade):
    The operating system on this computer does not meet the minimum requirements for SQL Server xxxx.

  4. Missing MSI files, e.g.:
    Slp: Target package: "C:\...\sql_engine_core_inst.msi"
    Slp: InstallPackage: MsiInstallProduct returned the result code 2.

  5. Too many instances to remove individually, or other various errors during uninstall.
In these situations, is there a way to cleanly uninstall SQL Server?
Solution
There is a built-in command called msiexec which has an uninstall parameter (-x). This command can be used to remove stubborn programs through brute force. First, though, you need to get an inventory of the GUIDs that represent the programs you need to remove. All you see in the Control Panel are the friendly names, as you can see here:
Control Panel : Before
(This is a brand new system where I installed multiple SQL Server 2014 components in order to demonstrate my approach of cleanly removing them without using the Control Panel or SQL Server's own setup, but rather using msiexec and the GUIDs for the products.)
You can get the GUID associated with each installed product by searching around the registry, but I would rather use the MSI Inventory tool, which you can download (msiinv_new.zip) from this OneDrive folder, and extract msiinv.exe to a folder, say C:\temp\.
Once that file is there, you can open a PowerShell console, and run the following code:
c:\temp\msiinv.exe -s | Select-String "SQL Server" -Context 0,1
Here is a truncated version of what my output from that command looks like (there is a benign error you can ignore):
Powershell command output
This output is marginally useful for now; I can quickly scan the list and see all the GUID codes for the products I want to remove, and start mentally filtering any out as necessary. I could manually copy and change the output to feed each GUID individually to msiexec, but ultimately where I want to end up is a set of commands I can run directly from the command line, for example:
rem Microsoft SQL Server System CLR Types
msiexec /x "{C3F6F200-6D7B-4879-B9EE-700C0CE1FCDA}"

rem SQL Server 2014 Integration Services
msiexec /x "{327B1B40-2434-4DC5-9D4D-B9B24D4B2EDE}"

rem SQL Server 2014 SQL Data Quality Common
msiexec /x "{2D95D8C0-0DC4-44A6-A729-1E2388D2C03E}"
  
...
And I get there with a little help from my good buddy, co-worker, and PowerShell officionado, Allen White (@SQLRunr):
$a = c:\temp\msiinv.exe -s | Select–String "SQL Server" -Context 1,1;
$a = $a -replace "Product code: ","msiexec /x """;
$a = $a -replace ">", "rem";
$a = $a -replace "\t", "";
$a = $a -replace "}","}""";
$a | Out-File c:\temp\remove.bat -encoding ascii;
Now, you can open up remove.bat, look through the list, and remove any products you *don't* want to uninstall. The format will look like this, almost exactly how I wanted them:
rem Microsoft SQL Server System CLR Types
  msiexec /x "{C3F6F200-6D7B-4879-B9EE-700C0CE1FCDA}"
  
rem SQL Server 2014 Integration Services
  msiexec /x "{327B1B40-2434-4DC5-9D4D-B9B24D4B2EDE}"

rem SQL Server 2014 SQL Data Quality Common
  msiexec /x "{2D95D8C0-0DC4-44A6-A729-1E2388D2C03E}"

...
Now run remove.bat from the command line. You'll get prompts like this:
Windows Installer prompts
And you'll also likely see some confirmation dialogs from User Account Control. You may get some errors or dependency warnings, and to avoid some of the prompts, you'll want to make sure all related services have been manually stopped. You might want to run from an elevated command prompt (perhaps even using the "super admin" if you're on Windows 10), and also play with some of the switches to msiexec, like /quiet. You can enable /quiet by changing the second last line in the Powershell script above to:
$a = $a -replace "}","}"" /quiet";
Also, you may have to run the script twice to completely remove things that failed the first time due to dependency order. The second time around, if you don't use /quiet, you will see multiple notifications that the product you're trying to remove is no longer installed:
You can only remove a product once
But after running through it twice, this should leave you with a much cleaner Control Panel:
Control Panel : After
The remainder of these items should really be much easier to remove manually; or, you may not want to bother; or, you could repeat the process above with different arguments to Select-String. I wanted to focus on a single run-through with the SQL Server components, since they are the ones that prove most problematic.
Summary
This is not the most intuitive approach in the world, but I hope it helps some of you remove stubborn, sticky SQL Server components from your systems. It constitutes a bit more work than normal uninstall operations, but when Control Panel or SQL Server Setup won't cooperate, this might be the next best thing. (Note that this procedure will not be effective in completely removing a clustered instance of SQL Server; I wouldn't even try.)

Monday, 21 September 2015

Subnetting

Subnetting is a process of dividing large network into the smaller networks based on layer 3 IP address.
IP->
  • 32bit decimal number. 
  • written as four numbers between 1 and 255.
  • divided into 5 classes

  • 0 [Zero] is reserved and represents all IP addresses.
  • 127 is a reserved address and is used for testing, like a loop back on an interface.
  • 255 is a reserved address and is used for broadcasting purposes.
  •  IP address has following parts-
    • First 16 bits : NETWORK ID
    • Next 8 bits : SUBNET ID
    • Next 8 bits : HOST ID
Subnet mask
Subnet mask is a 32 bits long address used to distinguish between network address and host address in IP address.
Subnet mask has only one purpose, to identify which part of an IP address is network address and which part is host address.











Subnetting is a process of breaking large network in small networks known as subnets. Subnetting happens when we extend default boundary of subnet mask. Basically we borrow host bits to create networks.

Let's take a example
Being a network administrator you are asked to create two networks, each will host 30 systems.

Single class C IP range can fulfill this requirement, still you have to purchase 2 class C IP range, one for each.
Single class C range provides 256 total addresses and we need only 30 addresses, this will waste 226 addresses. These unused addresses would make additional route advertisements slowing down the network.

With subnetting you only need to purchase single range of class C.
You can configure router to take first 26 bits instead of default 24 bits as network bits.
In this case we would extend default boundary of subnet mask and borrow 2 host bits to create networks.
By taking two bits from the host range and counting them as network bits, we can create two new subnets, and assign hosts them. As long as the two new network bits match in the address, then the address belongs to the same network. You can change either of the two bits, and you would be in a new subnet.


Friday, 11 April 2014

Configure alerts for CPU utilization higher than 80% for more than 5mins

SET NOCOUNT ON
DECLARE @TimeNow bigint
SELECT @TimeNow = cpu_ticks / convert(float, ms_ticks) from sys.dm_os_sys_info
-- Collect Data from DMV
Select record_id, dateadd(ms, -1 * (@TimeNow - [timestamp]),
GetDate())EventTime, SQLSvcUtilization, SystemIdle,
(100 - SystemIdle - SQLSvcUtilization) AS OtherOSProcessUtilization into #tempCPURecords
from ( select record.value('(./Record/@id)[1]', 'int')record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]','int')SystemIdle,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int')SQLSvcUtilization,
timestamp
from ( select timestamp, convert(xml, record)record
from sys.dm_os_ring_buffers
where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
and record like '%<SystemHealth>%')x )y  order by record_id desc
To send detailed sql server session reports consuming high cpu
 For a dedicated SQL Server you can monitor 'SQLProcessUtilization'
 if (select avg(SQLSvcUtilization) from #temp where EventTime>dateadd(mm,-5,getdate()))>=80
For a Shared SQL Server you can monitor 'SQLProcessUtilization'+'OtherOSProcessUtilization'
if (select avg(SQLSvcUtilization+OtherOSProcessUtilization)
from #tempCPURecords where EventTime>dateadd(mm,-5,getdate()))>=50
begin
print 'CPU Alert Condition Ture, Sending Email..'
DECLARE @tableHTML  NVARCHAR(MAX) ;
SET @tableHTML =
    N'<H1>High CPU Utilization Reported</H1>' +
    N'<H2>SQL Server Session Details</H2>' +
    N'<table border="1">' +
    N'<tr><th>SPID</th><th>Status</th><th>Login</th><th>Host</th><th>BlkBy</th>'+
N'<th>DatabaseID</th><th>CommandType</th><th>SQLStatement</th><th>ElapsedMS</th>'+
N'<th>CPUTime</th><th>IOReads</th><th>IOWrites</th><th>LastWaitType</th>'+
N'<th>StartTime</th><th>Protocol</th><th>ConnectionWrites</th>'+
N'<th>ConnectionReads</th><th>ClientAddress</th><th>Authentication</th>'+
CAST ( ( SELECT  TOP 50 -- or all by using *
td= er.session_id,'',
td= ses.status,'',
td= ses.login_name,'',
td= ses.host_name,'',  
td= er.blocking_session_id,'',
td= er.database_id,'',
td= er.command,'',
td= st.text,'',
td= er.total_elapsed_time,'',
td= er.cpu_time,'',
td= er.reads,'',
td= er.writes,'',
td= er.last_wait_type,'',
td= er.start_time,'',
td= con.net_transport,'',
td= con.num_writes,'',
td= con.num_reads,'',
td= con.client_net_address,'',
td= con.auth_scheme,''
FROM sys.dm_exec_requests er
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id  LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
WHERE er.session_id > 50
ORDER BY er.cpu_time DESC ,er.blocking_session_id
FOR XML PATH('tr'), TYPE
)AS NVARCHAR(MAX))+
N'</table>'
-- Change SQL Server Email notification code here


EXEC msdb.dbo.sp_send_dbmail
@recipients='hhhhhhhh.dddd@xxxx.com',
@profile_name = 'xxx',  
@subject = 'ServerName:Last 5 Minutes Avg CPU Utilization Over 80%',
@body = @tableHTML,
@body_format = 'HTML';
END
-- Drop the Temporary Table
DROP Table #tempCPURecords

Tuesday, 4 March 2014

convert comma seperated values into distinct values

CREATE FUNCTION [dbo].Split1(@input AS Varchar(4000) )

RETURNS

      @Result TABLE(Value BIGINT)

AS

BEGIN

      DECLARE @str VARCHAR(20)

      DECLARE @ind Int

      IF(@input is not null)

      BEGIN

            SET @ind = CharIndex(',',@input)

            WHILE @ind > 0

            BEGIN

                  SET @str = SUBSTRING(@input,1,@ind-1)

                  SET @input = SUBSTRING(@input,@ind+1,LEN(@input)-@ind)

                  INSERT INTO @Result values (@str)

                  SET @ind = CharIndex(',',@input)

            END

            SET @str = @input

            INSERT INTO @Result values (@str)

      END

      RETURN

END



DECLARE @str VARCHAR(4000) = '6,7,7,8,10,12,13,14,16,44,46,47,394,396,417,488,714,717,718,719,722,725,811,818,832,833,836,837,846,913,914,919,922,923,924,925,926,927,927,928,929,929,930,931,932,934,935,1029,1072,1187,1188,1192,1196,1197,1199,1199,1199,1199,1200,1201,1202,1203,1204,1205,1206,1207,1208,1209,1366,1367,1387,1388,1666,1759,1870,2042,2045,2163,2261,2374,2445,2550,2676,2879,2880,2881,2892,2893,2894'

SELECT * FROM dbo.split1 ( @str )

--==================================================

DECLARE @input VARCHAR(4000) = '6,7,7,8,10,12,13,14,16,44,46,47,394,396,417,488,714,717,718,719,722,725,811,818,832,833,836,837,846,913,914,919,922,923,924,925,926,927,927,928,929,929,930,931,932,934,935,1029,1072,1187,1188,1192,1196,1197,1199,1199,1199,1199,1200,1201,1202,1203,1204,1205,1206,1207,1208,1209,1366,1367,1387,1388,1666,1759,1870,2042,2045,2163,2261,2374,2445,2550,2676,2879,2880,2881,2892,2893,2894'

drop table #result
create table #result
(
    value bigint
)

      DECLARE @str VARCHAR(20)

      DECLARE @ind Int

      IF(@input is not null)

      BEGIN

            SET @ind = CharIndex(',',@input)

            WHILE @ind > 0

            BEGIN

                  SET @str = SUBSTRING(@input,1,@ind-1)

                  SET @input = SUBSTRING(@input,@ind+1,LEN(@input)-@ind)

                  INSERT INTO #result values (@str)

                  SET @ind = CharIndex(',',@input)

            END

            SET @str = @input

            INSERT INTO #result values (@str)

      END

select * from #result 

--====================================================================
on special demand from a good friend, 
i've modifed the function to work for character comma seperated values too.
Here you go-

create FUNCTION [dbo].Split_charval(@input AS Varchar(4000) )
RETURNS
      @Result TABLE(Value VARCHAR(200))
AS
BEGIN
      DECLARE @str VARCHAR(200)
      DECLARE @ind VARCHAR(200)
      IF(@input is not null)
      BEGIN
            SET @ind = CharIndex(',',@input)
            WHILE @ind > 0
            BEGIN
                  SET @str = SUBSTRING(@input,1,@ind-1)
                  SET @input = SUBSTRING(@input,@ind+1,LEN(@input)-@ind)
                  INSERT INTO @Result values (@str)
                  SET @ind = CharIndex(',',@input)
            END
            SET @str = @input
            INSERT INTO @Result values (@str)
      END
      RETURN
END


DECLARE @str VARCHAR(4000) = 'a,gf,sd,safd,hyd,w,r,t,y,c,c,h,gh,as,h,j,h'

SELECT * FROM dbo.split_charval ( @str )

Thursday, 27 February 2014

trimming characters

declare @T table
  (
  Col varchar(20)
  )



  insert into @T
  Select 'WO-012345' --'images/test1.jpg'
  --union all
  --Select 'images/test2.png'
  --union all
  --Select 'images/test3.jpg'
  --union all
  --Select 'images/test4.jpeg'
  --union all
  --Select 'images/test5.jpeg'

 Select substring( col,charindex('-',Col)+1,6 )
from @T

find a column name in all databases

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%gradient%'
ORDER BY schema_name, table_name;

Thursday, 23 January 2014

Find a VALUE in all the tables

CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN


/*

exec SearchAllTables @SearchStr = 'transparent'

*/

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    SET @TableName =
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE       TABLE_TYPE = 'BASE TABLE'
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM    INFORMATION_SCHEMA.COLUMNS
            WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND TABLE_NAME  = PARSENAME(@TableName, 1)
                AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                AND QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO #Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END
END

SELECT ColumnName, ColumnValue FROM #Results
 END