Prev: How much performance impact due to database mirrorring
Next: JDBC Driver
From: Mike Baker on 21 Aug 2007 11:26
Hi Charles,

I'm finally getting the chance to come back and finish this part of the app.
Here's what's going on...

The client has an online database in SQL server 2005 that services all their
connected locations. They also have locations which will, for a time, be
without internet access. They need to be able to deploy a disconnected
version of their database and application to these remote sites that will
only have dial-up intermittently. We went through the options of disconnected
datasets and replication through nightly processes, etc. Part of the problem
is the remote locations will have SQL 2000 rather than 2005 and there will be
limited space on the servers in these remote locations. One final thing, the
client wants to set up this db and app periodically (quarterly) by running a
program in the online environment that will be used in configuring the
offline system. The program needs to build setup scripts or a source file for
the DB *and* data.
What we came up with is this...

1) Copy the existing database from one database to another in the online
instance (same version 2005 to 2005). I have SMO Backup and Restore working
on this but haven't checked the result thoroughly yet. We can also use
TransferData if that's preferable.

2) Strip the database of anything not needed in the offline version to
conserve space. This is working fine using the tables, storeprocedure, etc
collectsions and the .Drop method.

3) Once the extra stuff is removed we need to get it ready to run in SQL
2000. It doesn't matter if this is done with TransferData but I think
ScriptTransfer would work just as well. The desired end result is an
installer that will place the database on the destination server. I have an
installer project for the VB.NET Web application being deployed and I believe
I can manage to add a custom step to call SQL scripts or use the SMO Restore
object if we can just get them prepared.

Want to see the code here or should I send it directly to save space in the
newsgroup?

Thanks in advance
Mike

"Charles Wang[MSFT]" wrote:

> Hi,
> I am interested in this issue. Would you mind letting me know the result of
> the suggestions? If you need further assistance, feel free to let me know.
> I will be more than happy to be of assistance.
>
> Charles Wang
> Microsoft Online Community Support
>
> ======================================================
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================
>
>
From: Charles Wang[MSFT] on 23 Aug 2007 07:48
Hi Mike,
Thanks for your detailed updating and response. This makes me understand
your scenario much clear.

I agree that your solution is quite reasonable. Since a long time past, I
would like to first check with you whether or not the issue mentioned in
your last response persists now. If it persists, please mail me
(changliw_at_microsoft_dot_com) your code for further research.

If you have any other questions or concerns, please feel free to let me
know.

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================







From: Mike Baker on 23 Aug 2007 15:50
Hi Charles,

Thanks for the reply. Yes a lot of time has past. When we talked last I was
in prototyping stage for the solution, now the 'offline' version of the app
is just about finished and I'm returning to 'loose ends' :-)

The copy from 2005 of the mainline DB to the 2005 working copy of the DB is
working with TransferData as follows

Public Class MainForm

' The server where we'll find the DB
Private Const SERVERSOURCE = "MACHINE02"
Private Const DBSOURCENAME = "AppDB"
' The server where we'll do the work on the copy
Private Const SERVERWORKING = "MACHINE02"
Private Const DBWORKINGNAME = "AppDBWorkingCopy"

Private Sub CopyAppDB(ByVal srvSource As Server, ByVal srvDest As Server)
Dim dbDest As Database = Nothing
Dim xfr As Transfer
Dim dbSource As Database = Nothing

If (srvSource Is Nothing Or srvDest Is Nothing) Then
Exit Sub
End If

Try
dbSource = srvSource.Databases(DBSOURCENAME)
dbDest = New Database(srvDest, DBWORKINGNAME)
dbDest.Create()

xfr = New Transfer(dbSource)
xfr.CopyAllObjects = True
xfr.CopySchema = True
xfr.CopyData = True
xfr.DestinationDatabase = DBWORKINGNAME
xfr.DestinationServer = srvDest.Name

xfr.TransferData()

Catch ex As Exception
' TODO: hanlde exception
' Throw up to caller
Throw (ex)
End Try

End Sub

Private Sub btnCopyDB_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnCopyDB.Click
Dim srvSource As Server = Nothing
Dim srvDest As Server = Nothing
Dim connSource As ServerConnection = Nothing
Dim connDest As ServerConnection = Nothing

Try
connSource = New ServerConnection(SERVERSOURCE)
connDest = New ServerConnection(SERVERWORKING)

srvSource = New Server(connSource)
srvDest = New Server(connDest)

CopyAppDB(srvSource, srvDest)

Catch ex As Exception

If (connSource.IsOpen) Then
connSource.Disconnect()
End If
End Try

End Sub
End Class

As I said this part is now working. I've eliminated all the errors that were
related to actual problems in the database such as invalid column references
in stored procedures and views and TransferData is working.

The database modifications are working just fine after the working copy is
made. I only need to make the final version that will be used to create the
DB on the end machine. I'm trying to use TransferData with a SQL 2000 target
and I'm getting this error "User.UserType: NoLogin is not a valid option for
SQL Server 2005." Here's the code:

Private Const SERVERDEST = "MACHINE03" ' SQL 2000
Private Const DBOFFLINENAME = "AppDBOffline"

Private Sub ScriptAppDBTransfer(ByVal srvSource As Server, ByVal srvDest
As Server)

Dim xfrstrings As StringCollection
Dim xfr As Transfer
Dim dbSource As Database = Nothing

If (srvSource Is Nothing Or srvDest Is Nothing) Then
Exit Sub
End If

Try
dbSource = srvSource.Databases(DBWORKINGNAME)
xfr = New Transfer(dbSource)

' copy options
xfr.CopyAllObjects = True
'xfr.CopyAllTables = True
'xfr.CopyAllViews = True
'xfr.CopyAllStoredProcedures = True
xfr.CopySchema = True
xfr.CopyData = True
xfr.Options.TargetServerVersion = SqlServerVersion.Version80
xfr.DestinationDatabase = DBOFFLINENAME
xfr.DestinationServer = srvDest.Name
' set for SQL Server login
xfr.DestinationLoginSecure = False
xfr.DestinationLogin = "sa"
xfr.DestinationPassword = "******"

' transfer the database to SQL 2000
xfr.TransferData()

' build scripts for database deployment
xfrstrings = xfr.ScriptTransfer()
Dim x As Integer
Using sw As StreamWriter = New StreamWriter("c:\ScriptFile.sql")
For x = 0 To xfrstrings.Count - 1
' Add some text to the file.
' sw.Write("Line" & x & ":")
sw.WriteLine(xfrstrings.Item(x))
Next
sw.Close()
End Using
Catch ex As Exception
' TODO: hanlde exception
' Throw up to caller
Throw (ex)
End Try

End Sub

You can see this last function is attempting two things. First is
TransferData which I put in today because ScriptTransfer doesn't include the
data. Doesn't matter to me how we get it done, could even be Backup and
Restore if we can make it work across versions. Thanks very much for helping
me with this.

Mike

"Charles Wang[MSFT]" wrote:

> Hi Mike,
> Thanks for your detailed updating and response. This makes me understand
> your scenario much clear.
>
> I agree that your solution is quite reasonable. Since a long time past, I
> would like to first check with you whether or not the issue mentioned in
> your last response persists now. If it persists, please mail me
> (changliw_at_microsoft_dot_com) your code for further research.
>
> If you have any other questions or concerns, please feel free to let me
> know.
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
>
From: Charles Wang[MSFT] on 27 Aug 2007 05:52
Hi Mike,
From the error message, it seemed that this issue was caused by
transferring database users/logins. If there are some users/logins created
for the database in SQL Server 2005, the transfer would fail.

Since there are incompatibilities regarding users/logins scripts between
SQL Server 2000 and SQL SERVER 2005, I recommend that you do not set
CopyAllObjects as True. BACKUP and RESTORED cannot be used here since SQL
Server 2005 backup file cannot be recognized by SQL Server 2000.

You may refer to the following script to see if it helps:
private void TransferDatabase(string srcSvr,string srcDb, string
destSvr,string destDb,string destUserName,string destPassword)
{
ServerConnection cnDest = new ServerConnection(destSvr,
destUserName, destPassword);
Server destServer = new Server(cnDest);
if (destServer.Databases[destDb] == null)
{
Database dbCopy;
dbCopy = new Database(destServer,destDb);
dbCopy.Create();
}

ServerConnection cnSource = new ServerConnection(srcSvr);
cnSource.LoginSecure = true;
Server srcServer = new Server(cnSource);

Database db;
db = srcServer.Databases[srcDb];
Transfer transfer = new Transfer(db);

transfer.CopyAllObjects = false;
transfer.CopySchema = false;
transfer.CopyData = true;
transfer.CopyAllStoredProcedures = true;
transfer.CopyAllTables = true;
transfer.CopyAllViews = true;
transfer.CopyAllDatabaseTriggers = true;
transfer.CopyAllUserDefinedFunctions = true;

transfer.Options.WithDependencies = true;
transfer.Options.ContinueScriptingOnError = true;
transfer.Options.TargetServerVersion =
SqlServerVersion.Version80;
transfer.DropDestinationObjectsFirst = true;
transfer.DestinationServer = destSvr;
transfer.DestinationDatabase = destDb;
transfer.DestinationServer = destSvr;
transfer.TransferData();
}

For SQL Server logins, passwords, users and permissions, you may need to
manually change the script generated from SQL Server 2005 and then execute
the script file on your destination server via SqlCommand.
You may also refer to:
How to transfer logins and passwords between instances of SQL Server
http://support.microsoft.com/kb/246133/EN-US

Please feel free to let me know if you have any other questions or concerns.

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================





From: Mike Baker on 27 Aug 2007 12:58
Hi Charles,

It's working now. I only changed one thing in your code, CopySchema = true;
If CopySchema is false then I get DB_NOTABLE error message.

Thanks very much for all the help.

Mike

 |  Next  |  Last
Pages: 1 2
Prev: How much performance impact due to database mirrorring
Next: JDBC Driver