Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Best practices for sending a database to your client (schema & data) Expand / Collapse
Author
Message
Posted Friday, March 11, 2011 8:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 27, 2011 10:26 AM
Points: 18, Visits: 65
We currently use SQL 2008's "Generate Scripts" feature to package our data into a series of scripts. We then have a batch file that uses sqlcmd to run each script against the target database.

I am looking for alternatives because this method has become quite cumbersome as the database continues to grow and grow and grow!

One possibility seems to be to create a .bak file for the database. But we've run into issues with collation when moving .bak files between servers that aren't configured the same. Not sure how to fix that either

Any help is appreciated, thanks.
Post #1076892
Posted Friday, March 11, 2011 9:29 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:23 PM
Points: 15,737, Visits: 28,145
You might want to start to look at tools like Visual Studio Team System or Red Gate SQL Source Control as a mechanism for implementing deployment processes that allow you to move things in an incremental manner.

If you just have to move the whole database every time, I'd go with backup and restore. It's probably the safest and most efficient. As far as collation errors, supply some documentation as to which collation should be used. That one is just a bit outside your control.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1076990
Posted Friday, March 11, 2011 9:57 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, February 8, 2012 1:38 PM
Points: 56, Visits: 107
If your clients are using SQL Server 2005, I think, and later, you could use the .net framework and SMO to create an exe that restores the backup or runs the scripts that they can run from the commandline.

I am an application developer, and we use Advanced Installer (http://www.advancedinstaller.com/) to install our ASP.net applications that are all front ends to a SQL Server 2005 or later database. The MSI that it creates executes the SQL scripts against the SQL Server. It works pretty slick, so this would be another option. You can use this in two manners: 1) Generate scripts for your database, including data, and have the MSI execute the scripts, or 2) Have the MSI restore your database. I have done the option 1 with success, but knowing the functionality of Advanced Installer, option 2 is doable.

You may also want to make sure that you are compacting your backups to help with size.

Hope this helps,
Jim
Post #1077028
Posted Friday, March 11, 2011 11:14 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
LightningFingers (3/11/2011)
One possibility seems to be to create a .bak file for the database. But we've run into issues with collation when moving .bak files between servers that aren't configured the same.


Out of curiosity, may I ask what kind of issues?

Backup/Restore and Detach/Attach methods are not supposed to change collation on target system e.g. collation remains the same as the one in source system.


_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #1077086
Posted Friday, March 11, 2011 11:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 27, 2011 10:26 AM
Points: 18, Visits: 65
PaulB-TheOneAndOnly (3/11/2011)

Out of curiosity, may I ask what kind of issues?

Backup/Restore and Detach/Attach methods are not supposed to change collation on target system e.g. collation remains the same as the one in source system.


I don't completely understand collations, to be honest. And unfortunately we don't have a DBA where I work to help guides us through things like this. The problems began when the developers all received new workstations. The local SQL Server install used a different collation than our staging server. I don't specifically remember what the issues were, just error messages when running stored procedures. Some tables had a specific collation applied to them that was different than the database collation. Its all quite messed up to be honest. But we found that as long as we use scripts to generate the database we didn't encounter the issues.

I wrote the following script to modify the collation for each table in a database, but didn't have the confidence in its implications to ever run it anywhere but on my local DB.

DECLARE @DB_Collation nvarchar(50)
SET @DB_Collation = CAST(DATABASEPROPERTYEX('TargetDbName', 'Collation') as nvarchar)

-- Display the rows before the updated
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE (
COLLATION_NAME IS NOT NULL AND
COLLATION_NAME != @DB_Collation AND
EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME = c.TABLE_NAME AND TABLE_TYPE = 'BASE TABLE')
)

DECLARE dynamicSqlCursor CURSOR
FOR SELECT 'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' + char(10) +
'ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE +
CASE WHEN DATA_TYPE = 'nvarchar' OR DATA_TYPE = 'varchar'
THEN '(' +
CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1
THEN 'MAX'
ELSE CAST(CHARACTER_MAXIMUM_LENGTH as varchar)
END + ')'
END + char(10) + 'COLLATE ' + @DB_Collation + char(10) +
CASE IS_NULLABLE
WHEN 'NO' THEN 'NOT NULL'
WHEN 'YES' THEN 'NULL'
END + char(10)
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE (
TABLE_SCHEMA != 'dbo' AND -- Avoid system tables
COLLATION_NAME IS NOT NULL AND -- Null collation will fall back on database collation anyways
COLLATION_NAME != @DB_Collation AND -- This is our true target: old collation values
EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME = c.TABLE_NAME AND TABLE_TYPE = 'BASE TABLE') -- Alter tables only
)

DECLARE @SQL nvarchar(MAX)

OPEN dynamicSqlCursor
FETCH NEXT FROM dynamicSqlCursor
INTO @SQL

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @SQL

-- Execute the query
EXEC sp_executeSql @SQL

FETCH NEXT FROM dynamicSqlCursor
INTO @SQL
END

CLOSE dynamicSqlCursor
DEALLOCATE dynamicSqlCursor

-- Verify the results. The following query should return 0 rows.
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE (
COLLATION_NAME IS NOT NULL AND
COLLATION_NAME != 'SQL_Latin1_General_CP1_CI_AS' AND
EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME = c.TABLE_NAME AND TABLE_TYPE = 'BASE TABLE')
)

Post #1077100
Posted Friday, March 11, 2011 12:05 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
LightningFingers (3/11/2011)
I don't completely understand collations, to be honest. And unfortunately we don't have a DBA where I work to help guides us through things like this.


It appears like you are in between a rock and a hard place.

You may want to check document below "How to transfer a database from one collation to another collation in SQL Server"; it includes a nice explanation about what "collation" is about - hope this helps.
http://support.microsoft.com/kb/325335


_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #1077123
Posted Thursday, November 8, 2012 3:59 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:32 AM
Points: 45, Visits: 338
PaulB-TheOneAndOnly (3/11/2011)
LightningFingers (3/11/2011)
One possibility seems to be to create a .bak file for the database. But we've run into issues with collation when moving .bak files between servers that aren't configured the same.


Out of curiosity, may I ask what kind of issues?

Backup/Restore and Detach/Attach methods are not supposed to change collation on target system e.g. collation remains the same as the one in source system.


As Paul says, those techniques don't change your collation.
So a restored database's collation will be consistent with its source, the database's default collation should also be the same.

However, if the server uses a different collation to your database, then there is a small inconvenience you're likely to run into:
Whenever you create a temp table, it will go to tempdb, and use temdb's collation as its default.

If you then try compare data between the temp tables and your regular tables, you will get collation errors.
To resolve this, create your temp tables specifying a collation clause COLLATE DATABASE DEFAULT for each column. This will create temp tables with the same default as your current database.

If this doesn't work, then you may already have inconsistent collation in your database; either due to changing the database defalt at some stage or explicitly specifying collation on some tables.
Resolve this by simply explicitly specifying the exact same collation when you create the temp tables.

There is one other thing worth knowing: you can explicitly indicate which collation to use on comparison expressions. E.g. Table1.Col1 = Table2.Col1 COLLATE XXXX
E.g. You might do this to force a case-sensitive comparison on case insensitive columns. But be warned, the technique can prevent the use of indexes, because indexes are stored in collation order.
Post #1382754
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse