Introduction to SQL Server 2019

Introduction to SQL Server 2019

SQL server 2019

Microsoft has announced the first public community technology preview (CTP 2.0) of SQL Server 2019. It is powerful and enhanced version of SQL Server. This SQL Server 2019 has a lot of new features, enhanced existing features, more on security and mainly focus on big data analytics. SQL Server 2019 comes with Apache Spark and Hadoop Distributed File System (HDFS) for intelligence over all your data.

Here I’m giving a glimpse of main points of this release.

Platform Support:

SQL Server 2019 supports the platform as below:

  • Windows
  • Linux
  • Containers (Docker & Big Data Analytics)

Main Highlights

  • Intelligence over any data
  • Choice of language and platform
  • Industry-leading performance
  • Advanced Security features
  • Make faster, better decisions

Database Compatibility Level

150

Top 10 Reasons to Choose & Use SQL Server 2019

Top 10 Reason to choose SQL Server 2019

To learn more, attend a free Webinar hosted by Microsoft on “Introduction to SQL Server 2019” on 9th Oct 2018.

Note: Please change the details while registering for Webinar.

Register to Webinar

Summary:

As I have given an idea about SQL Server 2019 , its new features, benefits and a lot of more to come to explore on documentation on Microsoft. Hence go through Microsoft site to explore more. Moreover I’ll also share more on this in coming blogs.

—————————–Hire Me———————————

Hire Me

Advertisements
Posted in Database Administration, Microsoft News, Mirza Husain, Sql Server New Feature, T-SQL | Tagged , , , , , , , , , , | Leave a comment

SQL Warning Fatal Error 605 occurred? Here is A Complete Profound

If you are getting SQL error 605 while working with SQL Server then, this troubleshooting guide will help you resolve this issue.

User Scenarios:

Query-1 “I am acquiring following error while running queries from one of my tables:

sql 605

In order to resolve this, someone told me to run DBCC CHECKDB command. I ran the check however, it reported 0 allocation errors along with 0 consistency errors hence, I am not sure where to go from here. Please help!”

Query-2“Code, which was working fine till last week is suddenly throwing this exception:

sql error 605

We are using .NET Framework 4.0 on the SQL Server 2008 R2 and this exception is thrown while running the stored procedure. I have no idea that how to fix this issue. Is there any solution for this?”

SQL Server Fatal Error 605 – Problem Description

Sometimes while users try to login with a user who is not a System Administrator user then, they encounter this Microsoft SQL Error 605 message. When the severity is Level 21, the session of a user becomes disconnected. After that, the error is written into SQL ERRORLOG and the Windows Application Event Log as EventID=605. The severity also can be 12 that will just throw the error as result to the client application.

In this error message, the first allocation unit that is after the “belongs to…” is the real allocation unit. The other allocation unit that is after “not to” is the expected allocation unit.

Note: An allocation unit is an ID that determines that from which index and object a page belongs to.

SQL Warning Fatal Error 605 occurred – Find the Cause

1. Error: 605, severity: 21, state: 3.
This error message can create a serious issue with the database page or with SQL Server engine while detecting the expected allocation.

2. Error: 605, severity: 12, state: 3.
The severity of this error message also can be Level 12 if the execution of query becomes failed while using the read uncommitted isolation level. Or, the NOLOCK query executed that is also called “dirty read”.

Microsoft SQL Error 605 – Find the Solution

The manual methods to resolve the SQL error 605 depends upon the reason behind its causes. Hence, recognize the cause and opt the fix accordingly.

1. If the Error is Occurred Due to Severity 21

The page may be damaged or incorrect if you encounter the error code 2533 after running DBCC CHECKDB or the CHECKTABLE command. Hence, to resolve this issue, one needs to restore data from the server backup file. In case if the backup file in not present then, utilize DBCC CHECKDB command to repair the data file. Below points will help you to determine the actual cause of this error:

  • Analyze all the issues that are associated with hardware and system
  • Make sure to enable PAGE_VERIFY=CHECKSUM on SQL Server
  • If the backup file of SQL Server is available then, try to restore data

In case if the issue occurs even after executing DBCC CHECKDB command then, contact with the Microsoft technical support team for further help.

2. If the Error is Occurred Due to Severity 12

  • Try not to use the reading uncommitted isolation level
  • Do not modify the existing tables during execution
  • Repeat query until you did not get the error code

MS SQL Error 605 – Resolve it by Expert Solution

SQL Database Recovery tool is an ultimate solution to repair corrupt MDF and NDF SQL Server Database and export it into SQL Server. The software is capable enough to recover the deleted SQL database table’s data. Using this, one can even scan and recover Triggers, Functions, Rules, Tables, Stored Procedures. It provides support to both ASCII and Unicode XML datatype and maintains the folder hierarchy throughout the process.

Final Words

This article includes a complete information about SQL error 605. Here, we have discussed different manual solutions to resolve this SQL Server fatal error 605 . In case, if the manual tricks do not work for you then, an alternate solution is also recommended here.

Posted in Sql Server | Leave a comment

DBA- Interview Questions & Answers

Hi Friends,

This blog is based on the interview based questions.  I have collected, summarized & prepared the answers and presenting in front of you.

SQL-dba-interview-questions-answers

These questions are based on Page & Extents in SQL Server.

  1. What is the fundamental unit of data storage in SQL server?

Ans. Page

     2. What is the page size?

Ans. 8 kB

  1. What is the page header size?

Ans. 96 byte and it is used to store system info about the page

  1. Name the different types of pages in SQL server?

Ans.

  • Data
  • Index
  • Text/Image
  • GAM (Global Allocation Map)
  • PFS (Page Free Space)
  • IAM (Index Allocation Map)
  • BCM (Bulk Changed Map)
  • DCM (Differential Changed Map)
  1. Does log files contains pages?

Ans. No, log files contains only a series of log records

  1. Name the type of extents?

Ans.

  1. Uniform Extents
  2. Mixed extents

7. What is PLE (Page Life Expectancy)?

Ans. It is a perfmon counter, found in the SQL server buffer manager. PLE is the best   indication of how volatile your buffer pool is. Volatility is measured by taking the   average”life” of a page within the buffer pool.

So if lots of pages are being overwritten with new data very often, the average PLE will be low and our BP volatility will be high.

Formula:

PLE threshold= ((maxbp(mb)/1024)4)*300

We can find the vaule of PLE by running this command

T-SQL

[SELECT [object_name],

[counter_name],

[cntr_value] FROM sys.dm_os_performance_counters

WHERE [object_name] LIKE ‘%Manager%’

AND [counter_name] = ‘Page life expectancy’]

8. What is PFS?

Ans. Page free space (PFS) pages record the allocation status of each page. After an          extent has been allocated to an object, the Database Engine uses the PFS pages to    record    which pages in the extent are allocated or free. This information is used when the Database Engine has to allocate a new page.

A PFS page is the first page after the file header page in a data file (page id 1) followed by GAM & SGAM pages.

9. What is DCM?

Ans. DCM stands for differential changed map. This tracks the extents that have changed since the last backup database statement. Differential backup just reads the DCM pages to determine which extents have been modified.

10. What is BCM?

Ans. BCM stands for bulk changed map. This tracks the extents that have changed since the last backup log statement. Although BCM pages appear in all databases, they are only relevant when the database is using the bulk logged recovery model.

Note

Your likes & comments are always appreciated.

 

Posted in Database Administration, Q & A, Sql Server, T-SQL | Tagged , , , , , , , , , , , , , , , | 2 Comments

SQL Database as a Service in Azure

Introduction –SQL Database as a Service in Azure

Summary:

This blog is based on a feature in Azure that is SQL Database which is used as a service. It is a good service for the management of SQL Database from a minimal up to the huge database sizes and near zero administration. The prerequisite and how to use this service is mentioned below in explanation section.

SQL Database in Azure

Explanation:

As we login to Azure portal and on the left hand side on the options (as in picture above) we can get this SQL Databases which we can use as a service. I have already stated in my previous blog that we can manage SQL database via two options.

  1. SQL Database in Azure
  2. SQL Server in virtual machine

Here I’ll talk about only SQL Database as a service in Azure. SQL Database provides many attractive features such as elastic scale, predictable performance, business continuity, near zero maintenance etc.  We need to keep in our mind that we are using this as a service so there would be no physical server which we need to manage. Almost all the administration would be handled by Azure itself.

SQL database is available in two different models –

  1. Elastic Database pools
  2. Single Database

The key feature of elastic pool model is the ability to share performance across many databases in pool.  Single database might be more appropriate in case of if we are having handful of databases. Both models allows us to adjust performance as necessary with no downtime and provide 99.99% SLA (service level agreement).

Introduction to SQL Database in Azure

Elastic database pools and single database, both models are available in three different service tiers as shown in picture above.

a. Basic      b. Standard         c. Premium 

Here performance is expressed in DTU i.e. Data Throughput Unit within these service tiers. Standard and premium tiers also have been further categorized as (S0, S1, S2, S3) and Premium as (P1, P2, P4, P6, P11). We can start with basic service tier and then can switch to any tiers depending on the usage and performance of the application. To switch to any tier is an online operation and we can use database during that operation.

Service Tiers

Conclusion:

So it was the introduction about the SQL database  as a service available in Azure. I have described its benefits, usages, database models & different service tiers etc.

Hope you enjoyed about this new service!

Regards,

Mirza Husain

Note: Next Blog coming soon based on Interview Q&A

Posted in Azure, Database Administration, Sql Server, windows azure | Tagged , , , , , , , , , , , , | 1 Comment

Microsoft under the Sea (A journey from Cloud to Sea)

Microsoft under the Sea

(A journey from Cloud to Sea)

This blog is based on the current news from Microsoft which is related to the milestone project Natick. I am sharing a brief summary and few important highlights however at the end of this bog there is a link which will redirect to Microsoft site for the complete news. I wish Microsoft a to be more successful  in such projects and serve the whole world.

Datacentre in Sea

Introduction

The underwater datacenter concept was originally presented in a white paper prepared for a Microsoft event called ThinkWeek that encourages employees to share out-of-the-box ideas. Lee’s group was intrigued. Just 12 months after launching Project Natick in July 2014, the team had deployed a lab-built proof-of-concept prototype in calm, shallow waters off California.

Project Natick -Main Highlights

  1. A deep water data center.
  2. Natick is a code name and having no special meaning
  3. Project Natick launched in 2014
  4. First prototype deployed in August 2015 & retrieve after 105 days
  5. The data center contains 864 servers.
  6. The datacentre contains 12 racks
  7. It is assembled and tested in France

Project Importance in the eye of Mr. Satya Nadella

As per Microsoft CEO Satya Nadella this project is  “relevant moonshots” with the potential to transform the core of Microsoft’s business and the computer technology industry. Project Natick is an out-of-the-box idea to accommodate exponential growth in demand for cloud computing infrastructure near population centers.

Project Details

Microsoft tests a datacentre under the sea that’s quick to deploy, could provide internet connectivity for years

It is a milestone project for Microsoft which will help the people who live near to coastel areas in the world. Project Natick is an out-of-the-box idea to accommodate exponential growth in demand for cloud computing infrastructure near population centers.

Project Natick’s 40-foot long Northern Isles datacenter is loaded with 12 racks containing a total of 864 servers and associated cooling system infrastructure. The datacenter was assembled and tested in France and shipped on a flatbed truck to Scotland where it was attached to a ballast-filled triangular base for deployment on the seabed.

At the deployment site, a remotely operated vehicle retrieved a cable containing the fiber optic and power wiring from the seafloor and brought it to the surface where it was checked and attached to the datacenter, and the datacenter powered on.

Backbone of the Internet

Datacenters are the backbone of the Internet, the physical clouds of cloud computing where customers leverage economies of scale to securely store and process data, train machine learning models and run AI algorithms.

Demand for datacenter resources across the computing industry is growing exponentially as corporations increasingly shift their networks and computing needs to the cloud, and internet-connected intelligent devices ranging from smartphones to robots proliferate.

Benefits

  1. Able to deploy a datacentre not more than 90 days.
  2. Natick datacentres are completely recycled, made from recycle material which is in turn to recycle at the end of the life of it.
  3. These datacentres doesn’t consume water to cool it.
  4. Microsoft create sustainable datacentre which leverages locally produced green energy, providing customers with additional options to meet their own sustainable requirements.

Project Natick

Conclusion

Project Natick’s Northern Isles datacenter reaches full submersion as it is lowered foot-by-foot 117 feet to the rock slab seafloor off the coast of the Orkney Islands in Scotland. Microsoft’s Project Natick is a years-long research effort to investigate manufacturing and operating environmentally sustainable, prepackaged datacenter units that can be ordered to size, rapidly deployed and left to operate lights out on the seafloor for years.

To get complete news

Please click here

Hire Me

Posted in Azure, Database Administration, Microsoft News, Other, Sql Server, T-SQL, windows azure | Tagged , , , , , , , , , , , , , , , , | Leave a comment

Comparison – SQL Database & SQL Server in Virtual Machine in Azure

Comparison – SQL Database & SQL Server in Virtual Machine in Azure

Summary:

This blog is based on the comparison of SQL database feature with SQL server installed in virtual machine in Azure. Which option is good or which one we need to opt to full fill our company\business requirements. There are multiple questions regarding this as –

  1. How can we setup database in Azure?
  2. What are the pre-requisite to work on Azure SQL Database?
  3. Can we install SQL Server and create databases there as we do in our premises on a server or VM?
  4. What are the different ways to create databases in Azure?
  5. What are the features available in SQL Database in Azure?
  6. What are the benefits to use SQL Database instead of SQL server in Virtual Machine? Etc. etc.

SQL Database

Explanation:

As Azure is progressing and capturing the IT market by leaps and bounds and companies also have started moving to Cloud and taking services from Azure.

Therefore such questions may arise in the minds of the Database Administrators regarding the databases and their creation/migration/deletion etc. etc.

To answers such questions I am writing this blog to help you out by clearing the doubts. Firstly I would like to tell you that there are two options to work on SQL databases in Azure:

  1. SQL Database in Azure as a service
  2. Create Virtual Machine and use SQL server by installing it

We can work on SQL databases by using either option however there are few differences that’s why both options are available in Azure. First option SQL Database in Azure give us almost all the features and we can use it Platform as a service (PaaS) but we can’t find all the features here.

To get all the features of SQL Server we have to create virtual machine and then install SQL Server into it as we do in our premises. If we compare SQL Database and SQL server in virtual machine, we will find a list of features not available in SQL database. Few common features which are not available in SQL Database  as below:

  1. Windows Authentication
  2. Filestream data
  3. Database Mirroring
  4. Extended Stored Procedures
  5. SQL Server Agent/Jobs
  6. SSRS & SSIS doesn’t support.

SSRS- SQL server reporting Services

SSIS – SQL Server Integration Services

T-SQL Features:

  1. Use statement is not supported. To change databases, a new connection must be established.
  2. Common Language Runtime (CLR)

These are few common features which are not available in SQL Database in Azure. To view the complete list please go through the Microsoft link

https://azure.microsoft.com/documentation/articles/sql-database-general-limitations/.

However the question is then why we need to use SQL Database instead of traditional SQL Server in VM.

The decision to use SQL Database or SQL Server in Virtual Machine can be difficult. There are many factors to consider when choosing between SQL Database and SQL Server in Azure Virtual Machine and these factors may be as database size, existing application versus new application, administration, business continuity etc.

Priorities:

SQL Database is often the right solution for cloud –designed application that are not using unsupported features and need near zero administration.

Azure virtual machine is often the right choice for new or existing application that require a high level of control and customization and which doesn’t require hardware maintenance on premise in future.

Advantages:

Using SQL Database in Azure

  1. Elastic Scale
  2. Predictable Performacne
  3. Business continuity
  4. Near-zero maintenance
  5. Low costCost Difference between SQL Database & SQL Server in VM

    We can understand the cost difference as below:

    Total Cost for SQL Server in VM = Windows Server Cost +SQL Server Licence Cost + Azure Storage cost

    Cost for SQL Database = SQL Database is considered as a service and it is charged per hour basis. There is no cost for VM, licence etc.

    Conclusion

    Therefore I would like to state that it totally depends on the business needs (database size, application type, new vs old application, business continuity etc )whether we need to use SQL Database feature or SQL server in VM as we have seen benefits and explanation above. There are certain limitations with SQL Database feature in Azure while we can use all features of SQL Server without any limit in virtual machine.

    ——————————————————————————————————————————–Hire Me

Posted in Azure, Database Administration, Q & A, Sql Server, windows azure | Tagged , , , , , , , , , , , | Leave a comment

How to learn Microsoft Cloud- Azure

Micrsofot Azure

As we know that technology has changed and Cloud Computing has come into picture. This technology provides easy access with lot of functionalities and is good from the point view of cost. Therefore companies have started to move to cloud technology.

There are several companies which provide Cloud technology however few are popular.

The popular and top most Cloud providers are as:

  1. Microsoft – Azure
  2. AWS- Amazon Web Services
  3. Google– Google Cloud
  4. IBM
  5. Salesforce
  6. Oracle
  7. SAP

Here, I am providing a glimpse of Microsoft cloud which is known as Azure. It was initially released on 1st Feb 2010. Azure is a cloud computing service created by Microsoft for building, testing, deploying and managing applications and services through a global network of Microsoft managed data centers.

To start with Azure,we need to login to https://portal.azure.com/ along with user name and password as below.

Login

Once logged in to the Azure portal website, we will get snapshot of the dashboard of Azure. We can get options on the left hand side to use e.g. SQL Database, Virtual Machines etc. We can search the particular service in search bar.

Azure-Dashboard

Dashboard-Services

Conclusion: To be familiar with the cloud computing and enhance our knowledge , Azure is the platform to learn and move ahead as Microsoft provides free subscription for 30 days with huge amount to use for learning purpose.

So what we are waiting for , move fast and learn new technology.

Looking forward your likes & Comments!!!

Posted in Azure, Database Administration, windows azure | Tagged , , , , , , , , | Leave a comment