Mustafa EL-Masry SQL Server 2014

Create Clustered ColumnStore index for All tables


Problem :

we are working in big project SQL Server 2014 upgrade and SQL Server 2014 Performance this project our role to test the new feature in SQL Server 2014 from the performance wise so we decide  to deploy the new Enhancement in SQL Server 2014 (Clustered ColumnStore index ) CCI so i take backup from one database in my work environment and restored it again with prefix _2014 .

this new type of index to create it on the table this table should be no index on it so i drooped all the index from all the tables but the Problem now How can i create the Clustered columnstore index on all tables( tables supported CCI) by one Click.

Solution :

i do one script to create Clustered columnstore index on all supported tables and here the description of the Script

1- to create CCI on any table we have some limitations one of this limitations the data type of the Columns CCI not supported Some data type.

2- I inserted the data type not supported by CCI in temp Tables

CREATE TABLE #CCI_DataType_Limitation ( DataType NVARCHAR(MAX) )
INSERT INTO #CCI_DataType_Limitation
VALUES ( ‘text’ ),
( ‘timestamp’ ),
( ‘hierarchyid’ ),
( ‘Sql_variant’ ),
( ‘xml’ ),
( ‘geography’ ),
( ‘geometry’ )
Select * from #CCI_DataType_Limitation

Continue reading “Create Clustered ColumnStore index for All tables” →

Mustafa EL-Masry SQL Server 2014

Breakthrough in SQL Server 2012 Security fixed by SQLServer 2014


Hi dear we have big bug in SQL Server 2012 security this from my personal view it’s Breakthrough in SQL Server 2012 Security in SQL Server 2012 can the user IMPERSONATE another user and take his privilege and do what he need What !! what i am saying is fact in SQL Server 2012 but it’s fixed in SQL Sever 2014 let’s go for demo:

Open your SQL Server 2014 and open 2 sessions

First Session : Create sysadmin user and read user

1- Sysadmin user

USE [master]
GO
CREATE LOGIN [adminuser] WITH PASSWORD=N’admin’, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [adminuser]
GO

2- Read user

USE [master]
GO
CREATE LOGIN [Readeruser] WITH PASSWORD=N’P@$$w0rd’
GO

Continue reading “Breakthrough in SQL Server 2012 Security fixed by SQLServer 2014” →

Mustafa EL-Masry Administration

How to Check the last restart for DB Server


Hi Dears i received request from the developers by the Staging DB was down yesterday evening, between 6:00 PM to 9:00 PM, can you please advice at this time to do Scientific investigation and troubleshooting so at this time you should check 2 point :

  1. Last SQL Server instance restart.
  2. Last DB server (windows services ) restart.

Last SQL Server Restart :

we can check it easily and by the Easiest ways (SQL Query)

SELECT sqlserver_start_time FROM sys.dm_os_sys_info
SELECT ‘Statistics since: ‘ + CAST(sqlserver_start_time AS VARCHAR) FROM sys.dm_os_sys_info
Server Restart

Continue reading “How to Check the last restart for DB Server” →