February 3, 2016 at 3:21 pm
Hi Guys,
I have a requirement to migrate on premise SQL 2014 db to Azure.
The database has user defined file groups. Is user defined file group supported in Azure?
Can I migrate to azure using the same file group.
Many thanks!
February 3, 2016 at 3:24 pm
Are you moving to Azure SQL DB or to Azure VMs with SQL installed?
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
February 3, 2016 at 4:09 pm
Hi Gail,
It will be Azure SQL DB.
Thanks!
February 8, 2016 at 8:54 am
Nope. No file group support in Azure SQL Database. Azure SQL Database is a Platform as a Service (PaaS) offering for SQL Server databases. No options that require access to the server, such as file and filegroups, are available there. What did you need the filegroup for?
Here's a good article from Microsoft on how to migrate [/url]and check your migration abilities. There's also a tool you can download from codeplex[/url].
These should help you with the migration.
"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
February 8, 2016 at 9:52 am
Grant Fritchey (2/8/2016)
What did you need the filegroup for?
Thanks Grant!
The db has been designed/created to use a user defined file group as default for data and a separate index file group for Indexes.
February 8, 2016 at 10:43 am
SQL!$@w$0ME (2/8/2016)
Grant Fritchey (2/8/2016)
What did you need the filegroup for?Thanks Grant!
The db has been designed/created to use a user defined file group as default for data and a separate index file group for Indexes.
OK. That sort of thing goes away. You don't really control where things are stored. You only control the service tier that you're running on. It's a little bit of a different animal when working with PaaS.
"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
February 12, 2016 at 8:21 am
As Grant said, this is different in Azure. You treat the system as a database service, meaning you connect to a database and see the objects, but that's it. There isn't any control over, nor need to worry about, the way in which the physical structure of the database is organized.
I would guess your application not doesn't really need to know indexes are separate from data in filegroups. Someone made that decision, and potentially a setup process/program does this, but you would ignore that in Azure SQL Database. The database gets created and then you just put objects and data inside it.
It works like this: http://www.sqlservercentral.com/articles/Azure+SQL+Platform+as+Service+%28PaaS%29/128108/
February 12, 2016 at 9:00 pm
Thanks!
April 19, 2017 at 12:39 pm
What if you created a "read_only" file group to keep the data in your database that is archived and no longer being updated? Having a separate file group enables us to create a backup of the primary file group daily and the read_only file group only once a month when new data ages out.
This way the backup of the primary file group will run much faster since it does not have to do any of the read_only file group data.
How would I implement this in Azure?
April 19, 2017 at 12:54 pm
Fred Theron - Wednesday, April 19, 2017 12:39 PMWhat if you created a "read_only" file group to keep the data in your database that is archived and no longer being updated? Having a separate file group enables us to create a backup of the primary file group daily and the read_only file group only once a month when new data ages out.This way the backup of the primary file group will run much faster since it does not have to do any of the read_only file group data.
How would I implement this in Azure?
In an Azure VM, exactly the same way.
Currently, in Azure SQL Database, you don't. Platform as a Service is a different critter. You have to think about it and approach it differently. Also, it's not there to solve any and every problem. There are some problems that are better served through other tools. Just as NoSQL databases offer good solutions in some circumstances, but not others. Same thing here.
"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
April 19, 2017 at 1:08 pm
Fred Theron - Wednesday, April 19, 2017 12:39 PMWhat if you created a "read_only" file group to keep the data in your database that is archived and no longer being updated? Having a separate file group enables us to create a backup of the primary file group daily and the read_only file group only once a month when new data ages out.This way the backup of the primary file group will run much faster since it does not have to do any of the read_only file group data.
Keep in mind that if you migrate that to Azure SQLDB, you don't do backups any longer, so the time required or space required for backups is not something you need to worry about.
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
April 19, 2017 at 7:10 pm
GilaMonster - Wednesday, April 19, 2017 1:08 PMFred Theron - Wednesday, April 19, 2017 12:39 PMWhat if you created a "read_only" file group to keep the data in your database that is archived and no longer being updated? Having a separate file group enables us to create a backup of the primary file group daily and the read_only file group only once a month when new data ages out.This way the backup of the primary file group will run much faster since it does not have to do any of the read_only file group data.
Keep in mind that if you migrate that to Azure SQLDB, you don't do backups any longer, so the time required or space required for backups is not something you need to worry about.
You can still do backups, and I do occasionally (paranoia). However, they're not really backups. You have to create a copy of the database and the exporting that to a .dacpac file, then remember to drop the copy.
"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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply