Pages

Sunday, 31 May 2020

Stairway to Microsoft Azure SQL Database Part 4 : Azure SQL server , an overview

In the last few posts, we were discussing about database deployment model and purchase model. Out of the three deployment models, managed instance works almost like on-premises SQL server and hence we are not going to discuss much about the managed instance. On the other side, a single database and elastic pool works a completely different ways.

while creating a single database or elastic poor, you need to attach those into the Azure SQL server. In other words, you need to create a SQL server before creating the SQL database. While searching for services in the Azure portal, you can search for 'SQL database' and 'SQL server'. In this post, we are going to discuss about the SQL Server, which is a logical container for your single Azure databases and Elastic Pool.

As per the Azure documentation, "a SQL server is a logical construct that acts as a central administrative point for a collection of databases". At the server level,  you can administrate the following components:
  • Logins
  • Connectivity 
  • Firewall rules 
  • Auditing rules 
  • Threat detection Policy 
  • Auto Failover group
  • Transparent Data Encryption 
  • Auditing 
  • Backup Management 
When you create an empty SQL Server in Azure, it will not cost you anything until you create a single database or elastic pool under this  SQL server. To create an empty SQL server, search for "SQL Server"  in the Azure portal, and follow the instructions. While creating the SQL server, it will ask for a user name and password. This is the only SQL login that you can create from the Azure portal. This login has the highest privilege in the server and can access all databases in that server without providing explicit permission in each database. Azure SQL server does not support window authentication but supports Azure active directory authentication. For enabling the Azure active directory authentication, you need to connect your server with Azure AD. You can achieve this by setting the Active directory Admin from the Azure portal. You can find this option under the Settings section of the server. This can be an individual account or a group. This account also will have the highest privilege and can access all the databases under that server without providing explicit permission.  These are the only two logins that you can create from the Azure portal. There is no option to change the SQL login once you have created it but you can change the Active directory admin at any time. These are the only two logins will have access to all the databases created under this server. There is no option to create server wide login which will have access to all the databases under this server. Still, you can create server wide login and can explicitly create the corresponding user in each database like we used to do in the on-premise installation. 

Azure SQL database provides two types of user :

Login and user: This is the same as we used to do in on-premise installations. Create a login and create corresponding user in each database. For example, connect to master database 

create login njohn with password ='Password@123'
--Connect to DB1
create user njohn from login njohn 
--Connect to DB2 
create user njohn from login njohn 

In this case, the login information is stored at the server level and the password remains same across all databases. While copying the database DB1 and DB2 to another server, you also need to copy the login and need to map the SID properly.

Contained user: These are the users created under the database without having the logins. In this case, the user informations are stored in the database level. After the restoration of the databases, DB1 and DB1 to another server, we don't have to copy the login information separately as the user password and other information are stored in the user database. In this scenario, the same user can have different passwords to connect with each database. For example 


--Connect to DB1
create user njohn with password ='Password1' 
--Connect to DB2 
create user njohn with password ='Password2'


When you create the SQL server, by default a virtual master database will be created. This is the only system database that you can see in the Azure SQL server. If you look into this database, you can see two additional database role added with Azure SQL server :

dbmanager: Members of this database role can create and delete the databases. When a member of the dbmanager role creates the database, becomes the owner of the database, and will have full access to that database as dbo. The members of this role will not have access to the database which is not created by them (owner) unless you explicitly created users under the database for those logins like we do in the on-premise installation. This role exists only in the master database and only the users of the master database can be added into this role. The ideal method is, create a login and create a user for that login and add that user to the dbmanager role. Contained database users in the master database also can be added to the dbmanger role, but they will not have permission to create new databases.
 
loginmanager:  can create and delete logins in the master database. Note that, members of this role does not have the permission to create users in the master database. 

As mentioned earlier, the Azure SQL server is a logical construct and does not represent a specific VM or hardware like on-premise installation. The databases associated with an Azure SQL server, maybe running in different VMs which is not visible for us. In our on-premise installation, the SQL server is installed on  VM and all databases created in that database server will be running on the same VM. Whereas Azure SQL server is a logical server and can deploy the databases under that server in multiple VMs. The below diagram helps you to understand the concept in a better way.

 
We have learned in our earlier post, each Azure SQL database will have one or more standby replica for high availability depending on the service tier. The database name marked in red are standby replica. Azure SQL inherently multi-tenant and it needs to share its resource with multiple clients. You can see one extra database, DB5 which is not there in our on-premises diagram. When you connect to Azure SQL server, "mysqlserver.database.windows.net", based on the database need to be connected, it will redirect the connection to data node hold the primary replica. This is the reason why we need to specify the database name while connecting through SSMS and not allowing us to change the database context using the USE statement. To change the context of the database, it needs to reroute the traffic to different data nodes. If we are not specifying the database name, it will connect to the virtual master database. If the 'datanode1' goes down for some reason, DB1, and DB2 comes online as the primary in Datanode2 and standby replica will get created in another data node. During this time, the connection to these two databases will be broken and the user will get an error message. To overcome these transient errors and to give a smooth experience to the application users, Microsoft suggests to implement retry mechanism for application connecting to Azure SQL. This is also beneficial to 
  • Handle the errors happens while azure perform the maintenance or patch update.  Azure SQL Database is a managed service and Azure performs the regular patch and version update. User does not have control over the date and time of these maintenance windows.
  • When we scale up the SQL database, there is a possibility to failover the database to another node due to a lack of resources in the current node. In that scenario, the existing connection will be terminated.

In our next post, we will discuss about connection architecture and different settings available under the "firewall and virtual network"





21 comments:

  1. Customized Digital Marketing Courses for Beginners with Live Projects & Premium marketing tools.Enroll Now and get trained by Industry Experts. If you are looking for Digital Marketing Courses then visit our website to know more information.
    https://onlineidealab.com/digital-marketing-training/

    ReplyDelete
    Replies
    1. Great Article Cloud Computing Projects

      Networking Projects

      Final Year Projects for CSE

      JavaScript Training in Chennai

      JavaScript Training in Chennai

      The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

      Delete
  2. Packers and Movers Hyderabad Give Certified and Verified Service Providers, Cheap and Best ###Office Relocation Charges, ***Home Shifting, ✔✔✔Goods Insurance worth Rs. 10,000, Assurance for Local and Domestic House Shifting. Safe and Reliable Household Shifting Services in Hyderabad with Reasonable Packers and Movers Price Quotation @ Packers And Movers Hyderabad

    ReplyDelete
  3. احسن شركات جده التي تقدم خدمات نقل عفش مع الفك والتركيب تلك هي ارخص شركة نقل عفش بجدة تمتلك امكانيات كبيرة لأعمال نقل العفش في مدينة جدة وما جاورها من مناطق تابعة لها وقد نضطر قبل نقل العفش الى نظافة المنزل الجديد قبل النقل من الداخل ومن الخارج وذلك بالتواصل مع افضل شركه تنظيف منازل بجده متمكنة بأعمال التنظيف للمنازل الجديدة والمنازل المفروشة مثل ارخص شركه تنظيف موكيت بجده تقدم امكانيات غسيل الموكيت والكنب في الموقع لكي يتم تعقيم المنزل ومن الأفضل ان تقوم بعمل مكافحة للحشرات بواسطة شركه مكافحه الحشرات بجده التي تتعامل في مكافحة الحشرات وتستخدم مبيدات آمنة ومضمونة ونحتاج ايضا الى تنظيف الخزان وذلك بالتعرف على اكبر شركة تنظيف خزانات بجدة تقدم افضل الخدمات الجيدة في تنظيف وتعقيم الخزانات لكي تحافظ على الماء نظيفا ومعقما اطول فترة زمنية ممكنة لكي تكون مياهك نظيفة فان شركة تنظيف فلل المضمونة ومستعدة لاستقبال مكالمتكم في أي وقت وطلب الخدمة من شركة تنظيف الخزانات بجدة

    ReplyDelete
  4. Packers and Movers Chennai Give Safe and Reliable ***Household Shifting Services in Chennai with Reasonable ###Packers and Movers Price Quotation. We Provide Household Shifting, Office Relocation, ✔ ✔ ✔ Local and Domestic Transportation Services, Affordable and Reliable Shifting Service Charges @ Packers And Movers Chennai

    ReplyDelete
  5. الرياض من اهم مناطق المملكة ولا بد ان تكون خدمات التنظيف ونقل الاثاث في الرياض على مستوى لائق باهل العاصمة ونقدم لكم اقوىشركات نقل العفش بالرياض المضمونة وتقدم خدمات رائعة وتستخدم سيارات نقل عفش مخصصة ومبطنة من الداخل وايضا تجد خدمات التنظيف للمنازل والفلل والشقق في افضل شركة تنظيف منازل بالرياض رخيصة وتمتلك خبرة طويلة في اعمال تنظيف الشقق والفلل والقصور وجلي وتلميع جميع انواع البلاط ونقدم ايضا خدمات تنظيف المنازل بالبخار في الرياض تحت اسم اكبر شركة تنظيف كنب بالرياض آمنة بهدف الحصول على تنظيف منزلي شامل للارضيات والشبابيك والمفروشات كالكنب والمجالس والموكيت والسجاد وقد تحتاج ايضا الى تنظيف خزان المياه خاصتك وذلك بالتعاقد مع ارخص شركه تنظيف خزانات بالرياض مجربة لخدمات تنظيف وتعقيم وصيانة لخزان الماء خاصتك وعمل تعقيم للخزان الأرضي والعلوي ولا بد ايضا ان تهتم بتنظيف المنزل من الحشرات مع افضل شركه مكافحه حشرات بالرياض مضمونة لتعقيم المنزل او المسجد والتخلص من الحشرات المزعجة كما في شركات مكافحة الحشرات بالرياض التي تقدم خدمات جيدة بالضمان

    ReplyDelete
  6. تريد نقل عفشك من مكان الى آخر وتريد شركه نقل اثاث بجده مضمونة فانت الآن مع افضل شركة نقل عفش ولو كنت في مكة ابحث عن افضل شركه نقل اثاث بمكة المكرمة لتحصل على خدمات جيدة خاصة بنقل الاثاث في مكة

    ReplyDelete
  7. Once the transaction log backup is completed then you must manually perform the normal TLOG backup. On successful completion of the normal Transaction Log backup your scheduled transaction log backup job to continue performing transaction log backups at regular intervals.

    Nice Read!, thanks for the information. feel free to check out the The Viral Tech

    ReplyDelete
  8. افضل شركة مكافحة حشرات ورش مبيدات في ابها وخميس مشيط
    شركة مكافحة حشرات بابها
    شركة مكافحة حشرات بخميس مشيط
    توااصلو بنا ...

    ReplyDelete

  9. Cool one! Interesting informations over this web. It is pretty worth enough for me. Max Cash For Cars Provide Best Car Price With Free Pickup. So if you are planning to Sell my car Brisban, contact us or fill our online form. We buy cars and pay quick cash for any scrap, old, or damaged vehicle up to $9,999. We Pay Top cash for cars Caboolture in Any Condition.

    ReplyDelete
  10. Thanks, foг ones marvelous posting! I genuinely enjoyed reading it, you miggһt Ƅe a great author. Au Cash for Cars is the Gold Coast’s No.1 Cash For Cars Buyer. Instant cash on the spot up to $11,000. 100% customer satisfaction guaranteed. Sell an old car today and earn top cash for cars Gold Coast with us.

    ReplyDelete
  11. Good Article thanks for sharing. Metal Biz Recyclers are one of Queensland’s leading cash for scrap cars, located in Brisbane. Across our three Brisbane scrap yards in Rocklea, Slacks Creek, and Geebung, we recycle all types of scrap metals – including scrap cars, unwanted cars, 4x4s, busses, and trucks. If you have a vehicle that needs removing or disposing of, Metal Biz Recyclers will come and collect your vehicle or vehicle body for free – and offer you cash for cars brisbane!

    ReplyDelete
  12. Wow, that was strange. I just wrote a really long comment but after I clicked submit my comment didn't show up. Grrrr. Get the top cash for cars brisbane and unwanted car today at the same time as you can get up to $11,000 on your unwanted free car removal in Brisbane, Gold Coast & Sunshine Coast. With over years of experience, we are Brisbane’s leading top dollar car removers who pay cash for cars on the spot.

    ReplyDelete
  13. Thank you for providing this blog really appreciate the efforts you have taken into curating this article if you want you can check out data science course in bangalore they have a lot to offer with regards to data science in terms of training and live projects.

    ReplyDelete
  14. Packers and Movers Chennai Give Safe and Reliable ***Household Shifting Services in Chennai with Reasonable ###Packers and Movers Price Quotatin. We Provide Household Shifting, Office Relocation, ✔✔✔Local and Domestic Transportation Services, Affordable and Reliable Shifting Service Charges @ Packers And Movers Chennai

    ReplyDelete