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

Tech-Archive recommends: Fix windows errors by optimizing your registry

Wow Hilary that is great information, Im going to get a demo
environment up today hopefully and try to install.

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

Excellent, I have the standard edition for 180 days so plenty of time
to test..


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).

Brilliant information and thank you for sharing it with me.

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=...";;
            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"]..I­sEnabled
!= true)
                {
                    LogMe("Enabling tcp protocol.");
                    ManagedComputerName.ServerInstances[InstanceName].ServerProtocols["Tcp"].Is­Enabled
= true;
                    ManagedComputerName.ServerInstances[InstanceName].ServerProtocols["Tcp"].Al­ter();
                    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.br...@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

.

Quantcast