{"id":632,"date":"2020-04-13T19:08:44","date_gmt":"2020-04-13T19:08:44","guid":{"rendered":"http:\/\/jackofalltradesmasterofsome.com\/blog\/?p=632"},"modified":"2020-04-14T17:26:12","modified_gmt":"2020-04-14T17:26:12","slug":"modern-data-architecture-part-4-setting-up-a-sql-server","status":"publish","type":"post","link":"http:\/\/jackofalltradesmasterofsome.com\/blog\/2020\/04\/13\/modern-data-architecture-part-4-setting-up-a-sql-server\/","title":{"rendered":"Modern Data Architecture \u2013 Part 4 \u2013  Setting up a SQL Server"},"content":{"rendered":"\n<p>Modern Data Architecture \u2013 Part 4 \u2013  Setting up a SQL Server<\/p>\n\n\n\n<p>Before we move on to loading data into our Azure Data Lake, we will need a data source to simulate pulling data from as to mirror a production system. For this exercise, we will walk through creating your own SQL server database and loading it with sample data. <\/p>\n\n\n\n<p>This is the\nfirst resource we care creating that will bill ongoing in the background, so we\nwill want to be sure we deprovision this resource once we are done with it. The\ntier we will be using is the basic database at $5.00 a month, so this lab\nshould only cost a few cents.<\/p>\n\n\n\n<p><strong>Prerequisites<\/strong><\/p>\n\n\n\n<p>You will want to install and set up SQL Server Management Studio prior to this completing this section <\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"624\" height=\"166\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-21.png\" alt=\"\" class=\"wp-image-667\" srcset=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-21.png 624w, http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-21-300x80.png 300w\" sizes=\"auto, (max-width: 624px) 100vw, 624px\" \/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Create a SQL Server and SQL Database in Azure<\/h2>\n\n\n\n<ol class=\"wp-block-list\"><li>In\nthe search bar, search for \u201cSQL Server\u201d<\/li><\/ol>\n\n\n\n\n\n<ul class=\"wp-block-list\"><li>Select \u201c+ Add\u201d to create a new SQL Server<\/li><li>Fill in the necessary details and select \u201cReview and Create\u201d<ul><li>Subscription \u2013 The default subscription you set up for the demo<\/li><\/ul><ul><li>Resource Group \u2013 The training group resource group \u201ctraining_resourcegroup_<em>yourname<\/em>\u201d you created for the lab<\/li><\/ul><ul><li>Database Details<ul><li>Database Name \u2013 \u201ctraining_sqldatabase_yourname\u201d<\/li><\/ul><ul><li>Server \u2013 Create New<ul><li>Server Name \u2013 \u201ctraining-sqlserver-<em>yourname<\/em>\u201d<ul><li>Underscores are not allowed, use dashes<\/li><\/ul><\/li><\/ul><ul><li>Server Admin Login \u2013 create a unique id <ul><li>Record this somewhere as we will need it for later in the lab<\/li><\/ul><ul><li><em>sqlserveradmin<\/em> for this lab sample if needed<\/li><\/ul><\/li><\/ul><ul><li>Password \u2013 secure password <ul><li>Record this somewhere as we will need it for later in the lab<\/li><\/ul><\/li><\/ul><ul><li>Location \u2013 US East 2<\/li><\/ul><\/li><\/ul><\/li><\/ul><\/li><li>Want to Use SQL Elastic Pool \u2013 No<\/li><li>Compute + Storage<ul><li>Select Configure. <\/li><\/ul><ul><li>Select Basic. This will ensure you are using the cheapest tier of databases to mitigate costs.<\/li><\/ul><\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"624\" height=\"289\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-22.png\" alt=\"\" class=\"wp-image-668\" srcset=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-22.png 624w, http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-22-300x139.png 300w\" sizes=\"auto, (max-width: 624px) 100vw, 624px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"624\" height=\"286\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-23.png\" alt=\"\" class=\"wp-image-669\" srcset=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-23.png 624w, http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-23-300x138.png 300w\" sizes=\"auto, (max-width: 624px) 100vw, 624px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\"><li>Once completed, the SQL Server and Database resource will be available from the dashboard. Click on the server to navigate to details.<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"624\" height=\"167\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-24.png\" alt=\"\" class=\"wp-image-670\" srcset=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-24.png 624w, http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-24-300x80.png 300w\" sizes=\"auto, (max-width: 624px) 100vw, 624px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Setting Security and Firewalls<\/h2>\n\n\n\n<ul class=\"wp-block-list\"><li>Click on the server name to begin to configure database rules<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"624\" height=\"221\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-25.png\" alt=\"\" class=\"wp-image-671\" srcset=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-25.png 624w, http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-25-300x106.png 300w\" sizes=\"auto, (max-width: 624px) 100vw, 624px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\"><li>Select \u201cShow Firewall Settings\u201d<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"624\" height=\"194\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-26.png\" alt=\"\" class=\"wp-image-672\" srcset=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-26.png 624w, http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-26-300x93.png 300w\" sizes=\"auto, (max-width: 624px) 100vw, 624px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\"><li>Select \u201cAdd client IP\u201d to whitelist the current IP of your computers location and select \u201cSave\u201d. Add any other IP that may need access. A simple google search of \u201cwhat is my IP\u201d will return the IP address required.<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"496\" height=\"265\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-27.png\" alt=\"\" class=\"wp-image-673\" srcset=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-27.png 496w, http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-27-300x160.png 300w\" sizes=\"auto, (max-width: 496px) 100vw, 496px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\"><li><\/li><\/ul>\n\n\n\n<ol class=\"wp-block-list\"><li>Set Allow Azure Services and resources to access server to \u201cYes\u201d. This will allow Azure Data Factory and other data connections to connect.<\/li><\/ol>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"396\" height=\"114\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-28.png\" alt=\"\" class=\"wp-image-674\" srcset=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-28.png 396w, http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-28-300x86.png 300w\" sizes=\"auto, (max-width: 396px) 100vw, 396px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Creating a Sample Database<\/h2>\n\n\n\n<ol class=\"wp-block-list\"><li>Open SQL Server Management Studio. In the login prompt, enter the server name from Azure and the username and password that was configured. Be sure to use \u201cSQL Server Authentication\u201d in the Authentication Setting<\/li><\/ol>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"410\" height=\"251\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-29.png\" alt=\"\" class=\"wp-image-675\" srcset=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-29.png 410w, http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-29-300x184.png 300w\" sizes=\"auto, (max-width: 410px) 100vw, 410px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\"><li>Upon\nsuccessful login, your databases should be available<\/li><\/ul>\n\n\n\n<ul class=\"wp-block-list\"><li>Select \u201cNew query and set the database to your training database<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"392\" height=\"268\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-30.png\" alt=\"\" class=\"wp-image-676\" srcset=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-30.png 392w, http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-30-300x205.png 300w\" sizes=\"auto, (max-width: 392px) 100vw, 392px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\"><li>From the Sample Data Folder, run the following two scripts. Be sure the database connection does not revert back to \u201cmaster\u201d as updated in the previous step.<ul><li>BikeStores Sample Database \u2013 create objects<\/li><\/ul><ul><li>BikeStores Sample Database &#8211; load data<\/li><\/ul><\/li><li>Tables will now appear in your database which you can query and review once the scripts complete running.<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"264\" height=\"330\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-31.png\" alt=\"\" class=\"wp-image-677\" srcset=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-31.png 264w, http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-31-240x300.png 240w\" sizes=\"auto, (max-width: 264px) 100vw, 264px\" \/><\/figure>\n\n\n\n<p>Be sure to check out my full online class on the topic. A hands on walk through of a Modern Data Architecture using Microsoft Azure. For beginners and experienced business intelligence experts alike, learn the basic of navigating the Azure Portal to building an end to end solution of a modern data warehouse using popular technologies such as SQL\u00a0Database, Data Lake, Data Factory, Data Bricks, Azure Synapse Data Warehouse and Power BI. Link to the class can be <a href=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/2020\/04\/08\/modern-data-architecture-using-microsoft-azure-online-class-and-free-ebook\/\">found here<\/a> or directly <a href=\"https:\/\/www.udemy.com\/course\/modern-data-architecture-using-microsoft-azure\/learn\/lecture\/18527998#overview\">here<\/a>.  <\/p>\n\n\n<p><br>\n<br>\n<!--StartFragment--><\/p>\n\n\n<p><a href=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/2020\/04\/08\/modern-data-architecture-part-1-navigating-the-azure-portal\/\">Part 1 &#8211; Navigating the Azure Portal<\/a><\/p>\n\n\n\n<p><a href=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/2020\/04\/13\/modern-data-architecture-part-2-resource-groups-and-subscriptions\/\">Part 2 &#8211; Resource Groups and Subscriptions<\/a><\/p>\n\n\n\n<p><a href=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/2020\/04\/13\/modern-data-architecture-part-3-creating-data-lake-storage\/\">Part 3 &#8211; Creating Data Lake Storage<\/a><\/p>\n\n\n\n<p><a href=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/2020\/04\/13\/modern-data-architecture-part-4-setting-up-a-sql-server\/\">Part 4 &#8211; Setting up an Azure SQL Server<\/a><\/p>\n\n\n\n<p><a href=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/2020\/04\/13\/modern-data-architecture-part-5-loading-data-lake-with-data-factory\/\">Part 5 &#8211; Loading Data Lake with Azure Data Factory<\/a><\/p>\n\n\n\n<p><a href=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/2020\/04\/13\/modern-data-architecture-part-6-configuring-and-setting-up-data-bricks\/\">Part 6 &#8211; Configuring and Setting up Data Bricks<\/a><\/p>\n\n\n\n<p><a href=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/2020\/04\/13\/modern-data-architecture-part-7-staging-data-into-data-lake\/\">Part 7 &#8211; Staging data into Data Lake<\/a><\/p>\n\n\n\n<p><a href=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/2020\/04\/14\/modern-data-architecture-part-8-provisioning-a-synapsis-sql-data-warehouse\/\">Part 8 = Provisioning a Synapse SQL Data Warehouse<\/a><\/p>\n\n\n\n<p><a href=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/2020\/04\/14\/modern-data-architecture-part-9-loading-data-synapse-data-warehouse\/\">Part 9 &#8211; Loading Data into Azure Data Synapse Data Warehouse<\/a><\/p>\n\n\n<p><!--EndFragment--><br>\n<br>\n<\/p>\n\n\n<p>Modern Data Architecture \u2013 Part 4 \u2013  Setting up a SQL Server<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Modern Data Architecture \u2013 Part 4 \u2013 Setting up a SQL Server Before we move on to loading data into our Azure Data Lake, we will need a data source to simulate pulling data from as to mirror a production system. For this exercise, we will walk through creating your own SQL server database and [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":624,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,28,11,85],"tags":[],"class_list":["post-632","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-analytics","category-azure","category-data-warehouse","category-sql-server"],"_links":{"self":[{"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/632","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/comments?post=632"}],"version-history":[{"count":5,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/632\/revisions"}],"predecessor-version":[{"id":767,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/632\/revisions\/767"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/media\/624"}],"wp:attachment":[{"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/media?parent=632"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/categories?post=632"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/tags?post=632"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}