January 27, 2016 at 3:01 am
GilaMonster (1/27/2016)
ChrisM@Work (1/27/2016)
Two databases on the same "instance" isn't allowed or the two databases can't reference each other using three-part naming?With Azure SQL DB, there's no such thing as 'instances'. You have a DB. Where it is, is none of your concern. It may be on the same 'server' as another DB today and elsewhere tomorrow. You have a SQL DB offered as a service, with all the server-level stuff handled for you.
If you want control over which DBs are on an instance, which instances are on a server, then you need to control the server, that's Azure VMs with SQL installed on them
Thanks Gail. That's a helluva limitation.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 27, 2016 at 3:23 am
ChrisM@Work (1/27/2016)
GilaMonster (1/27/2016)
ChrisM@Work (1/27/2016)
Two databases on the same "instance" isn't allowed or the two databases can't reference each other using three-part naming?With Azure SQL DB, there's no such thing as 'instances'. You have a DB. Where it is, is none of your concern. It may be on the same 'server' as another DB today and elsewhere tomorrow. You have a SQL DB offered as a service, with all the server-level stuff handled for you.
If you want control over which DBs are on an instance, which instances are on a server, then you need to control the server, that's Azure VMs with SQL installed on them
Thanks Gail. That's a helluva limitation.
No, it's not. Azure SQLDB is not, afaik, intended to replace something like Hugo's interconnected web of DBs. It's a hosted, managed database (singular)
If you need control over the server-level, to define server roles, to have DBs sharing an instance, to set up linked servers or the rest, you need a hosted VM, not a hosted database.
When you go for Azure SQLDB, you're saying 'I want a database. I'll worry about what goes in that DB, you manage everything else'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 27, 2016 at 4:38 am
ChrisM@Work (1/27/2016)[hrMy jaw hit the floor when I read the last paragraph. No support for cross-database queries? Two databases on the same "instance" isn't allowed or the two databases can't reference each other using three-part naming? I've had a poke around in the release notes for elastic databases and a few other docs [/url] - but the over-liberal sprinkling of the keyword "remote" renders the documentation - at least for me - virtually unusable. I can't find an answer to the simple question "can two databases on the same instance communicate transparently as they can with terrestrial SQL Server". I keep landing on sharding - which, if it's meant to be a solution, appears to me to be massive overkill. Or maybe I'm particularly thick this morning.
With the new database pools and polybase, there are ways to do cross database queries. Here's a link.[/url]
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 27, 2016 at 4:40 am
GilaMonster (1/27/2016)
ChrisM@Work (1/27/2016)
GilaMonster (1/27/2016)
ChrisM@Work (1/27/2016)
Two databases on the same "instance" isn't allowed or the two databases can't reference each other using three-part naming?With Azure SQL DB, there's no such thing as 'instances'. You have a DB. Where it is, is none of your concern. It may be on the same 'server' as another DB today and elsewhere tomorrow. You have a SQL DB offered as a service, with all the server-level stuff handled for you.
If you want control over which DBs are on an instance, which instances are on a server, then you need to control the server, that's Azure VMs with SQL installed on them
Thanks Gail. That's a helluva limitation.
No, it's not. Azure SQLDB is not, afaik, intended to replace something like Hugo's interconnected web of DBs. It's a hosted, managed database (singular)
If you need control over the server-level, to define server roles, to have DBs sharing an instance, to set up linked servers or the rest, you need a hosted VM, not a hosted database.
When you go for Azure SQLDB, you're saying 'I want a database. I'll worry about what goes in that DB, you manage everything else'
That is what Azure SQLDB currently offers, true. But I do agree with Chris in that this offering doesn't cut it for a lot of companies.
Many companies use several 3rd party products, like ERP, CRM, etc. And then they have some custom software to integrate them and provide their own dashboards and reports for their specific needs. The 3rd party tools expect to be alone in a database; you cannot connect ERP and CRM to the same database, so you will end up with multiple databases. And then you will need to have some way to connect those databases.
I think that MS could get a lot more potential Azure customers if they change the AzureDB motto to 'I want a database that can also talk to other databases. I'll worry about what goes in that DB and I will follow your rules on setting up secure communications to the other databases, you manage everything else'
EDIT: Just saw Grant's post. Apparently MS is already working on this. I'll have to investigate and see if/how I can use this for my customer's migration.
Thanks, Grant!
January 27, 2016 at 5:08 am
Hugo Kornelis (1/27/2016)
I think that MS could get a lot more potential Azure customers if they change the AzureDB motto to 'I want a database that can also talk to other databases. I'll worry about what goes in that DB and I will follow your rules on setting up secure communications to the other databases, you manage everything else'
Oh sure, absolutely. I just see too many reactions to Azure SQLDB to be along the lines of 'no instance-level control? Unacceptable!'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 27, 2016 at 5:34 am
Grant Fritchey (1/27/2016)
ChrisM@Work (1/27/2016)[hrMy jaw hit the floor when I read the last paragraph. No support for cross-database queries? Two databases on the same "instance" isn't allowed or the two databases can't reference each other using three-part naming? I've had a poke around in the release notes for elastic databases and a few other docs [/url] - but the over-liberal sprinkling of the keyword "remote" renders the documentation - at least for me - virtually unusable. I can't find an answer to the simple question "can two databases on the same instance communicate transparently as they can with terrestrial SQL Server". I keep landing on sharding - which, if it's meant to be a solution, appears to me to be massive overkill. Or maybe I'm particularly thick this morning.
With the new database pools and polybase, there are ways to do cross database queries. Here's a link.[/url]
Thanks Grant, this isn't one of the docs I read earlier.
Here's a quote from it:
To start with, we need to create two databases, Customers and Orders, either in the same or different logical servers.
How confusing is that?
This same article has a "This topic applies to..." link. Open the link and navigate to the very first paragraph, and you find this:
For vertically partitioned scenarios, elastic query extends the current T-SQL DDL to refer to tables that are stored on remote databases. This section provides an overview of the DDL statements to configure elastic query for transparent access to remote tables. These DDL statements allow to create the metadata representation of your remote tables in the local database.
Say my customer has four databases on the same (terrestrial SQL Server) instance: Manufacturing, Suppliers & Purchasing, HR (third party) and Customers & Sales. There's also a modest staging db holding mostly metadata to facilitate daily data exports to another instance for offline reporting. Each of these db's communicates transparently and efficiently to the others - because they're local to each other. Very common scenario.
We don't want to combine these modules into one db, so at this point in time I'd be reluctant to consider migration to Azure because cross-database communication isn't yet sufficiently mature to inspire confidence.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 27, 2016 at 6:18 am
Hugo Kornelis (1/27/2016)
That is what Azure SQLDB currently offers, true. But I do agree with Chris in that this offering doesn't cut it for a lot of companies.Many companies use several 3rd party products, like ERP, CRM, etc. And then they have some custom software to integrate them and provide their own dashboards and reports for their specific needs. The 3rd party tools expect to be alone in a database; you cannot connect ERP and CRM to the same database, so you will end up with multiple databases. And then you will need to have some way to connect those databases.
I think that MS could get a lot more potential Azure customers if they change the AzureDB motto to 'I want a database that can also talk to other databases. I'll worry about what goes in that DB and I will follow your rules on setting up secure communications to the other databases, you manage everything else'
EDIT: Just saw Grant's post. Apparently MS is already working on this. I'll have to investigate and see if/how I can use this for my customer's migration.
Thanks, Grant!
I'm here to serve. 😀
I'm so mixed on the cross-database thing. It's like the lack of support for the variant data type. I think that's a feature. However, I know that people do cross-database queries, good or ill, and it does act as a limiting factor.
Actually, it might support variants now. Stuff moves so fast.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 27, 2016 at 6:24 am
ChrisM@Work (1/27/2016)
Grant Fritchey (1/27/2016)
ChrisM@Work (1/27/2016)[hrMy jaw hit the floor when I read the last paragraph. No support for cross-database queries? Two databases on the same "instance" isn't allowed or the two databases can't reference each other using three-part naming? I've had a poke around in the release notes for elastic databases and a few other docs [/url] - but the over-liberal sprinkling of the keyword "remote" renders the documentation - at least for me - virtually unusable. I can't find an answer to the simple question "can two databases on the same instance communicate transparently as they can with terrestrial SQL Server". I keep landing on sharding - which, if it's meant to be a solution, appears to me to be massive overkill. Or maybe I'm particularly thick this morning.
With the new database pools and polybase, there are ways to do cross database queries. Here's a link.[/url]
Thanks Grant, this isn't one of the docs I read earlier.
Here's a quote from it:
To start with, we need to create two databases, Customers and Orders, either in the same or different logical servers.
How confusing is that?
This same article has a "This topic applies to..." link. Open the link and navigate to the very first paragraph, and you find this:
For vertically partitioned scenarios, elastic query extends the current T-SQL DDL to refer to tables that are stored on remote databases. This section provides an overview of the DDL statements to configure elastic query for transparent access to remote tables. These DDL statements allow to create the metadata representation of your remote tables in the local database.
Say my customer has four databases on the same (terrestrial SQL Server) instance: Manufacturing, Suppliers & Purchasing, HR (third party) and Customers & Sales. There's also a modest staging db holding mostly metadata to facilitate daily data exports to another instance for offline reporting. Each of these db's communicates transparently and efficiently to the others - because they're local to each other. Very common scenario.
We don't want to combine these modules into one db, so at this point in time I'd be reluctant to consider migration to Azure because cross-database communication isn't yet sufficiently mature to inspire confidence.
Yeah. Either you're not who they're aiming at, or, you can rethink the design (and no, I'm not tossing rocks, just sayin). No arguments.
The issue is, there are no instances. You have to surrender that concept. Yeah, you have these things called "servers" and you can have all your databases on that server. However, that just means that you're putting all your databases into a particular data center, not on the same machine. They're in a single server for security and billing and maintenance. You have zero control over what machine any of the databases is on. Plus, remember, even the database isn't really a database. It's a logical construct, just like the server, and is actually three (at least) databases under the covers, stored on three different machines, using a process similar to replication (it's called that, but it's not what we think of when we say "replication") to make sure all three databases have the same data.
It's just a different critter. Once you abandon the "SQL Server in Space" concept that so many people approach it with, it's easier to deal with. It doesn't mean that eliminates all issues with it, but you can approach it as it's meant to be used rather than thinking about just moving your on-premises servers into the cloud. That's why they emphasize that it's Platform as a Service, not Infrastructure as a Service.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 27, 2016 at 6:41 am
Given that there are no instances, I assume there are other things that are not like normal servers.
How does the SAN features (if there are any) work? I'm assuming you can't put filegroups or log / data files on different drives. Is that true?
January 27, 2016 at 7:12 am
Brandie Tarvin (1/27/2016)
Given that there are no instances, I assume there are other things that are not like normal servers.How does the SAN features (if there are any) work? I'm assuming you can't put filegroups or log / data files on different drives. Is that true?
The server roles would be one of my questions. It impacts things like BULK INSERT, backups, restores, granting permissions, index and statistics maintenance, growth tracking, performance investigation with the DMVs, database jobs and a whole bunch of other stuff.
The cross-database queries and queries over linked servers are unknowns. Then there's the whole concept of development, test and production environments. Then there's the fixed versus variable costs. If we get billed by reading data and not writing it (which is brilliant on Microsoft's part, BTW) then do we get hit the same for development, test and production databases? That certainly wouldn't sit well with the boss.
Never having used it, I guess i just can't wrap my head around how some things are done. I get that full and log backups can be scheduled, but some of the other stuff are still big questions. To be fair, I haven't done much research into it because the way things sit now, we flat-out can't use it. There must be a document somewhere that addresses some of the bigger questions that I haven't found yet.
January 27, 2016 at 7:15 am
Brandie Tarvin (1/27/2016)
Given that there are no instances, I assume there are other things that are not like normal servers.How does the SAN features (if there are any) work? I'm assuming you can't put filegroups or log / data files on different drives. Is that true?
Abandon the idea of SAN, file groups, and even logs. None of that is under your management. You're managing a database or a group of databases. You're not managing a server. Control of the database is set through the service tier, not through the server. It's a different world.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 27, 2016 at 7:21 am
Ed Wagner (1/27/2016)
Brandie Tarvin (1/27/2016)
Given that there are no instances, I assume there are other things that are not like normal servers.How does the SAN features (if there are any) work? I'm assuming you can't put filegroups or log / data files on different drives. Is that true?
The server roles would be one of my questions. It impacts things like BULK INSERT, backups, restores, granting permissions, index and statistics maintenance, growth tracking, performance investigation with the DMVs, database jobs and a whole bunch of other stuff.
The cross-database queries and queries over linked servers are unknowns. Then there's the whole concept of development, test and production environments. Then there's the fixed versus variable costs. If we get billed by reading data and not writing it (which is brilliant on Microsoft's part, BTW) then do we get hit the same for development, test and production databases? That certainly wouldn't sit well with the boss.
Never having used it, I guess i just can't wrap my head around how some things are done. I get that full and log backups can be scheduled, but some of the other stuff are still big questions. To be fair, I haven't done much research into it because the way things sit now, we flat-out can't use it. There must be a document somewhere that addresses some of the bigger questions that I haven't found yet.
Backups are actually managed by Microsoft. You can read this blog post[/url] I have on doing a point in time restore. There is a way to do database backups, but log backups are completely out of your control.
Database maintenance, like index defrag, statistics, still has to be done. DMVs are there, although there are differences. For example sys.dm_os_wait_stats is pretty useless. Instead, there's a new one, sys.dm_db_wait_stats that shows waits for a database.
The fixed cost model is controlled through database pooling. You can actually have a set amount of cost every month and you just have to manage your databases within that cost, spinning them up and down through the service tiers.
It's really exciting stuff. It changes how we do what we do. It sure doesn't eliminate our jobs, at all, but it changes them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 27, 2016 at 7:29 am
Grant Fritchey (1/27/2016)
Database maintenance, like index defrag, statistics, still has to be done. DMVs are there, although there are differences. For example sys.dm_os_wait_stats is pretty useless. Instead, there's a new one, sys.dm_db_wait_stats that shows waits for a database.
If cross-database connections aren't allowed, does that mean database maintenance has to be written in a decentralized way. In other words, no more DBA database that does the maintenance for all databases?
January 27, 2016 at 7:34 am
Grant Fritchey (1/27/2016)
ChrisM@Work (1/27/2016)
Grant Fritchey (1/27/2016)
ChrisM@Work (1/27/2016)[hrMy jaw hit the floor when I read the last paragraph. No support for cross-database queries? Two databases on the same "instance" isn't allowed or the two databases can't reference each other using three-part naming? I've had a poke around in the release notes for elastic databases and a few other docs [/url] - but the over-liberal sprinkling of the keyword "remote" renders the documentation - at least for me - virtually unusable. I can't find an answer to the simple question "can two databases on the same instance communicate transparently as they can with terrestrial SQL Server". I keep landing on sharding - which, if it's meant to be a solution, appears to me to be massive overkill. Or maybe I'm particularly thick this morning.
With the new database pools and polybase, there are ways to do cross database queries. Here's a link.[/url]
Thanks Grant, this isn't one of the docs I read earlier.
Here's a quote from it:
To start with, we need to create two databases, Customers and Orders, either in the same or different logical servers.
How confusing is that?
This same article has a "This topic applies to..." link. Open the link and navigate to the very first paragraph, and you find this:
For vertically partitioned scenarios, elastic query extends the current T-SQL DDL to refer to tables that are stored on remote databases. This section provides an overview of the DDL statements to configure elastic query for transparent access to remote tables. These DDL statements allow to create the metadata representation of your remote tables in the local database.
Say my customer has four databases on the same (terrestrial SQL Server) instance: Manufacturing, Suppliers & Purchasing, HR (third party) and Customers & Sales. There's also a modest staging db holding mostly metadata to facilitate daily data exports to another instance for offline reporting. Each of these db's communicates transparently and efficiently to the others - because they're local to each other. Very common scenario.
We don't want to combine these modules into one db, so at this point in time I'd be reluctant to consider migration to Azure because cross-database communication isn't yet sufficiently mature to inspire confidence.
Yeah. Either you're not who they're aiming at, or, you can rethink the design (and no, I'm not tossing rocks, just sayin). No arguments.
The issue is, there are no instances. You have to surrender that concept. Yeah, you have these things called "servers" and you can have all your databases on that server. However, that just means that you're putting all your databases into a particular data center, not on the same machine. They're in a single server for security and billing and maintenance. You have zero control over what machine any of the databases is on. Plus, remember, even the database isn't really a database. It's a logical construct, just like the server, and is actually three (at least) databases under the covers, stored on three different machines, using a process similar to replication (it's called that, but it's not what we think of when we say "replication") to make sure all three databases have the same data.
It's just a different critter. Once you abandon the "SQL Server in Space" concept that so many people approach it with, it's easier to deal with. It doesn't mean that eliminates all issues with it, but you can approach it as it's meant to be used rather than thinking about just moving your on-premises servers into the cloud. That's why they emphasize that it's Platform as a Service, not Infrastructure as a Service.
For those who can't abandon the "SQL Server in Space" concept and therefore aren't the target for Azure database, they have an alternative - Azure VM's, which can host what appears to be vanilla SQL Server, albeit heavily managed. Apparently I'll be playing with this quite soon.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 27, 2016 at 7:40 am
Grant Fritchey (1/27/2016)
Ed Wagner (1/27/2016)
Brandie Tarvin (1/27/2016)
Given that there are no instances, I assume there are other things that are not like normal servers.How does the SAN features (if there are any) work? I'm assuming you can't put filegroups or log / data files on different drives. Is that true?
The server roles would be one of my questions. It impacts things like BULK INSERT, backups, restores, granting permissions, index and statistics maintenance, growth tracking, performance investigation with the DMVs, database jobs and a whole bunch of other stuff.
The cross-database queries and queries over linked servers are unknowns. Then there's the whole concept of development, test and production environments. Then there's the fixed versus variable costs. If we get billed by reading data and not writing it (which is brilliant on Microsoft's part, BTW) then do we get hit the same for development, test and production databases? That certainly wouldn't sit well with the boss.
Never having used it, I guess i just can't wrap my head around how some things are done. I get that full and log backups can be scheduled, but some of the other stuff are still big questions. To be fair, I haven't done much research into it because the way things sit now, we flat-out can't use it. There must be a document somewhere that addresses some of the bigger questions that I haven't found yet.
I haven't come up with a good idea on that yet. Our Azure initiative is in it's infancy. There are a couple very small "one-off" systems that are in production. The devs run reindexing/statistics scripts manually about once a week.
Backups are actually managed by Microsoft. You can read this blog post[/url] I have on doing a point in time restore. There is a way to do database backups, but log backups are completely out of your control.
Database maintenance, like index defrag, statistics, still has to be done. DMVs are there, although there are differences. For example sys.dm_os_wait_stats is pretty useless. Instead, there's a new one, sys.dm_db_wait_stats that shows waits for a database.
The fixed cost model is controlled through database pooling. You can actually have a set amount of cost every month and you just have to manage your databases within that cost, spinning them up and down through the service tiers.
It's really exciting stuff. It changes how we do what we do. It sure doesn't eliminate our jobs, at all, but it changes them.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 15 posts - 52,396 through 52,410 (of 66,738 total)
You must be logged in to reply to this topic. Login to reply