WheWhen you are doing an azure migration, one of the first thing you must do is
collect all the information you can about your database.
Also at some point in your migration process you might consider between migration to
SQL Azure or Azure Storage or Azure Tables.
Do do all the appropriate decisions you need to collect at least basic data like:
- Database Size
- Table Size
- Row Size
- User Defined Types or any other code that depends on the CLR
- Extended Properties
Database Size
You can use a script like this to collect some general information:
create table #spaceused(
databasename varchar(255),
size varchar(255),
owner varchar(255),
dbid int,
created varchar(255),
status varchar(255),
level int)
insert #spaceused (databasename , size,owner,dbid,created,status, level) exec sp_helpdb
select * from #spaceused for xml raw
drop table #spaceused
When you run this script you will get an XML like:
<row databasename="master"
size=" 33.69 MB"
owner="sa"
dbid="1"
created="Apr 8 2003"
status="Status=ONLINE, ..."
level="90"/>
<row databasename="msdb"
size=" 50.50 MB"
owner="sa"
dbid="4"
created="Oct 14 2005"
status="Status=ONLINE, ..."
level="90"/>
<row databasename="mycooldb"
size=" 180.94 MB"
owner="sa"
dbid="89"
created="Apr 22 2010"
status="Status=ONLINE, ..."
level="90"/>
<row databasename="cooldb"
size=" 10.49 MB"
owner="sa"
dbid="53"
created="Jul 22 2010"
status="Status=ONLINE, ..."
level="90"/>
<row databasename="tempdb"
size=" 398.44 MB"
owner="sa" dbid="2"
created="Feb 16 2011"
status="Status=ONLINE, ..."
level="90"/>
And yes I know there are several other scripts that can give you more detailed information about your database
but this one answers simple questions like
Does my database fits in SQL Azure?
Which is an appropriate SQL Azure DB Size?
Also remember that SQL Azure is based on SQL Server 2008 (level 100).
80 = SQL Server 2000
90 = SQL Server 2005
100 = SQL Server 2008
If you are migrating from an older database (level 80 or 90) it might be necessary to upgrade first.
This post might be helpful: http://blog.scalabilityexperts.com/2008/01/28/upgrade-sql-server-2000-to-2005-or-2008/
Table Size
Table size is also important.There great script for that:
http://vyaskn.tripod.com/sp_show_biggest_tables.htm
If you plan to migrate to Azure Storage there are certain constraints. For example consider looking at the number of columns:
You can use these scripts: http://www.novicksoftware.com/udfofweek/vol2/t-sql-udf-vol-2-num-27-udf_tbl_colcounttab.htm (I just had to change the alter for create)
Row Size
I found this on a forum (thanks to Lee Dice and Michael Lee)
DECLARE @sql VARCHAR (8000)
, @tablename VARCHAR (255)
, @delim VARCHAR (3)
, @q CHAR (1)
SELECT @tablename = '{table name}'
, @q = CHAR (39)
SELECT @delim = ''
, @sql = 'SELECT '
SELECT @sql = @sql
+ @delim
+ 'ISNULL(DATALENGTH ([' + name + ']),0)'
, @delim = ' + '
FROM syscolumns
WHERE id = OBJECT_ID (@tablename)
ORDER BY colid
SELECT @sql = @sql + ' rowlength'
+ ' FROM [' + @tablename + ']'
, @sql = 'SELECT MAX (rowlength)'
+ ' FROM (' + @sql + ') rowlengths'
PRINT @sql
EXEC (@sql)
Remember to change the {table name} for the name of the table you need
User Defined Types or any other code that depends on the CLR
Just look at your db scripts at determine if there are any CREATE TYPE statements with the assembly keyword.
Also determine if CLR is enabled with a query like:
select * from sys.configurations where name = 'clr enabled'
If this query has a column value = 1 then it is enabled.
Extended Properties
Look for calls to sp_addextendedproperty dropextendedproperty OBJECTPROPERTY and sys.extended_properties in your scripts.