UNISYS-7TH TRAINING IN AZURE SQL DATABASE
Azure SQL Database
Microsoft Azure Cloud SQL Database is the cloud database service for developers. Explore Azure SQL Database 2016, including service tiers, elastic database pools and scaling, and how to create and connect to an Azure SQL database.
Table of Contents
Service Tiers
[Topic Title: Service Tiers. Your host for this session is Aaron Sampson.] Now if you are considering an implementation of Azure SQL Database, then one of the decisions you face is the appropriate service tier. And what you are doing here is effectively trying to identify the amount of resources overall that you will require while trying to balance that with the cost. So as of the time of this recording you see there are four tiers available, Basic, Standard, Premium, and Premium RS. Now that one is quite new and we'll talk about some of the differences in a moment. But again, overall, any given tier supplies x amount of resources. And as you might imagine, when you move up to a higher tier, more resources become available. But of course, it costs more. So it's always a matter of trying to find the balance.
So if we look at some of the actual numbers, if you will here. [The database values are provided for reference in the Service Tier Features section at the end of the transcript.] The maximum database size is restricted, as is the max storage in an elastic pool. The maximum number of databases per pool and the backup retention in terms of the days. And obviously, this matrix just gives you a bit of an idea as to what you can expect to see. Now do take note that it's not always the case that every value will increase as you move up in tier. For example, take note that the Standard tier offers 400 databases per pool, as does the Basic. But Premium and Premium RS only allow 50 per pool, and the maximum storage for the pool is also not as high. But this typically tends to come down to the fact that the higher levels are much more focused on the performance characteristics. Much more processing and memory, not necessarily the amount of storage space available. You can have very large databases that don't require a lot of processing power. So it's not always the case that every value will always increase. So you want to try to find, basically, just where you fit in. But in terms of the max database size, certainly a lot of variation there. Basic is only 2 gigabytes per database, Standard 250, Premium all the way up to 4 terabytes.
Now Premium RS scales back a little bit compared to Premium, so that one clearly is the highest level. So Premium RS, again very new, is a little more focused on the resources that relate to performance of processing and memory. But where they tend to pull back a little bit is on fault tolerance redundancy, components like that. So if it's a database such as a data warehouse, typically the source of information for a data warehouse can be recovered in the event of a failure. You have other sources of information feeding the warehouse, so even if it were entirely lost, there is a pretty good chance you can recover everything. So it scales back a little bit on the redundancy and focuses a little more on the performance. So the max storage in an elastic pool, this is where you can combine multiple databases in a flexible resource pool so that underutilized resources from one database can be allocated to one that is over-utilized at that point in time. And basically just try to find a balance. So again, the maximum storage available, 117 for Basic, 1200 for Standard, Premium 750, and Premium RS 750 as well. And then the maximum databases per pool, 400 for both Basic and Standard. And again, you see only 50 for the Premium levels. But as mentioned, they're focused more so on the high performance characteristics. And then the backup retention, 7 for Basic, 35 for everything else.
So when it comes to performance, the overall value that is used by Azure SQL Database is known as the DTU, Database Throughput Unit. And this is just an index value that really takes into consideration all resources that are typically associated with performance. So certainly memory, and processor, and disk speed access. For example, solid state disks versus traditional hard drives, things along those lines. [The performance values are provided for reference in the Service Tier Performance Levels section at the end of the transcript.] So you can see that the Basic tier is quite low, only 5 DTUs, Standard 10-100, Premium 125 all the way up to 4000. So a significant improvement there. Premium RS 125 starting, but only goes as high as 1000. And then the max concurrent workers, again quite low for Basic, Standard 60-200, Premium 200-6400, and then Premium RS again starts at the same value but not quite as high. The maximum concurrent logins, again you see the associated values there, more or less the same as concurrent workers. And the concurrent sessions as well. So as mentioned, it really is just a matter of trying to determine what is required for your particular solution. And the tier can be changed so you don't have to select the given tier and basically stay that way forever. But again, you need to consider what the costs are going to be. Now when you do go into Azure to create any given database and you choose the tier, it does give you some estimates per month based on the settings that you have chosen. So it gives you an idea, which of course is only an estimate, but at least it's a place to begin. So hopefully, you can find an appropriate service tier that meets both your resource requirements as well as your budgetary constraints.
Service Tier Features
Basic: Maximum database size (GB) = 2, Maximum storage in elastic pool (GB) = 117, Maximum databases per pool = 400, Backup retention (days) = 7.
Standard: Maximum database size (GB) = 250, Maximum storage in elastic pool (GB) = 1200, Maximum databases per pool = 400, Backup retention (days) = 35.
Premium: Maximum database size (GB) = 4000, Maximum storage in elastic pool (GB) = 750, Maximum databases per pool = 500, Backup retention (days) = 35.
Premium RS: Maximum database size (GB) = 500, Maximum storage in elastic pool (GB) = 750, Maximum databases per pool = 50, Backup retention (days) = 35.
Service Tier Performance Levels
Basic: Maximum DTUs = 5, Maximum concurrent workers = 30, Maximum concurrent logins = 30, Maximum concurrent sessions = 300.
Standard: Maximum DTUs = 10 to 100, Maximum concurrent workers = 60 to 200, Maximum concurrent logins = 60 to 200, Maximum concurrent sessions = 600 to 2400.
Premium: Maximum DTUs = 125 to 4000, Maximum concurrent workers = 200 to 6400, Maximum concurrent logins = 200 to 6400, Maximum concurrent sessions = 30000.
Premium RS: Maximum DTUs = 125 to 1000, Maximum concurrent workers = 200 to 1600, Maximum concurrent logins = 200 to 1600, Maximum concurrent sessions = 30000.
Common Service Tier Use Cases
[Topic Title: Common Service Tier Use Cases. Your host for this session is Aaron Sampson.] Now, in this presentation, we'll take a look at some use cases for each tier available in Azure SQL Database, beginning with the Basic and Standard. And at the Basic tier, you are probably looking at just a very small database that maybe only has a single active operation. Now it's not limited to just a single, that's just something that you might see fairly commonly at this level. And certainly, ideal for testing and development and just trying to maybe get a feel for what is available within the Azure SQL Database environment. Then if you decide to move up to the Standard service tier, then we do see much better support for multiple queries, low to medium IO requirements, still ideal for cloud applications and even web applications, that again, just aren't particularly high in terms of IO. But, you know, that is still a fairly relative term.
You might only decide that your overall requirements are too low after you actually implement your application and you see that it's underperforming. In which case, you absolutely can increase your tier. But, generally, it's a good place to start. Now, the Premium service tiers, again Premium on its own, absolutely supports high transaction volume and very high performance. Supports multiple users and ideal for mission-critical applications because it does offer a lot of availability features so that your uptime is absolutely maximized. And again, very new on the scene is Premium RS. Which still supports a very high transaction volume, but sacrifices a little bit in terms of availability. So when that is not critical, this might be a better option because it's not as expensive. So still ideal for high-performance and analytical workloads. Because, typically with analytical workloads, the thinking is that the sources of data for whatever is being analyzed can be reconstructed or re-accessed even if the entire database was lost. Traditional data warehouses tend to receive their data from other sources.
So even if you lose the whole warehouse, you could probably reconstruct it. So this is where you can sacrifice some of the requirements in favor of performance. So, again, Premium RS might be a good option if you just aren't concerned with the availability but you still want high performance.
Elastic Database Pools
[Topic Title: Elastic Database Pools. Your host for this session is Aaron Sampson.] If you are implementing a solution in Azure SQL Database, and you are implementing multiple databases as part of that solution, then another option you can choose is the elastic pool. This allows you to manage and scale multiple databases. And is particularly useful when the demand on those databases is quite unpredictable. In other words, you have a very peaks and valleys kind of scenario if you will. The performance can go way up in terms of demands and then go way down. And you really don't have any good way to predict that. So this can help arrive at a much more cost effective solution by spreading the resources around. Rather than having one database that is very over-allocated in terms of its resources. And then really just doing nothing with them for a fair amount of time. We can share the resources amongst all of the databases. So they reside on single server and share the fixed resources of that server. But with that you know that the costs are fixed as well. And then each database that is configured in the pool is able to share its resources with other databases. So that when database A is being over-utilized, it can borrow resources from database B because it's being underutilized. And you can just level those peaks and valleys off. In terms of creating elastic pools, you can of course do so using the Azure portal.
Or you can use PowerShell cmdlets or Transact-SQL and there are even some other options. But they're fairly simple to create in the portal. And the overall performance measurement is then transformed, if you will, to what's known as an eDTU. [or elastic Database Transaction Units] Database throughput units were discussed earlier and it's just an overall measure of performance. But once you put them into an elastic database pool, then it's referred to as an eDTU. Which basically indicates how much of the elastic pool is being allocated at any given time. So with respect to some of the features, it autoscales and this gives you, of course, increased flexibility. You don't have to configure anything to say, okay, allocate x amount more to database B when it's low and database A is high in terms of demand. So this happens automatically and the consumption of the eDTUs adjust to meet the demand. So again as B goes up in terms of its requirements and A goes down, A will allocate to B automatically. There is no database downtime required for the scale up or scale down. Everything happens in real time and live. And the databases themselves can be actively added or removed from an elastic pool at any time. The suitability as to whether or not it should even be in a pool certainly depends on the activity and/or inactivity patterns of the database.
So again good candidates are these ones that exhibit the peaks and valleys in terms of performance. If you do have a system that is very level then that might be better suited to its own configuration, you know, not in a pool. And the shared resources to simplify your management tasks. You don't have to do anything with respect to configuring these resources. They are allocated dynamically. And ultimately, this results in your overall expenditures becoming much more predictable. Because you still allocate x amount of resources to the pool. You know what the cost of that is going to be each month. Then the databases within the pool look after themselves in terms of allocating those resources. So you don't end up with the associated peaks and valleys in your bill that you might see in the databases. So ultimately, if you do have multiple databases with significant variation, in terms of their resource allocation and requirements, then you might definitely want to look into implementing an elastic pool.
Elastic Scale
[Topic Title: Elastic Scale. Your host for this session is Aaron Sampson.] If you have implemented a database solution in Azure, you, of course, may find yourself needing to scale at some point to deal with increased workload or just different configurations that are necessary. And you can absolutely do so using the Elastic Database tools to help you take advantage of what is practically unlimited resources when it comes to what you have at your disposal in Azure. So we'll take a look at some of the tools themselves and then some of the approaches for scaling. But the first tool is the Elastic Database client library, which is a feature that allows you to create and maintain what's known as sharded databases. And we'll talk about those in a moment as well. It's another term for horizontal partitioning. So it is just dividing a database in some manner, and again, we will discuss that in a moment. The Elastic Database split-merge tool moves data between sharded databases. So once you have implemented your sharding, your partitioning, this can be useful for moving data. Perhaps, if you had a single tenant scenario that is moving to a multitenant, or vice versa, you can use the split-merge tool. Elastic Database jobs. Now at the time of this recording this feature is actually still in preview. But this is used to manage just large numbers of Azure SQL databases, so you can perform various administrative tasks such as managing credentials, handling updates, gathering performance data, either on a per tenant, or a per database basis.
The Elastic Database query tool allows you to run T-SQL queries that span multiple databases, so that you can report using tools such as Excel or Power BI. And elastic transactions allow you to run transactions that also span several Azure SQL databases. So again, you can gather information from multiple databases at the same time. So again, those are some of the tools that you can help to implement and manage multiple database configurations. But when it comes to scaling, there are two options, you can scale horizontally or vertically. And vertical scaling is, of course, also referred to as scaling up or down. So what you are doing here is just taking your existing database and changing the edition to a higher edition, or a lower one as the case maybe, to simply accommodate the increased demand. So, effectively, it's more powerful databases, more powerful servers, but it's all still the same database. So that's the scaling up, and of course you can go the other way as well. And then the horizontal scaling, also known as sharding, is the division of databases into multiple different copies of that database, if you will. And that also can be done in a couple of different ways.
You can create different schemas, for example, each schema hosts a different tenant or you can simply just divide the data by some kind of criteria. But it's multiple databases that have the same structure. So it's just a matter of ensuring the data is isolated in some way, shape, or form. But the structure of the database remains the same, and you implement multiple instances of that database, or multiple schemas, just something to keep them divided. But now we are talking about multiple databases, possibly, multiple servers. And that is the horizontal or sharding scaling, also referred to as scaling out. [For example, each database has a shard allocated to it. So Database 1 has shard 1, Database 2 has Shard 2, and so on for as many databases as are required.] So some use cases then for sharding. If you have a large amount of data that you really feel is too much for a single database, then you might look at a scaling out scenario, a sharding configuration. If you feel that you have an excessive workload transaction throughput, again, for just a single database, then of course, you can scale out as well. The physical isolation, in most cases, this is one of the primary reasons. If you are supporting multiple tenants, for example, within a single database configuration, again, you need to ensure that tenant A has their data separate from tenant B. So a separate copy of the same database structure or different schemas, but it's all still the same database, just in some way, isolated from the others.
If you require varied geographic locations for the databases, again, because you have these shards, these pieces, they can really be located anywhere. And if you also have any kind of custom database organization that really is required for any reason, then you might choose to implement it via sharding. And finally, if you are just considering a single versus a multitenant sharding pattern, if it is just a single tenant, then you might be looking at just implementing a single shard for just that tenant. And then if it's multitenants, of course, you start creating these additional instances. And each one is still identical in structure, but of course, the data in shard A belongs to tenant A, the data in shard B belongs to tenant B, and so on, and so on. So it certainly depends on the circumstances, but one way or another, you are implementing some kind of scaling pattern. So that we can handle more data, more tenants, more processing, a higher workload, anything along those lines, by implementing these multiple shards. But one of the differences is that first of all, the webjob is tied to that web application. Runbooks aren't, they can talk to any type of resource. And remember that the webjob can be written in many different languages. It doesn't have to be PowerShell, it could be a batch file, you could create it using Node.js, and so on. So while webjobs for a web application and Azure runbooks are similar, they're not quite the same. So make sure you know when you should choose one over the other, given a specific scenario.
Tempdb Files
[Topic Title: Tempdb Files. Your host for this session is Aaron Sampson.] Now when fine tuning the performance of your SQL databases, you will also want to consider some optimal settings for the tempdb. And they include implementing the SIMPLE recovery model. And this can help to improve overall performance, because it automatically reclaims log space to keep the overall space requirements and the amount of growth to a minimum. And along the lines of growth, you do want to implement automatic file growth on the tempdb, so that it does grow when necessary, and you don't have to worry about it filling up. But you also want to be mindful, of the FILEGROWTH increment, and the original size of your files. Now the overall size will depend on what is happening in your environment, and there's no real set value with respect to how large the file should be. But in essence, you can see that if the database file's between 0 to 100 megabytes, then the growth increment should be around 10 and 100 to 200 around 20.
So it's remaining fairly consistent at around 10%. So once you see the size going up over 200, you can switch your increment to just a percentage value. But all of those, of course, are around a 10% increment. [In summary, a File size of 0 to 100MB has an increment of 10MB, a File size of 100 to 200MB has an increment of 20MB, and a File size of 200MB+ has an increment of 10%.] So it's something you want to be mindful of, but basically using those values, you should get a reasonable performance from your tempdb. Now some other key recommendations for optimizing the performance, you can certainly pre-allocate some space. And this ensures that it is not always having to autogrow every time the server or the service restarts. So again, you do have to come up with a reasonable value as to the size, but you generally do still want autogrowth on. So pre-allocate what you feel to be enough space so that it doesn't spend all of its time allocating that space every time it starts or restarts. Maximizing bandwidth, in this case actually refers to disk bandwidth.
And this comes down to the number of files that you allocate for the tempdb. And the general recommendation here is to allocate one file per processor. And it's also not a bad idea to keep multiple files on multiple disks. And ideally, your best performing disks as well, which comes to the final bullet, location. But again, the recommendation, typically one file per processor. And standardizing the file size for each file is also a good idea, just helps to keep things consistent. And again with the implementation of multiple files, you can reduce contention by for example, reading from one location and writing to another. And again, considering where that location is, if you have access to faster disk subsystems, such as solid state drives compared to traditional hard drives. Then placing the tempdb on the solid state drives will certainly give you better performance. So it's not anything that you have to monitor all the time, day in and day out. But certainly something to consider and check from time to time to ensure that the settings for the tempdb are in an optimal configuration.
Creating an Azure SQL Database
[Topic Title: Creating an Azure SQL Database. Your host for this session is Aaron Sampson. Microsoft Azure is open on the Dashboard. Options on the toolbar include New dashboard, Edit dashboard, and Share. Options in the Object Explorer include All resources, Resource Groups, and App Services. All resources, Service health, and Get started options display in the main view pane.] In this demonstration, we'll see how to get started with creating a new Azure SQL database. And from your main Dashboard page you have a couple of options. You can just select SQL databases here from your Object Explorer or you can just click on New. In fact, you'll also see there's a getting started shortcut right there to SQL databases. [The presenter refers to the Get started pane, which includes a SQL Database option.] It really doesn't matter. Let's click the New option just so we can follow the entire path, if you will. [A New menu is displayed.] And under New, it's in the category Databases, of course. [Other options include Compute, Networking, and Storage.] And we can expand this, [A flyout menu opens.] and the very first option is SQL Database. [The SQL Database pane opens.] Now when you are creating an Azure SQL database, you are, in essence, only creating the database. In other words, we're not particularly concerned with the server. We aren't going to see a virtual machine where we can log into the desktop and configure the server like you can with an actual Azure virtual machine. But there still needs to be a server somewhere. So if you are just getting started with this, you will see that the Server, right here, will require you to create a new one. [The presenter refers to the currently selected server. In this case, azdbserver 1 (South Central US).] If you have already created a database, then there has to have been a server for that database. So even though the database may have been deleted, the server may still be there. So again, this depends on where you are in your implementations. But you can see, I already have a server because I created the database already.
But you do not have to place this database on the same server. That's entirely your call. But just to quickly see what the options are here, [He clicks the Server option.] I can absolutely choose my existing server or, of course, I can create a new one. [The existing server displays along with a Create a new server option.] So if I click on Create new I just have to assign a Server name, the admin login, a Password, and of course, the Location. [There is also an Allow azure services to access server checkbox, which is selected by default.] And really, that's all that you need to specify. Again, you aren't really concerned so much with the server, but rather just the database itself. So I'll go ahead and create mine using my existing server, but by all means, feel free to create a new one. [He closes the Server pane. A message displays noting that unsaved edits will be discarded. He clicks OK.] So, of course, we need a database name. So, let's call this AZ, for Azure, TestDB2. I already have DB1, and that is why I already have this server right here. Then, simply choose your Subscription. [The Subscription is currently set to Pay-As-You-Go.] Because I have a Server I already have a Resource group, this gathers together related resources. So the server and the database are in the same resource group, later I might add a web server and a web application that are all going to be connecting to this database, so I would place them in the same resource group as well. So again, I'll use my existing, but you can absolutely create a new one if you want to. [He refers to the Use existing and Create new radio buttons.] Just give it a name, and you're set to go. [The Resource group is named AZRG1.]
The source can be a blank database, [He refers to the Select source option.] or in fact you do see they offer a Sample light version, if you will, of AdventureWorks. [He clicks the Select source drop-down button. The menu contains options for: Blank database, Sample (AdventureWorksLT), and Backup.] So if you're just testing, then that might be a good option. I'll just go with a blank database and I'll choose my existing Server. I won't bother with an elastic pool at this point. [He refers to the Want to use SQL elastic pool field. There are Yes and Not now radio buttons.] This typically is, again, when you have multiple databases and you want to balance their resources dynamically. And perhaps, one of the more significant considerations is the Pricing tier. Now it defaults to Standard S2, there are various levels within each tier, 50 DTUs, or Database Throughput Units, and a size of 250 GB. Well, if that's perfectly acceptable then, by all means, feel free to leave it that way, but if you want to change it just click on that option. [The Configure performance page opens.] And you can see here are the available tiers, Basic, [For infrequent access and less demanding workloads.] Standard, [For most production workloads.] Premium [For IO-intensive workloads and highest availability.] and PremiumRS, [For IO-intensive workloads but with a limited availability guarantee.] which is still in preview. But this is entirely your call, how resource intensive is this database going to be? So if you're just testing and developing, you might want to just go with Basic, but you can see the values are quite small. [The DTU value is 5.] Again the DTUs, the Database Throughput Unit, is an overall collection of performance metrics. Processor, memory, disk speed, access, things along those lines. And you can see in fact there's a link there to click on that says, What is a DTU? But it's just the overall performance. So the more DTUs, the better your performance. And then of course the Storage size, and at Basic it maxes out at only 2 GB. So clearly, not particularly high in terms of resources, but look at the price, [It's $4.99.] not even $5 a month. So clearly, this is where you can save some money.
Standard, for most production workloads, and again of course, you can adjust whichever values you feel appropriate here. And you can see, of course, the cost is changing as I make the changes here. [For example, 10 DTU has a cost of $15 a month, whereas 50 DTU has a monthly cost of $75.02.] The Storage, of course, can be adjusted as well. [Storage can be adjusted from 100MB to 200GB.] And again, this is entirely your call. But it's nice that it does give you that estimate in terms of monthly cost. So as you adjust you see what the price is, and you can see now once we get up into the very high DTUs a very high cost associated with that. [For example, in the Premium option 1000 DTU has a monthly charge of $3720.] So well into the thousands now compared to Basic, only $5. So certainly, try to choose something that is appropriate, but Standard does suffice for most production workloads. So again that's your call, simply choose anything you want. I'll just go with the Basic option at this point and click on Apply, and that will set your tier. You absolutely can change your tier later on if you want to. There are a few remaining values here, you can see, well actually just one. But the Collation, and I'll just leave this as is, that's perfectly fine, but you can choose a different one if you want to. [Collation is set to SQL_Latin1_General_CP1_CI_AS.]
Then we can Pin to the dashboard [He selects the Pin to dashboard checkbox.] and just simply click on Create and that's it. That is going to go ahead and start allocating that database. And it won't take very long, maybe a couple of minutes. [A message displays that the new database is busy deploying.] But ultimately, that is all that you need to do, just decide on the name of the database, configure a server, choose whether or not it's going to be part of an elastic pool. And, of course, choose the appropriate service tier. And, again, if you are just testing and developing, I would certainly recommend to keep it at the lower levels. But if you do need to actually test some workloads to see what the performance is, then sure, you might need a little higher level. But if you're just assessing features, doing proof of concept for example, then certainly the lower ones might suffice. Shouldn't take much longer for this to complete. You can see the notification here shows that it is in progress so it shouldn't take particularly long. [He clicks the Notifications icon. A Deployment started message displays.] But depending on various other things that might be going on within your Azure environment, it might take a few more minutes. But there we go, that has succeeded. It's bringing us straight to this page automatically, [The AZTestDB2 page opens on the Overview section. Information such as the Resource group, Server name, and Status displays.] and just showing us our basic overview for this new database. But that's all there is to creating a new Azure SQL database.
Connecting to an Azure SQL Database
[Topic Title: Connecting to an Azure SQL Database. Your host for this session is Aaron Sampson. The AZTestDB2 database is open in Microsoft Azure. The Overview section is displayed. Details include the Resource group, Server name, and Status.] All right now previously, we saw how to create a new Azure SQL Database. And once you have that created, then of course you're going to want to be able to manage it. And there's limited management available within the Azure portal. So, of course, perhaps the most familiar management tool for SQL Database administrators is SQL Server Management Studio. And you absolutely can connect through to your Azure SQL databases using SQL Server Management Studio. And, in fact, when you do create a new database, as soon as the creation completes, it brings you to this very page right here. And you can see the server name is what we do need to connect to. So azdbserver1 is my server, and there are now two databases on this server. I just created one in the previous demonstration, and I had already created one earlier. But in terms of establishing a connection, this is what I am connecting to. But the other thing that we need to do is allow a connection through to this server. And that option is right here. Set server firewall. [The presenter refers to the option on the toolbar.] So all you need to do is click on that. And you can see that this opens up a page [The Firewall settings page opens.] and all you have to do is add the IP address that is being picked up on by the Azure portal. [He refers to the Client IP address that displays.]
So from the perspective of Azure, this is your public IP address and that is all you need to do. Click this button right here that says Add client IP. Now, you might notice that mine is already here, and this is simply because I've already set this up. But that's all you have to do, it picks up on your IP. Click Add client IP, it gives it a default name of ClientIPAddress with a date and time indicator here, you can rename it if you want to, that's perfectly fine, and you can also allow a range of addresses. So if I wanted several systems to be able to access this, I could just call it something like SQL Firewall Group or something along those lines. [He types SQLFWGroup in the RULE NAME text field.] It doesn't matter what you call it but then just plug in your first IP and the last IP, the range for those servers and they will all be allowed. [He refers to the START IP and END IP text fields.] Now, I won't bother saving that. And again, the only thing you need to do to add the client at which you are sitting, is click this Add client IP button. I've already done so. That's it, so, I'm going to close this. I won't bother saving any of those changes, and I'll minimize my Azure portal, and there is SQL Server Management Studio. [He clicks the icon on the taskbar to open Microsoft SQL Server Management Studio. The Object Explorer displays.]
So, now, all I need to do is click on Connect. [He clicks the Connect drop-down button on the Object Explorer toolbar.] Choose Database Engine, [The Connect to Server window opens.] and the server name will be that name that we just saw in the portal. Now, again I've already done this so mine will be remembered. There it is. azdbserver1.database.windows.net. [He selects the option from the Server name drop-down menu list.] So again, that's what you need. Let me quickly return to the portal to show you that value, so there it is right there. [He opens Microsoft Azure and refers to the Server name on the Overview page.] As soon as you click on the database, on the Overview page, just copy this and there is the copy link right there. So that's all you need after you have set the firewall rule. [He changes back to Management Studio.] So again, we just specify that as the name of the server, and of course, the login, which was created when you created the database in the first place is that login that was set up on the server page. So I called mine AZTestAdmin. Simply enter the password, click on Connect. And we have a connection from SQL Server Management Studio through to our Azure Database Server. [The azdbserver1 database displays in Object Explorer.] If I expand Databases, I should see two. AZTestDB1, AZTestDB2. Now I can start managing these databases like any other database. There's not 100% similar configuration options here compared to local databases, but quite a lot. So feature parity is getting quite close between Azure SQL databases and local instance databases. So that's it, there is the connection, there are my databases. I can go ahead and perform my management now. And of course, I can just right-click on this and choose Disconnect and reconnect at any time. [He right-clicks the azdbserver1 database node.] So simply take note of the name of the server in Azure, set the firewall rule, make sure you have the administrative credentials for the server. And you should absolutely be able to connect from a local instance of SQL Server Management Studio.
Comments
Post a Comment