Re: SQL 2000 and MSDE/Express/Compact edition..

1) Actually I was very wrong in that article. WebSynchronization is available in Workgroup Edition and above.

2) I write it in code. There is a sample at the end. What is does is downloads .Net Framework and SQL Server Express if they are not installed and then configures SQL Server as a subscriber. For this sample you have to visit each machine and push a button (or click on a link to deploy it).

4) its fairly straightfoward. The more work and testing you do the more canned it can become.


using System;
using System.Collections.Generic;
using System.Text;
using System.Diagnostics;
using System.IO;
using System.Threading;
using System.Net;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.Wmi;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Management.Common;


namespace DownloadSQLServerExpress
{
class Program
{

static string LogFileName = "c:\\SQLServerExpressInstallLog.txt";

static void Main(string[] args)
{
string SQLServerExpressLocation = "http://go.microsoft.com/fwlink/?linkid=65212";;
string DotNetFramework2Location = "http://www.microsoft.com/downloads/info.aspx?na=90&p=&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=0856eacb-4362-4b0d-8edd-aab15c5e04f5&u=http%3a%2f%2fdownload.microsoft.com%2fdownload%2f5%2f6%2f7%2f567758a3-759e-473e-bf8f-52154438565a%2fdotnetfx.exe";;
string tempFolderLocation = "c:\\TempFolder\\";
string SQLExpressFileName = "SQLEXPR32.exe";
string DotNetFrameWorkFileName = "dotNetFX.exe";
string DotNetInstallString = tempFolderLocation + DotNetFrameWorkFileName + " /q:a /c:\"install.exe /q\"";
string SQLExpansionString = tempFolderLocation + SQLExpressFileName + " /x:" + tempFolderLocation + " /q";
string SQLInstallString = tempFolderLocation + "setup.exe /settings=" + tempFolderLocation + "template.ini /qn";
string MachineName = Environment.MachineName.ToString();

//not this will have to be changes in production
string InstanceName = "SQL2005";
string ReplicationAccount = "repl";
string ReplicationAccountPassword = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";
string ReplicationDatabase = "SubscriberDB";

ManagedComputer ManagedComputerName = new ManagedComputer(MachineName);

LogMe("Checking for .Net Framework 2.0 Existence");

if (CheckForRequiredDotNetFrameWordEdition() == 0)
{
LogMe("need to install the .Net Framework 2.0");
LogMe("Downloading .Net Framework 2.0");
int retCode = DownLoadFile(DotNetFramework2Location, DotNetFrameWorkFileName, tempFolderLocation);
if (retCode == 0)
{
LogMe("Download failed.");
return;
}
LogMe("Downloaded .Net Framework 2.0");
LogMe("Installing .Net Framework 2.0");
if (InstallSoftware(DotNetFrameWorkFileName, tempFolderLocation, DotNetInstallString) == 0)
{
LogMe("Install of .Net Framework 2.0 failed.");
return;
}
LogMe("Installed .Net Framework 2.0");
}


LogMe("Downloading SQL Server 2005 Express");
if (DownLoadFile(SQLServerExpressLocation, SQLExpressFileName, tempFolderLocation) == 0)
{
LogMe("Download failed.");
return;
}
LogMe("Downloaded SQL Server 2005 Express");

LogMe("Expanding SQL Server 2005 Express");
if (InstallSoftware(SQLExpressFileName, tempFolderLocation, SQLExpansionString) == 0)
{
LogMe("Expanding SQL Server Express 2005 failed.");
return;
}
LogMe("Expanded SQL Server Express 2005");

//need to build the template.ini file
LogMe("Building template.ini");
if (CreateTemplateIni(tempFolderLocation, InstanceName) == 0)
{
LogMe("Creating template.ini failed.");
return;
}
LogMe("Built template.ini");

LogMe("Installing SQL Server 2005 Express");
if (InstallSoftware(SQLExpressFileName, tempFolderLocation, SQLInstallString) == 0)
{
LogMe("Install of SQL Server Express 2005 failed.");
return;
}
LogMe("Installed SQL Server Express 2005");

Server server = new Server(ManagedComputerName.Name.ToString() + "\\" + InstanceName);

LogMe("Creating Replication account!");
if (CreateReplicationAccount(server, ReplicationAccount, ReplicationAccountPassword) == 0)
{
LogMe("Creation of Replication Account failed.");
return;
}
LogMe("Replication account created!");

LogMe("Creating Subscriber database!");
if (CreateUserDatabase(server, ReplicationDatabase) == 0)
{
LogMe("Creation of Subscriber database failed.");
return;

}
LogMe("Created Subscriber database!");

LogMe("Configuring Server Protocols!");
if(ConfigureServerProtocols(ManagedComputerName, InstanceName)==0)
{
LogMe("Configuring Server Protocols failed.");
return;
}
LogMe("Configured Server protocols!");

LogMe("Deleting temp directory.");
try
{
Directory.Delete(tempFolderLocation, true);
}
catch (Exception e)
{
LogMe("Delete temp directory failed");
LogMe(e.Message);
return ;
}
LogMe("Delete temp directory successful.");
LogMe("All Done");
return;
}

private static int CreateTemplateIni(string tempFolderLocation, string InstanceName)
{
LogMe("Creating template.ini");
try
{
TextWriter tw = new StreamWriter(tempFolderLocation + "template.ini");
tw.WriteLine("[Options]");
tw.WriteLine("INSTANCENAME="+InstanceName );
tw.WriteLine("INSTALLSQLDIR=c:\\Program Files\\Microsoft SQL Server\\");
tw.WriteLine("INSTALLSQLDATADIR=c:\\Program Files\\Microsoft SQL Server\\");
tw.WriteLine("USERNAME=XXXXX");
tw.WriteLine("COMPANYNAME=XXXXXXXXXXXXX");
tw.WriteLine("PIDKEY=********");
tw.WriteLine("INSTALLSQLDIR=c:\\Program Files\\Microsoft SQL Server\\");
tw.WriteLine("ADDLOCAL=ALL");
tw.WriteLine("SQLBROWSERAUTOSTART=1");
tw.WriteLine("SQLAUTOSTART=1");
tw.WriteLine("AGTAUTOSTART=1");
tw.WriteLine("SQLACCOUNT=NT AUTHORITY\\NETWORK SERVICE");
tw.WriteLine("SECURITYMODE=SQL");
tw.WriteLine("SQLPASSWORD=XXXXXXXXXXXXXXXXX");
tw.WriteLine("SAPWD=XXXXXXXXXXXX");
tw.WriteLine("ProductCode={6C428277-232D-4CC2-90ED-A1DCFE7DF64F}");
tw.WriteLine("ProductVersion=9.00.1399.06");
tw.WriteLine("ProductLanguage=1033");
tw.WriteLine("SQL_Replication=INSTALLSTATE_LOCAL");
tw.WriteLine("Client_Components=INSTALLSTATE_LOCAL");

tw.Close();
}
catch (Exception e)
{
LogMe("Failed Creating Template.ini");
LogMe(e.Message);
return 0;
}
return -1;
}

private static int InstallSoftware(object FileName, string tempFolderLocation, string InstallString)
{
Process process = new Process();

process.StartInfo.FileName= Environment.GetEnvironmentVariable("COMSPEC");
process.StartInfo.Arguments = "/c "+InstallString;
process.StartInfo.CreateNoWindow = true;
try
{
process.Start();
process.WaitForExit();
process.Close();
}
catch (Exception e)
{
LogMe("Failure to install Software");
LogMe(e.Message);
return 0;
}
return -1;
}

private static int DownLoadFile(string url, string FileName, string tempFolderLocation)
{
try
{
WebClient Client = new WebClient();
Stream strm = Client.OpenRead(url);
LogMe("Starting Download");
if (!Directory.Exists(tempFolderLocation))
Directory.CreateDirectory(tempFolderLocation);
FileStream writeStream = new FileStream(tempFolderLocation + FileName, FileMode.Create, FileAccess.Write);
LogMe("Download Complete");
LogMe("Writing File to disk");
ReadWriteStream(strm, writeStream);
LogMe("All Done!");

}
catch (Exception e)
{
LogMe("Failure! " + e.ToString());
return 0;
}
return -1;
}


private static int CheckForRequiredDotNetFrameWordEdition()
{
if (Environment.Version.Major.ToString() == "2")
{
LogMe(".Net Framework 2.0 Installed");
return -1;
}
else
{
LogMe(".Net Framework 2.0 not Installed");
return 0;
}
}

private static void ReadWriteStream(Stream readStream, FileStream writeStream)
{
int Length = 256;
Byte[] buffer = new Byte[Length];
int bytesRead = readStream.Read(buffer, 0, Length);
// write the required bytes
try
{
while (bytesRead > 0)
{
writeStream.Write(buffer, 0, bytesRead);
bytesRead = readStream.Read(buffer, 0, Length);
}
readStream.Close();
writeStream.Close();
}
catch (Exception e)
{
LogMe("Problem writing file to disk.");
LogMe(e.Message);
}
}
private static int ConfigureServerProtocols(ManagedComputer ManagedComputerName, string InstanceName)
{
try
{
LogMe("checking to see if tcp protocol is enabled");
if (ManagedComputerName.ServerInstances[InstanceName].ServerProtocols["Tcp"].IsEnabled != true)
{
LogMe("Enabling tcp protocol.");
ManagedComputerName.ServerInstances[InstanceName].ServerProtocols["Tcp"].IsEnabled = true;
ManagedComputerName.ServerInstances[InstanceName].ServerProtocols["Tcp"].Alter();
LogMe("Tcp protocol is not enabled.");
}
else
{
LogMe("Tcp protocol already enabled");
}


//checking to see if the client tcp ip protocol is enabled for this machine
//if not enabling it
LogMe("Checking to see if SQL Native Client has tcp protocol enabled.");
if (ManagedComputerName.ClientProtocols["tcp"].IsEnabled != true)
{
LogMe("Enabling SQL Native Client tcp protocol.");
ManagedComputerName.ClientProtocols["tcp"].IsEnabled = true;
ManagedComputerName.ClientProtocols["tcp"].Alter();
LogMe("SQL Native Client tcp protocol is enabled.");
}
else
{
LogMe("SQL Native Client tcp protocol is already enabled.");
}


//checking to see if the default tcp ip port is 1150 for this machine
//if not enabling it
LogMe("Checking to see if SQL Native Client tcp protocol is listening on port 1150.");
if (ManagedComputerName.ClientProtocols["tcp"].ProtocolProperties["Default Port"].Value.ToString() != "1150")
{
LogMe("Configuring SQL Server to listen on port 1150.");
ManagedComputerName.ClientProtocols["tcp"].ProtocolProperties["Default Port"].Value = 1150;
ManagedComputerName.ClientProtocols["tcp"].Alter();
LogMe("SQL Server listening on port 1150.");
}
else
{
LogMe("SQL Server already listening on port 1150.");
}

//restarting the instance to enable these settings
LogMe("Stopping SQL Server");
ManagedComputerName.Services["MSSQL$" + InstanceName].Stop();
LogMe("SQL Server stopped");
LogMe("Starting SQL Server");
//need special handling here!
if(RestartSQLServer(InstanceName)==0)
{
return 0;
}
LogMe("SQL Server started");
}
catch (Exception e)
{
LogMe("Failure to start SQL Server");
LogMe(e.Message);
return 0;
}
return -1;
}

private static int RestartSQLServer(string InstanceName)
{
Process process = new Process();

process.StartInfo.FileName = Environment.GetEnvironmentVariable("COMSPEC");
process.StartInfo.Arguments = "/c NET START MSSQL$" + InstanceName;
process.StartInfo.CreateNoWindow = true;
try
{
process.Start();
LogMe("Sleeping for 30 seconds to allow service to start");
Thread.Sleep(30000);
process.Close();
}
catch (Exception e)
{
LogMe("Failed to restart SQL Server");
LogMe(e.Message);
return 0;
}
return -1;

}

private static int CreateUserDatabase(Server server, string ReplicationDatabase)
{
//checking to see if the tcp ip protocol is enabled for this instance
//if not enabling it

try
{
LogMe("Checking to see if user database already exists.");
if (server.Databases[ReplicationDatabase] == null)
{
LogMe("Creating user database.");
Database database = new Database();
database.Name = ReplicationDatabase;
database.Parent = server;
database.Create();

LogMe("User database created.");
}
else
{
LogMe("User database already exists.");
}
}
catch (Exception e)
{
LogMe("Failure creating Subscriber Database");
LogMe(e.Message);
return 0;
}
return -1;

}

private static int CreateReplicationAccount(Server server, string ReplicationAccount, string ReplicationAccountPassword)
{
try
{
LogMe("Connecting to server");
//server.ConnectionContext.LoginSecure = true;

if (server.Logins[ReplicationAccount] == null)
{
LogMe("replication account does not exist.");
LogMe("Creating replication.");
Login login = new Login(server, ReplicationAccount);
login.LoginType = LoginType.SqlLogin;
login.DefaultDatabase = "master";
login.Create(ReplicationAccountPassword);
LogMe("Replication account Created.");
LogMe("Adding replication account to sysadmin role.");
login.AddToRole("sysadmin");
LogMe("Replication account added to sysadmin role.");
}
else
{
LogMe("Replication account already exists");
}
}
catch (Exception e)
{
LogMe("Failure creating Replication Account");
LogMe(e.Message);
return 0;
}
return -1;
}

private static void LogMe(string Message)
{
Console.WriteLine(Message);
TextWriter tw = new StreamWriter(LogFileName,true);
tw.WriteLine(Message);
tw.Close();
}


}
}




"gstar" <gary.brett@xxxxxxxxx> wrote in message news:ea903b7b-583e-4779-9808-df296bd6e487@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi Hilary and thankyou for your response. Ive just read your article
on this subect [http://www.informit.com/articles/article.aspx?
p=659523&seqNum=2] very good.

1) yes. Consider something like OpenVPN or using WebSynchronization with SQL
Server 2005.

Like the WebSynchronization idea, is this only available on 2005
Enterprise edition? We would really only have 2005 or 2008 standard
SQL.

2) This is not my understanding. I know you can do silent installs to remote
clients of SQL Server 2000, but I am not sure about MSDE. You will need to
create an iss file for this. I have created installation packages for SQL
Server 2005 Express that does complete configuration.

How have you created thses files, 3rd party software?

3) You need to use the encrypted file system to totally encrypt the contents
of the laptop.

Could be an issue as many users will not be part of domains and have
expired recovery certificates. I will look into how this works on
workgroup laptops..

4) Yes. I would definately move to 2005 from end to end. We use SQL Server
Express as opposed to CE.

Thats what I hoped, have you found it straightforward to roll out
offline apps to clients using 2005 & Express? I am a systems admin
being tasked with SQL admin so probably steep learning curve, although
I have managed to setup replication between MSDE2000 & Sql2000 albeit
in a domain environment..

Thanx again

Gary


.