New SQL Server 2014 permissions chart is available

New SQL Server 2014 permissions chart is available

 

 

 

 

 

 

Hello My Followers as usual our Community share the new Subject and the new news so Today i need to say for ALL DBA don’t worry from the Security part you are working in Microsoft Product.

Microsoft created new chart for all New Permission in SQL Server 2014  It includes the 3 permissions that are new in SQL Server 2014. also you will find Poster for SQL Server 2012 Security Chart and Azure SQL Database Chart

Download the poster from here. Everyone should have this in their workplace.

Database Level Permision

 

 

 

to know all our blogs in SQL Server 2014 To know what is the new in SQL Server 2012 or 2014 to View all my tips

See you in the next blog Post

Follow up us on

Facebook Page LinkedIn Group,Twitter,Networked Blogs,Facebook Group,Youtube Channel, 

Spread the word

Durable vs. Non-durable tables: In-Memory OLTP

Durable vs. Non-durable tables: In-Memory OLTP

 

 

 

 

 

 

Hello followers Today i will explain very important point Durable vs. Non-durable tables SQL Server 2014 when come for us with In-Memory  Feature come with 2 Kind of tables Durable and Non-durable tables The default one is Durable Table. One cool feature besides the durable in-memory tables, is the possibility to create non-durable in-memory tables. As you realize, you can get a lot of performance improvement with the in-memory solution, but what is the benefit with non-durable tables as the data will be lost in case of failure? Well, one option is to use non-durable in-memory tables as staging tables in your ETL solution as you don’t care about the staging data in case of a system crash. do you have Temporary data ? do you have data you are OK to lose the data due to SQL Server restart at this time you can think in Non-durable tables, 

Memory-Optimized-Table come with 2 option (DURABILITY = SCHEMA_AND_DATA) , (DURABILITY = SCHEMA_ONLY)

Durable Table          : DURABILITY = SCHEMA_AND_DATA

Non-durable tables :DURABILITY = SCHEMA_ONLY

Why would I want to use a non-durable in memory table?

These non-durable tables, are special case tables after the database engine is rebooted he table will be empty but this tables is great for filling  very very fast so this tables is very fantastic for  staging tables in data warehouses, logging tables,For production tables within an OLTP system these non-durable tables are a really bad idea. If you were to use non-durable tables in a production OLTP database, under most situations if the data was lost this would end up being a really bad idea because data would be lost when the SQL Server instance is rebooted.

Let’s go for demo to know How non-durable in memory table is faster than durable in memory table and how the data will lose after the engine rebooted.

Otey SQL2449 Fig 1

Spread the word

SQL Server Restart Notification

SQL Server Restart Notification

 

 

 

 

 

 

Hi dears today we will explain How we can do Automatic notification for SQL Server Restart After the DB Server Restart Successfully and All Databases Returned and not in Recovery mode this we can say it as log or as notification to know what happened in your Server but for DB Server notification when Restart Start this another thing i am  not Cover it in this post i will Cover it in another Post

Today we don’t need for any tolls only SQL Server can do it by Small Configuration let’s Go for Demo to see How We can get SQL Server Restart Notification after Restart Success

 

  • Database Server Configuration 

EXEC sys.sp_configure N'Show Advanced Options', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'scan for startup procs', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

Spread the word

SQL Server 2012 Service Pack 2 (SP2) RTM has released.

SQL Server 2012 Service Pack 2 (SP2) RTM has released.

 

 

 

 

 

 

Hi Dears today i come with good news for all SQL Server DBA/DB Analyst and DB Developer Microsoft SQL Server team is pleased to announce the release of SQL Server 2012 Service Pack 2 (SP2).and the good thing here is this upgrade is free and doesn’t require an additional service contract.The Service Pack and Feature Pack updates are available for download on the Microsoft Download Center. This includes Slipstream installation packages that can be utilized with existing licenses to install (or upgrade to) new instances with SP2 pre-installed.

Microsoft® SQL Server® 2012 Service Pack 2 (SP2)

Microsoft® SQL Server® 2012 SP2 Feature Pack

SQL Server 2012 SP2 will release through other distribution channels (including Microsoft Update) by end of July 2014.The first Cumulative Update of SQL Server 2012 Service Pack 2 is scheduled to release end of July 2014. It will contain hotfixes that have been delivered in SQL Server 2012 Service Pack 1 Cumulative Update 10.

Bugs that are fixed in SQL Server 2012 Service Pack 2 :

Spread the word

What we Should know before going to upgrade to SQL Server 2014(4/4)

What we Should know before going to upgrade to SQL Server 2014(4/4)

 

 

 

 

 

 

Hello my Followers in the last previous post  i covered some points not supported in SQL Server 2014 in the new Technology and Feature Memory-Optimized-Table

  • Memory Optimized Table Dosen’t Support Table Without Non Clustered index or Primary Key
  • IDENTITY Feature not Supported in Memory-Optimized-Table
  • ANSI_PADDING OFF Not Supported in Memory-Optimized-Table
  • Memory-optimized tables Not Suuported as target of MERGE

and today we will Cover new points and limitation in SQL Server 2014 Memory Optimized Table

SQL Server 2014

Spread the word

Speak at SQL Saturday Cairo #321 at Microsoft smart village Egypt

Speak at SQL Saturday Cairo #321 at Microsoft smart village Egypt

 

 

 

 

 

 

 

I am travelling on next Monday to speak at SQL Saturday Cairo #321 at Microsoft smart village Egypt, I am going to talk 2 sessions about SQL Server 2014 performance dreams and T-SQL performance guiltiness for better DB stress powers , here below is more details about my sessions

http://sqlsaturday.com/321/schedule.aspx

Spread the word

Memory allocated for Memory-Optimized-Table in SQL Server 2014

Memory allocated for Memory-Optimized-Table in SQL Server 2014

 

 

 

 

 

 

 

Hi dears today i coming with small DMV but it’s very important for us to know the actually memory allocated for each memory optimized table in our SQL Server 2014 Database let’s go :

  • find allocated memory for one Memory-Optimized-Table 

select * from sys.dm_db_xtp_table_memory_stats
where object_id = object_id('dbo.students')

 

Spread the word

Walk inside DMV stack (11/100)

Walk inside DMV stack (11/100)

 

 

 

 

 

 

 

 

By the last blog , I explained DMV stacks that are used often to diagnose disk space issues and by this time I am turning now into a new portfolio about SQL Server Agent service administration for jobs and alerts administration as follows:

 

1-      Assign a mail profile for SQL Server agent service :

You have to create a mail profile and much preferable to make it public to be used by any other services or functions not SQL Server agent service ONLY , you can read more about it at http://msdn.microsoft.com/en-us/library/ms187605.aspx , then you have to assign it to SQL Server agent service as follows:

 


USE [msdb]

GO

EXEC msdb.dbo.sp_set_sqlagent_properties @databasemail_profile=N'mailprofile'

GO

EXEC msdb.dbo.sp_purge_jobhistory  @oldest_date='2014-05-22T12:13:19'

GO

USE [msdb]

GO

EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=999999,

@jobhistory_max_rows_per_job=10000,

@databasemail_profile=N'mailprofile'

 

Spread the word

Subscriptions and Delivery (Reporting Services)(1/2)

Subscriptions and Delivery (Reporting Services)(1/2)

 

 

 

 

 

 

 

HI dears today we will talk in very fantastic subject amazing option in SQL Server reporting Services Subscriptions and Delivery if we have reports and need to send by email for one Subscriptions or multiple Subscriptions we can do it from Subscriptions option in RRS before Jumping to the Configuration i need to explain How you can find this options and what is the Subscriptions Requirement .

images

 

 

Spread the word

What we Should know before going to upgrade to SQL Server 2014(3/4)

What we Should know before going to upgrade to SQL Server 2014(3/4)

 

 

 

 

 

 

Hello my Followers in the last previous post  i covered some points not supported in SQL Server 2014 in the new Technology and Feature Memory-Optimized-Table :

  1. We can Create Index on NULL Column on Memory-optimized-table ..?
  2. We can Create Memory-optimized-table Without Primary Key Column ..?
  3. Clustered index Supported or not Supported in Memory-optimized-table ..?
  4. What about Computed Column / PERSISTED Supported or No in Memory-optimized-table ..?
  5. How we can Update the Primary key Column in Memory-optimized-table ..?

and today we will Cover new point and limitation in SQL Server 2014 Memory Optimized Table :

SQL Server 2014

 

 

 

 

 

 

 

Spread the word

In love of SQL Server 2014

In love of SQL Server 2014

 

 

 

 

 

 

Because our community http://sqlserver-performance-tuning.net/ is believing in SQL Server 2014 performance dreams based so that we are all trying nowadays to unveiled all of its new performance features and make spotlight on the most important ones for both DB developer and administrator through the below logs , I am urging everybody who even didn’t breath air for SQL Serer to read them and of course he will enjoy extremely working with SQL Server 2014 :

Spread the word

Performance Improvements in SQL 2014(2/4)

Performance Improvements in SQL 2014(2/4)

Hi dears, on my previous blog we saw about a enhancement regarding a update statistics on SQL 2014. Will see yet another enhancement on SQL Server 2014 which is non clustered indexes on temporary table, and which can also be called inline indexes.

“Non clustered indexes on table variable”

First we will see ‘Which is not possible in previous edition, which is now possible in 2014′ secondly we will see ‘Which is possible in previous edition, which can be coded very easily’  

NewFeatures-logo

Spread the word

Walk inside DMV Stacks (10/100)

Walk inside DMV Stacks (10/100)

Today we will talk about Backup /Restore Status DMV :

how to detect Backup /Restore status, last backup operation for your database.

Hi dear, being a DBA we should practice to tell ‘it can be’. We all know the backup are back boons, and part of our job. Being working in a environment, where the database size up to 1TB. Just scheduling the back up and sit back, won’t be fair. We suppose to answer many question regarding our backups, as I told in the beginning we should answer ‘Can be’ most of the time.

Can you tell me, when will the backup finish ?

How many percentage completed so for ?

What was the elapsed time so for ?

Can you give me the answer for all this, Yes of course. It can be answered.

Spread the word

List on Non-Clustered HASH index in SQL Server 2014

List on Non-Clustered HASH index in SQL Server 2014

Hi Dears Non Clustered HASH Index is new index in SQL Server 2014 supported for Memory-Optimized-Table this index we will speak on it by Detail in the next posts because it’s need More Posts to Cover all things in this New Index type but today we will know How we can list on Non-Clustered HASH Index in SQL Serve 2014

SELECT
 object_schema_name([object_id]) As [Schema Name]
, object_name([object_id]) As [Table Name]
, [name] As [Index Name]
, [type_desc] As [Index Description]
FROM
sys.hash_indexes
Spread the word

List All Memory-Optimized-table in SQL Server 2014

List All Memory-Optimized-table in SQL Server 2014


Hi Dears today i will post very simple DMV but it’s very nice Select How we can list all Memory-optimized-table in SQL Server 2014 any one work on SQL Server now Sys.tables in SQL Server 2014 we have 2 new Columns

(durability_desc,Is_memory_Optimized)


SELECT name,type_desc,durability_desc,Is_memory_Optimized FROM sys.tables
WHERE Is_memory_Optimized = 1

Spread the word

What we Should know before going to upgrade to SQL Server 2014(2/4)

What we Should know before going to upgrade to SQL Server 2014(2/4)

 

 

 

 

 

 

Hello my Followers in the last previous post i covered some points not supported in SQL Server 2014 in the new Technology and Feature Memory-Optimized-Table :

  1. Memory Optimized table doesn’t support Alter.
  2. We can’t create index after we create the table but before we can do (Non-Clustered Hash / Non-Clustered )
  3. Memory optimized tables cannot be created in system databases.
  4. The index option ‘fillfactor’ , data_compression is not supported with indexes on memory optimized tables.
  5. Indexes on character columns that do not use a *_BIN2 collation are not supported with indexes on memory optimized tables
  6. The data types char(n) and varchar(n) using a collation that has a code page other than 1252 are not supported with memory optimized tables.

Today we are going to Show another Important point must be the DB Developer and DB Analyst / Architecture / DBA take care about it  let’s go for our DEMO : I will work on the Same Database i sued it on the previous Post

What we will Cover today in this Post is :

  1. We can Create Index on NULL Column on Memory-optimized-table ..?
  2. We can Create Memory-optimized-table Without Primary Key Column ..?
  3. Clustered index Supported or not Supported in Memory-optimized-table ..?
  4. What about Computed Column / PERSISTED Supported or No in Memory-optimized-table ..?
  5. How we can Update the Primary key Column in Memory-optimized-table ..?
Spread the word

Performance Improvements in SQL 2014(1/4)

Hi dears, it is always nice to know something new. Would like to start with a wisdom i like “Learning brains will stay younger”. It is always advised by many performance tuning expertise,

“Update Statistics before you take any other steps to tune performance”.

SQL server 2014 has new feature called incremental statistics. Really it was a nice a feature, if you are having your tables spread into many partitions. Tables on single partition won’t be benefited by this feature.  Before we know about this feature, will have a glance about Update statistics, if you did’t update statistics on the database. The query optimized will struggle to give you best execution plan, which will ruin the performance of your query for sure.

Click to Continue

Spread the word

Walk inside DMV Stacks (9/100)

 

 

 

 

 

 

 

Today we will talk about one of Security DMV :

how to detect any create /update for sys admin user to your logins.

Sys admin privilege is the higher privilege in SQL server service so you should know and detect if any one added to your server and assigned as sys admin

The below DMV you could put it in job that run every 30 minutes and it will detect if any sys admin user added in the last hour to your logins.

also this SP will send to you mail notification about the user added and i make also customization for raising message that you could use it’s number through alerts.

Spread the word

Walk inside DMV Stacks (8/100)

Walk inside DMV Stacks (8/100)

 

 

 

 

 

 

 

 

By the last blog , I explained DMV stacks that are used often to determine tables sizes as well as DB growth , now I am going to zoom more on another disk issue that annoy often all database administrator which is log file inflation due to a certain transaction which might end up eventually with long DB recovery time after DB service restart or cluster failover , this is really horrible and people might look for you J if it is not solved quickly , that is why I am going now to illustrate fast ways to define log space issues and fix them ASAP in addition some tips to mitigate DB recovery time

1-      Define transaction log file free space as well as size

Last blogs , we discussed how to find the disk spaced consumed by all data and log DB files but this time you are trying to know the percentage of free space inside a log file itself so that you can decide faster which log file can be shrunk quicker , you can do it easily as follows:

Spread the word

Walk inside DMV Stacks (7/100)

Walk inside DMV Stacks (7/100)

 

 

 

 

 

 

 

 

 

By the last blog , I explained DMV stacks that are used often to determine the major disk issues for any DBA , disk space and disk IO performance issues and we could determine which DB file was responsible about the related issues to take quick win solution like shrinking ,moving DB file to another bigger drive but this is not the end of story coz you have to find out exactly the root causes of this DB growth to address it permanently thereby I am turning now into another DMV stack that will zoom more on table level as well as DB growth

Spread the word

List all Memory-Optimized-table on SQL Server 2014

Hi Followers fast way to fthe  find Memory-Optimized-table from basic table in SQL Server 2014 is by simple query in SQL Server 2014 we have new Column name IS_memory_Optimized in System tables (SYS.Tables) this new Column Allow 2 value

1 = IS_memory_Optimized

0 = Basic Table

Demo

Create in-Memory Database :


CREATE DATABASE SQL2014_WorkShop

GO

ALTER DATABASE SQL2014_WorkShop
ADD FILEGROUP SQL2014_WorkShop_mod CONTAINS MEMORY_OPTIMIZED_DATA

ALTER DATABASE SQL2014_WorkShop
ADD FILE( NAME = 'SQL2014_WorkShop_mod' ,
FILENAME = 'c:\SQL2014_WorkShop_mod')
TO FILEGROUP SQL2014_WorkShop_mod;

Create Memory-Optimized-Table


CREATE TABLE [dbo].[orders_mem](
 [O_OrderKey] [int] NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=3000000),
 [O_CustKey] [int] NULL,
 [O_OrderStatus] [Nvarchar](64) NULL,
 [O_TotalPrice] [decimal](13, 2) NULL,
 [O_OrderDate] [datetime] NOt NULL,
 [O_OrderPriority] [Nvarchar](15) NULL,
 [O_Clerk] [Nvarchar](64) NULL,
 [O_ShipPriority] [int] NULL,
 [O_Comment] [Nvarchar](80) NULL,
 [skip] [Nvarchar](64) NULL

INDEX IX_OrderDate (O_OrderDate ASC)
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA)
GO

Note : We can.t Create index on Nullable Column {Memory-Optimized-table didn’t Support :( }

Check in Memory-Optimized-table


Select NaME , IS_memory_Optimized from Sys.tables
WHERE IS_memory_Optimized = 1

Please Follow Me Mostafa Elmasry to know more Secret in SQL Server 2014

Follow up us on

Facebook Page LinkedIn Group,Twitter,Networked Blogs,Facebook Group,Youtube Channel, 

Spread the word

What we Should know before going to upgrade to SQL Server 2014(1/4)

What we Should know before going to upgrade to SQL Server 2014(1/4)

Hi dears i think all (DBA / DB Analyst / BI developer / SQL Developer) knowing about new Improvement in The world of databases new Enhancement in The world of databases  How the Query will be faster by  SQL Server 2014…ETC , but i think not all try this New version from SQL SERVER not knowing about the restriction on SQL Server 2014 , How we can use ..? What we used before in SQL Server 2012 and we can’t use it now in SQL Server 2014 ..?

So before going to upgrade we should now

  1.  the Improvement of SQL Server 2014 and the new Feature on SQL Server 2014. you can follow this Series of blogs  thanks for SHEHAP EL-NAGAR for his wonderful posts:

Why need to upgrade quickly to SQL Server 2014…? (1/10)

Why need to upgrade quickly to SQL Server 2014…? (2/10)

Why need to upgrade quickly to SQL Server 2014…? (3/10)

Why need to upgrade quickly to SQL Server 2014…? (4/10)

2- You Should now the Restriction/Constructs in SQL Server 2014 (Follow me  in this Part)

(What you do in SQL Server 2012 you can do on SQL Sever 2014) / but / (What you do on SQL Server 2012 you can’t do it on all new feature on SQL Server 2014 )

SQL Server 2014 coming with new Improvement and enhancement  called (In-Memory OLTP)

Memory-optimized tables and natively compiled stored procedures do not support the full Transact-SQL surface area that is supported by disk-based tables and interpreted Transact-SQL stored procedures. When attempting to use one of the unsupported features, the server returns an error. in my post i have 2 parts(theoretical part,Practical Part)

Spread the word

Walk inside DMV stacks (6/100)

Walk inside DMV stacks (6/100)

 

 

 

 

 

 

 

 

 

By the last blog , I explained DMV stacks that are used often for security tasks and now I am turning into a very interesting part related to storage usage and DB sizes that are commonly used while fast troubleshooting for any disk space issue to know easier and faster which DB file either .mdf , .ndf or ldf is consuming the biggest space usage , this is extremely important while catastrophic situations and no enough RDP access for DB server and you want are obligated  to know quickly where is the exact space bottleneck , in addition I will explain how to find those DB files that are consuming the most IO throughput and in this was you will really have a clear picture about all disks usage either from space or IO throughput perspectives

Spread the word

Walk inside DMV Stacks (5/100)

 

 

 

 

 

 

Hi dears today i am coming with new DMV and it’s like DMV for Monitoring and we can use it For any reason but it’s very Fantastic DMV it,s Support All SQL Server LEVL until SQL Server 2014

Fast way to Check Database  Propitiates   

 

SELECT database_id,
CONVERT(VARCHAR(25), DB.name) AS dbName,
CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status],
state_desc,
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS DataFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS [Data MB],
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS LogFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS [Log MB],
user_access_desc AS [User access],
recovery_model_desc AS [Recovery model],
CASE compatibility_level
WHEN 60 THEN '60 (SQL Server 6.0)'
WHEN 65 THEN '65 (SQL Server 6.5)'
WHEN 70 THEN '70 (SQL Server 7.0)'
WHEN 80 THEN '80 (SQL Server 2000)'
WHEN 90 THEN '90 (SQL Server 2005)'
WHEN 100 THEN '100 (SQL Server 2008)'
When 110 Then '110 (SQL Server 2012)'
When 120 Then '120 (SQL Server 2014)'
END AS [compatibility level],
CONVERT(VARCHAR(20), create_date, 103) + ' ' +

CONVERT(VARCHAR(20), create_date, 108) AS [Creation date],
-- last backup
ISNULL((SELECT TOP 1
CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' +
LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' +
CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' +

CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' +
CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' +

CONVERT(VARCHAR(20), backup_finish_date, 108) +
' (' + CAST(DATEDIFF(second, BK.backup_start_date,
BK.backup_finish_date) AS VARCHAR(4)) + ' '
+ 'seconds)'
FROM msdb..backupset BK WHERE BK.database_name =

DB.name ORDER BY backup_set_id DESC),'-') AS [Last backup],
CASE WHEN is_fulltext_enabled = 1 THEN 'Fulltext enabled' ELSE '' END AS [fulltext],
CASE WHEN is_auto_close_on = 1 THEN 'autoclose' ELSE '' END AS [autoclose],
page_verify_option_desc AS [page verify option],
CASE WHEN is_read_only = 1 THEN 'read only' ELSE '' END AS [read only],
CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink' ELSE ''

END AS [autoshrink],
CASE WHEN is_auto_create_stats_on = 1 THEN 'auto create statistics' ELSE ''

END AS [auto create statistics],
CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update statistics' ELSE ''

END AS [auto update statistics],
CASE WHEN is_in_standby = 1 THEN 'standby' ELSE '' END AS [standby],
CASE WHEN is_cleanly_shutdown = 1 THEN 'cleanly shutdown' ELSE ''

END AS [cleanly shutdown]
FROM sys.databases DB
ORDER BY dbName, [Last backup] DESC, NAME

Follow Me
Mostafa Elmasry 

Follow up us on
Facebook Page LinkedIn Group,Twitter,Networked Blogs,Facebook Group,Youtube Channel,
 

Spread the word

Our Community blogs are now on Microsoft press blogs

Our Community blogs are now on Microsoft press blogs

 

 

 

 

 

 

 

 

We are glad to announce that our community blogs are published now on both Microsoft press blogs and Microsoft Award program blogs,we are getting there first with a series about SQL Server 2014 performance dreams , here you go for the 1st blog of this series :

http://blogs.msdn.com/b/microsoft_press/archive/2014/05/19/from-the-mvps-sql-server-2014-performance-enhancements.aspx

http://blogs.msdn.com/b/mvpawardprogram/archive/2014/05/19/sql-server-2014-performance-enhancements.aspx

Spread the word