RSS

Tag Archives: Database Administration

What is New in SQL Server 2016 Management Studio

Hello my followers today i will speak in very good Point in SQL Server 2016 all we are working on SSMS (SQL Server Mangment Studio) and we should now the options in this tools and what is the new on it so today we will show the new features in SQL Server 2016 Management Studio then later will explain deeply this features if it needed

  • In the installation you find new tape for Tempdb Configuration 
  • Check for Update : ( Now we can update the SQL Server Management Studio alone you will find it under tools )
  • 1- SSMS Update
  • Save Open Queries in SQL Server Management Studio (Tools > options > Query Execution > Prompt to save unsaved T-SQL Query Windows on Close)

Save unsaved T-SQL

Read the rest of this entry »

 
7 Comments

Posted by on November 5, 2015 in SQL Server 2016

 

Tags: , , , , , ,

Customized Database-Server Alert Part#2

Hi my followers today i will complete on the series of the SQL Server Customized Database-Server Alert as i started the first post Part#1 and i explained on it on of the most important Database alert (SQL Server Database alert) and i do one Stored procedure to monitor the database status and to send Immediate alert and today i will add two new alerts :

Part#1 

Read_only Database Report : 

USE msdb
GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

— =============================================
— Author: Mustafa EL.Masry
— Create date: 16/09/2015 01:52 PM
— Description: Report by all Database are in read_only Mode
— We are Exceulded the Drive Name E
— =============================================
Create Proc [dbo].[ReadOnlyDatabasesReport]
@profile_name_P Nvarchar(500) =’DBMailProfile’,@recipients_P Nvarchar(500) =’SQLGULF@MostafaElmasry.com’
AS
begin
Set NOCount on
declare @p_subject Nvarchar(500)
SET @p_subject = N’Databases in Read_Only Mode on DB Cluster ‘ + ( CAST((SELECT SERVERPROPERTY(‘ServerName’)) AS nvarchar))

—Send the mail as table Formate
if(select count(*) from sys.databases where is_read_only <> 0 )>0
Begin
DECLARE @table NVARCHAR(MAX) ;
SET @table =
N'<H2 style=” color: red; ” >Read_only Mode Databases Report</H2>’ +
N’ <span style=” font-size: 16px;” >Urgnet this list by Databases are in the Read_only mode please take immediate action to fix it or Exclude it from the Alert </span>’ +
N'<table border=”1″>’ +
N'<tr><th>Database Name</th><th>Database Status</th></tr>’ +
CAST ( ( Select td=name, ”,td=is_read_only from sys.databases where is_read_only <> 0
FOR XML PATH(‘tr’), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>’ ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name=@profile_name_P, –Change to your Profile Name
@recipients=@recipients_P, –Put the email address of those who want to receive the e-mail
@subject = @p_subject ,
@body = @table,
@body_format = ‘HTML’ ;

END
Else Print ‘All Databases are in Read_Write Mode’

END

Read the rest of this entry »

 
1 Comment

Posted by on September 18, 2015 in Database Alert

 

Tags: , , , , ,

Customized Database-Server Alert Part#1

 

Problem 

AlertHello my Followers as we are working as Database administrators (DBA) we can’t stay on our office 24 hour we should enjoy and by the other things in the life but we still responsible about the stability of Production databases should be accessible in any time with the optimal performance so at this time we should leave something working 24 Hour in our servers to monitors the servers and send to us what is happening immoderately. the market have a lot of Product and lot of tools for Monitoring SQL Server from IO , CPU , backup , Security and Database Performance but not all the organization have the capital to provide you all of this tools at this time you should depend on your self because you are still the Responsible person In front of all the management so in this post and other posts i will publish very important and impressive Customized Database Alert depended on T-SQL (DMV Alert ) also i write in the SQL Server alert type or category and How we can configure it .

 

Part#1  , Part#2

SQL Server Database alert Type :

  1. Warning Alert
  2. Critical Alert
  3. OS Alert
  4. Customized Database alert (DMV_Alert) this point i will start with it because it is not easy like the previous points it is need some one have good Experience in T-SQL (handmade alert)

Customized database alert list :

this is List by the Database alerts i will explain it one by one and we will go deeply for each one to know the mechanizm of the Stored procedure and How it work and How we can update on it based on our work environment

  • Database status Report
  • Read_Only Database Report
  • Backup Running Now
  • Check backup History
  • Check_ServicesStatus_Reporting
  • Disabled SQL Server Jobs
  • Disk Space Alert
  • SQLServerRestartAlert_1
  • SQLServerRestartAlert_2
  • sp_check_log_shipping_monitor_alert
  • Trg_TrackLoginManagement (Secuirty Trigger)
  • Keep Your DB in Safe Mode (Database Drop/Edit Trigger)
  • Database DDL Alert
  • Job Status Alert (Job Status Trigger to catch any action happened on the jobs status)

I am not remember all the list i will update it ASAP now let us start by the first DMV Alert but before this we should know

Read the rest of this entry »

 
1 Comment

Posted by on September 17, 2015 in Database Alert

 

Tags: , , , , , , , ,

Cannot add new node in SQL Server 2014

Problem 

Hi my followers today i have task to build two new SQL Server Clusters when i am working i go to for adding new Node at this time i revived very new issue for me Rule “SQL Server Database Services feature state” failed

—————————
Rule Check Result
—————————
Rule “SQL Server Database Services feature state” failed. The SQL Server Database Services feature failed when it was initially installed. The feature must be removed before the current scenario can proceed.
—————————
OK
—————————

This error meaning When you are adding the first node the cluster installed with some issues and this relay what is happened with me when i installed the First node i faced some issues so based on that adding second node is blocked

How i can fix this issue :

  • To fix this issue at the first you should check the Cluster error log in the first node and try to do prepare for this node

If this step not success with you to fix this issue you can try the second solution :

  1. Go for the Run
  2. Write Regedit
  3. Go for this path (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQLServer\MSSQL12.MSSQLSERVER2014\ConfigurationState )
  4. Check all the value for all the Component if you found the value is 2 this meaning you have failures in this Component change it to 1

Component name :

MPT_AGENT_CORE_CNI, SQL_Engine_Core_Inst, SQL_FullText_Adv, SQL_Replication_Core_Inst

Note : to find the correct place in the registry you  after this path (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQLServer\) we have to know Our SQL Server Version and SQL Server instance name

  • SQL Server 2008     : MSSQL10.      (InstanceName)
  • SQL Server 2008R2 : MSSQL10_50. (InstanceName)
  • SQL Server 2012     : MSSQL11 .     (InstanceName)
  • SQL Server 2014     : MSSQL12.      (InstanceName)

I hope the issue and How to fix the issue is clear

Follow Us :

LinkedIn Slideshare ,Youtube Channel.MSDN POSTS , WHO WE ARE

 
Leave a comment

Posted by on September 16, 2015 in Errors

 

Tags: , , , , , , , , , , ,

Next SQL Gulf event “SQL Gulf #2 “ at Microsoft Dubai

logo

SQL Gulf Community reveals about its upcoming SQL Gulf event “SQL Gulf #2” at Microsoft Dubai on 5/9/2015, It is the first SQL Server event ever in the Middle East to talk about SQL Server 2016 ..!
SQL Gulf #1 was at Saudi Arabia, Riyadh on 30/8/2014 , indeed it was extremely successful one that is why we are so excited to spread the word everywhere in the Gulf through many SQL Gulf events at all Gulf cities ,interestingly said that the top notch SQL Server experts and most ever popular speakers are coming from US, UK ..etc are coming to speak to you  at SQL Gulf #2 like Kevin Kline from US and Satya Shyam K Jayanty from UK ..etc  and more importantly to know it is the first SQL Server event in the Middle East ever to talk about SQL Server 2016…

Register here to reserve your seat ASAP, seats are limited , here below is the event program:

Session Abstracts:

1-FIRE! A Fullproof Checklist for Tuning and Troubleshooting

Session Abstract: Learning how to detect, diagnose and resolve performance problems in SQL Server is tough.  Often, years are spent learning how to use the tools and techniques that help you detect when a problem is occurring, diagnose the root-cause of the problem, and then resolve the problem.
In this session, attendees will see demonstrations of the tools and techniques which make difficult troubleshooting scenarios much faster and easier, including:
•             XEvents, Profiler/Traces, and PerfMon
•             Using Dynamic Management Views (DMVs)
•             Advanced Diagnostics Using Wait Stats
•             Reading SQL Server execution plan

Every DBA needs to know how to keep their SQL Server in tip-top condition, and you’ll need skills the covered in this session to do it.
Prerequisites: Intermediate database administration and development skills, especially competence with SSMS.
Goal 1: Learn the “sieve” method of troubleshooting and problem solving, and how to make SQL Server alert you when problems arise.
Goal 2: Discover how to use wait stat analysis, as well as correlate performance information from other sources inside of SQL Server including DMVs, performance counters, and Xevent/trace information.
Goal 3: Learn how to use the most important native tools within SQL Server through live demos to successfully conduct troubleshooting and performance tuning.

Summary: Microsoft ships a multitude of tools to help detect, diagnose and resolve problems inside of SQL Server. But which is best to use and when? This session teaches attendees how to tackle the troubleshooting process to achieve repeated, optimal results.

2-What do I need to know about Data Platform Upgrade best practices & techniques

Data Platform Upgrade topic has been a popular session that I’ve presented in major conferences like Microsoft Tech-Ed (North America, Europe & India), SQLPASS, SQLSaturdays and SQLbits since the year 2008.

In this session, we will overview in depth end-to-end upgrade process that covers the essential phases, steps and issues involved in upgrading SQL Server 2000, 2005, 2008 R2 & SQL Server 2012/2014 (with a good overview on 2016 too) by using best practices and available resources.
We will cover the complete upgrade cycle, including the preparation tasks, upgrade tasks, and post-upgrade tasks. Real-world examples from my Consulting experience expanding on why & how such a solution will work in critical situations.

3-Performance Dreams started at SQL Server 2014 and come true now at SQL Server 2016

“Performance Dreams started at SQL Server 2014 and come true now at SQL Server 2016”
, it is just like this because you Microsoft launched live SQL Server 2014 with unbelievable performance reads and substantial improvement that you cannot give up them , come in here to my session and you will know more about many new features and rich powers of SQL Server 2014 regarding performance particularly like  Microsoft project “Hekaton” for In-memory built in for OLTP , CCI( columnstore index) ,Resource Governor for IO consumption ,  lock priority management  and also Single partition online index rebuild technologies ,you will get much hands-on experience  for all definitions , architecture design , values and benefits ,caveats  and recommendations related to each one of them so that you can drive a conscious decision for upgrading your DBs to SQL Server 2014 but keep in mind that are some limitations for those features that should be considered largely before  production deployments …If not ,don’t be upset as it Is still not the end of way coz Microsoft had really rolled out SQL Server 2016 which could address successfully many of those limitations and indeed SQL Server 2014 performance dreams come true at SQL Server 2016, I will speak there simply to help those who didn’t breath SQL Server for the last little while

4-SQL Server Internals and Architecture

Session Abstract: Let’s face it.  You can effectively do many IT jobs related to SQL Server without knowing the internals of how SQL Server works.  Many great developers, DBAs, and designers get their day-to-day work completed on time and with reasonable quality while never really knowing what’s happening behind the scenes.  But if you want to take your skills to the next level, it’s critical to know SQL Server’s internal processes and architecture.  This session will answer questions like:

–       What are the various areas of memory inside of SQL Server?
–       How are queries handled behind the scenes?
–       What does SQL Server do with procedural code, like functions, procedures, and triggers?
–       What happens during checkpoints?  Lazywrites?
–       How are IOs handled with regards to transaction logs and database?
–       What happens when transaction logs and databases grow or shrinks?

This fast paced session will take you through many aspects of the internal operations of SQL Server and, for those topics we don’t cover, will point you to resources where you can get more information.  So strap on your silly, as we cover all these topics and more at speed with tongue planted firmly in cheek!

Prerequisites: Basic understanding of SQL Server operations and activities – such as transactions, queries, and preventative maintenance tasks like backup and recovery.

Goal 1: Learn about the major components within the SQL Server architecture, starting with the relational engine and storage engine, working down to greater and greater detail.
Goal 2: See what happens inside the SQL Server query optimizer and how the query optimizer affects transaction execution time, both read-only and read-write transactions
Goal 3: Review the major operational processes inside of SQL Server that affect memory management and IO activity.

5-Power BI deployment best practices to deliver data analytics to the business

How to deploy Power BI, how to implement configuration parameters and package BI features as a part of Office 365 roll out in your organisation.

Having said that, cloud computing is another aspect of this technology made is possible to get data within few clicks and ticks to the end-user. Let us review how to manage & connect on-premise data to cloud capabilities that can offer full advantage of data catalogue capabilities by keeping data secure as per Information Governance standards. Not just with nuts and bolts, performance is another aspect that every Admin is keeping up, let us look into few settings on how to maximize performance to optimize access to data as required.

Gain understanding and insight into number of tools that are available for your Business Intelligence needs.

There will be a showcase of events to demonstrate where to begin and how to proceed in BI world.

 
5 Comments

Posted by on August 12, 2015 in Event

 

Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

My First Arabic post on Microsoft MSDN in SQL Server 2014

Hello Guys this my First Arabic post on Microsoft MSDN in SQL Server 2014 in very important subject Durable vs. Non-durable tables: In-Memory OLTP 

All My Arabic Posts :

تكنولوجيا الجداول الدائمه والغير دائمه Microsoft SQL Server 2014

الصلاحيات الجديده فى محرك قواعد بيانات مايكروسوفت 2014

أهم بعض الأكواد الديناميكيه المستخدمه فى محرك قواعد بيانات مايكروسوفت 2014

الأكواد الديناميكية لمراقبة ومتابعة حلول تزامن البيانات 1/ 3

الأكواد الديناميكية لمراقبة ومتابعة حلول تزامن البيانات 2/ 3

الأكواد الديناميكية لمراقبة ومتابعة حلول تزامن البيانات 3/ 3

أكواد الفحص الدورى والتشخيص الديناميكيه لمحرك قواعد بيانات مايكروسوفت 2014 الجزء 1

تكنولوجيا الجداول الدائمه والغير دائمه Microsoft SQL Server 2014

Follow ME

View all my tips , LinkedIn Website Slideshare ,Youtube Channel

 
Leave a comment

Posted by on April 30, 2015 in Arabic Posts, MSDN

 

Tags: , , , , , , ,

SQL Server 2014 Health check DMV queries Day 2

images (1)

Hi guys in the Previous post i explained How we can get the SQL Server instance version and How we can know the installed date for SQL Server instance put today is DAY 2 and i will show new DMV in our track Instance Level configuration Queries

DMV#3 Server Properties

below DMV is more helpful and useful this DMV This gives you a lot of useful information about your instance of SQL Server like (Computer name , ProcessID , SQL Server collation  , Product level…etc)

DMV#4 SQL Server Agent Job Information

below DMV it will return for us basic information about SQl Server agent and the user name configure it to review it one by one.

DMV#5 SQL Server Agent Alert Information 

by the below DMV we will return the basic information about SQL Server agent alerts

Check the Full posts and the scripts : http://sqlserver-performance-tuning.net/?p=5924

To check all my posts you can direct to my personal website or you can download this Excel sheet My Technical POSTS

View all my tips , LinkedIn Website Slideshare ,Youtube Channel.

 
1 Comment

Posted by on April 23, 2015 in SQL Server 2014

 

Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,