Create SQL cluster on 2003

I have 2 2003 servers each running a separate copy of SQL. I have purchased
a external storage Dell Powervault running RAID 5 to serve as the shared disk
space. I would like to create a SQL cluster with these 2 machines. Each SQL
server has databases that will need to be moved to the shared space. What is
the easiest way to accomplish this?
I was thinking I would need to do backup my databases from both SQL servers.
Create a cluster in 2003 cluster management
Uninstall SQL server from both SQL servers
Install SQL server as a virtual server from one of the 2003 servers.
Restore the SQL databases to the shared disk space
Am I missing anything?
First, your configuration is unsupported. A cluster must be purchased as a
cluster, not just assembled ad-hoc from components that may or may not be on
the cluster Hardware Compatibility list in order to be a supported
configuration. Some storage vendors will certify the entire platform if you
purchase installation services along with the storage device.
Second, your RAID-5 Powervault will run very slowly in a cluster. RAID-5
has significant overhead for writes. Normally a caching controller can
mitigate these issues but with clustering, all SCSI controllers for shared
storage must disable write cache. Since you have the PowerVault divided
into a single array, you will have to install SQL onto the Quorum partition,
again an unsupported configuration. Note that Clustering will work at the
RAID container level, not at the logical partition level. Data and
transaction logs will be on the same physical device so there goes another
bit of performance and recoverability. The whole purpose of SQL Clustering
is to increase availability. I don't see how this configuration will help
reach that goal.
I would talk to my Dell representative about their certified cluster
offerings rather than pursue this path.
Since you did ask for how to do something instead of whether it should be
done, here goes. Create a cluster and install an instance of SQL onto the
cluster (likely a named instance since I would guess that the local
machine(s) already use a default instance). After that, it is a simple
matter to move the databases as you would between any two SQL servers.
Windows 2003 Server has a really great clustering wizard that keeps you from
building a non-functional cluster. Once that is working, you can easily
install SQL clustering according to the instructions in BOL.
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default...b;en-us;314546
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
"Amy Lewis" <AmyLewis@.discussions.microsoft.com> wrote in message
news:EF12ECFD-4BA7-48DD-8605-46D045E39532@.microsoft.com...
>I have 2 2003 servers each running a separate copy of SQL. I have
>purchased
> a external storage Dell Powervault running RAID 5 to serve as the shared
> disk
> space. I would like to create a SQL cluster with these 2 machines. Each
> SQL
> server has databases that will need to be moved to the shared space. What
> is
> the easiest way to accomplish this?
> I was thinking I would need to do backup my databases from both SQL
> servers.
> Create a cluster in 2003 cluster management
> Uninstall SQL server from both SQL servers
> Install SQL server as a virtual server from one of the 2003 servers.
> Restore the SQL databases to the shared disk space
> Am I missing anything?
|||Thanks for the response. I have actually talked with Dell about this and
given the small volume of SQL database activity - they recommended this.
I have not configured my PowerVault yet - would Raid 1 be better relating
to performance? My current 2003 servers have a single RAID 5 configuration -
and the databases are stored in the normal c:\program files\.... and it
seems to be working fine for us. We only about about 20 databases - all
small (the largest is 500M) and all with less than 20 users connected at 1
time.
"Geoff N. Hiten" wrote:

> First, your configuration is unsupported. A cluster must be purchased as a
> cluster, not just assembled ad-hoc from components that may or may not be on
> the cluster Hardware Compatibility list in order to be a supported
> configuration. Some storage vendors will certify the entire platform if you
> purchase installation services along with the storage device.
> Second, your RAID-5 Powervault will run very slowly in a cluster. RAID-5
> has significant overhead for writes. Normally a caching controller can
> mitigate these issues but with clustering, all SCSI controllers for shared
> storage must disable write cache. Since you have the PowerVault divided
> into a single array, you will have to install SQL onto the Quorum partition,
> again an unsupported configuration. Note that Clustering will work at the
> RAID container level, not at the logical partition level. Data and
> transaction logs will be on the same physical device so there goes another
> bit of performance and recoverability. The whole purpose of SQL Clustering
> is to increase availability. I don't see how this configuration will help
> reach that goal.
> I would talk to my Dell representative about their certified cluster
> offerings rather than pursue this path.
> Since you did ask for how to do something instead of whether it should be
> done, here goes. Create a cluster and install an instance of SQL onto the
> cluster (likely a named instance since I would guess that the local
> machine(s) already use a default instance). After that, it is a simple
> matter to move the databases as you would between any two SQL servers.
> Windows 2003 Server has a really great clustering wizard that keeps you from
> building a non-functional cluster. Once that is working, you can easily
> install SQL clustering according to the instructions in BOL.
> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://support.microsoft.com/default...b;en-us;314546
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
>
> "Amy Lewis" <AmyLewis@.discussions.microsoft.com> wrote in message
> news:EF12ECFD-4BA7-48DD-8605-46D045E39532@.microsoft.com...
>
>
|||I am assuming a PV 220S with 14 slots.
2ea RAID-1 drives for Quorum and MSDTC (36GB 15KRPM) Normal best practices
has them apart but with your small scale combining them should be safe.
2ea RAID-1 drives for Logs (73GB 15KRPM)
2ea RAID-1 drives for Data (146GB 15KRPM)
That leaves 8 slots for future expansion. Make sure you have blanks so the
airflow works correctly. You can adjust the sizes of the drives to meet
your needs, but try to keep the Quorum and Logs drives at 15KRPM. The speed
definitely makes a difference. Since you are in a cluster configuration,
the physical location of the drives in the individual slots makes no
difference. This will give you a decent performing system that is also
pretty reliable and recoverable.
Geoff N. Hiten
Microsoft SQL Server MVP
"Amy Lewis" <AmyLewis@.discussions.microsoft.com> wrote in message
news:F0ECD371-DAB4-433C-8445-750EB2D45AA3@.microsoft.com...[vbcol=seagreen]
> Thanks for the response. I have actually talked with Dell about this and
> given the small volume of SQL database activity - they recommended this.
> I have not configured my PowerVault yet - would Raid 1 be better relating
> to performance? My current 2003 servers have a single RAID 5
> configuration -
> and the databases are stored in the normal c:\program files\.... and it
> seems to be working fine for us. We only about about 20 databases - all
> small (the largest is 500M) and all with less than 20 users connected at 1
> time.
> "Geoff N. Hiten" wrote:

Create procedure in target servers

I am creating a job in master server where in one step, it creates
stored procedure in target server. The proc text is exceeding the
limit to directly paste in job scheduler. What is the best way to push
procedure to target servers?
You can either split the sproc into smaller ones to bypass the text size
limit, or save the proc in a text file and use osql in the job to call the
input file.
"tram" <tram_e@.hotmail.com> wrote in message
news:26ee1067.0407130929.62a38b86@.posting.google.c om...
> I am creating a job in master server where in one step, it creates
> stored procedure in target server. The proc text is exceeding the
> limit to directly paste in job scheduler. What is the best way to push
> procedure to target servers?
|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.c om...
|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.c om...
|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.c om...

Create procedure in target servers

I am creating a job in master server where in one step, it creates
stored procedure in target server. The proc text is exceeding the
limit to directly paste in job scheduler. What is the best way to push
procedure to target servers?You can either split the sproc into smaller ones to bypass the text size
limit, or save the proc in a text file and use osql in the job to call the
input file.
"tram" <tram_e@.hotmail.com> wrote in message
news:26ee1067.0407130929.62a38b86@.posting.google.com...
> I am creating a job in master server where in one step, it creates
> stored procedure in target server. The proc text is exceeding the
> limit to directly paste in job scheduler. What is the best way to push
> procedure to target servers?|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP1
2.phx.gbl>...[vbcol=seagreen]
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.com...|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP1
2.phx.gbl>...[vbcol=seagreen]
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.com...|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP1
2.phx.gbl>...[vbcol=seagreen]
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.com...

Create procedure in target servers

I am creating a job in master server where in one step, it creates
stored procedure in target server. The proc text is exceeding the
limit to directly paste in job scheduler. What is the best way to push
procedure to target servers?You can either split the sproc into smaller ones to bypass the text size
limit, or save the proc in a text file and use osql in the job to call the
input file.
"tram" <tram_e@.hotmail.com> wrote in message
news:26ee1067.0407130929.62a38b86@.posting.google.com...
> I am creating a job in master server where in one step, it creates
> stored procedure in target server. The proc text is exceeding the
> limit to directly paste in job scheduler. What is the best way to push
> procedure to target servers?|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP12.phx.gbl>...
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.com...
> > I am creating a job in master server where in one step, it creates
> > stored procedure in target server. The proc text is exceeding the
> > limit to directly paste in job scheduler. What is the best way to push
> > procedure to target servers?|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP12.phx.gbl>...
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.com...
> > I am creating a job in master server where in one step, it creates
> > stored procedure in target server. The proc text is exceeding the
> > limit to directly paste in job scheduler. What is the best way to push
> > procedure to target servers?|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP12.phx.gbl>...
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.com...
> > I am creating a job in master server where in one step, it creates
> > stored procedure in target server. The proc text is exceeding the
> > limit to directly paste in job scheduler. What is the best way to push
> > procedure to target servers?

Create index before or after insert?

I have a number of remote sql servers from which I collect data. Said data i
s
inserted into #temp tables, such as for example:
CREATE TABLE dbo.#tmp
(
x int not null primary key
, y int not null
)
INSERT INTO dbo.#tmp (x, y)
SELECT x, y FROM server.db.dbo.table
For sync'ing this data with local tables it is adventageous to have an index
on y, x:
CREATE NONCLUSTERED INDEX IX_tmp
ON dbo.#tmp (y, x)
Does anyone see any reason to create that index before or after inserting
the records? I.e. is scenario (A) or (B) below better?
(A) 1. Create temp table; 2. Define index; 3. Insert records
(B) 1. Create temp table; 2. Insert records; 3. Create index
My gut tells me that it's a wash, and keeping the index definition with the
table (in code) is better for maintainability, but there's probably no
performance benefit either way. Or maybe there is a performance benefit to
one that I can't think of?
Comments?
Thanks - KenKH wrote:
> I have a number of remote sql servers from which I collect data. Said
> data is inserted into #temp tables, such as for example:
> CREATE TABLE dbo.#tmp
> (
> x int not null primary key
> , y int not null
> )
> INSERT INTO dbo.#tmp (x, y)
> SELECT x, y FROM server.db.dbo.table
> For sync'ing this data with local tables it is adventageous to have
Temp table usage in stored procedures can be a source of recompilation.
To avoid recompiles (which are costly), you should try to avoid
interleaving DML and DDL statements related to temp tables. Therefore,
you are better off defining all your temp tables up front and creating
indexes on them before inserting or otherwise manipulating data in the
tables.
David Gugick
Imceda Software
www.imceda.com|||If you are doing Bulk Insert of a large number of records at once...
Then drop and recreate the indices. I quote from Books OnLine, From the
Bulk Insert Entry.
"If nonclustered indexes are also present on the table, drop these before
copying data into the table. It is generally faster to bulk copy data into a
table without nonclustered indexes, and then to re-create the nonclustered
indexes, rather than bulk copy data into a table with the nonclustered
indexes in place."
The only exception is when you have a clustered Index on teh table, AND you
have the luxury of pre-sorting the data in in the same Order as they will be
in the Clusterd Index (Therefore Inserting the records in Clustered Index
Order). In THis special case, leave the CLustered Index on the table during
the Insert.
"David Gugick" wrote:

> KH wrote:
> Temp table usage in stored procedures can be a source of recompilation.
> To avoid recompiles (which are costly), you should try to avoid
> interleaving DML and DDL statements related to temp tables. Therefore,
> you are better off defining all your temp tables up front and creating
> indexes on them before inserting or otherwise manipulating data in the
> tables.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Create Duplicate SQL Server with Databases

Hi,
Is there a way to copy a SQL Server along with its databases and all other
configuration to other servers? Please keep in mind that all the other
servers are in different Active Directory/domains.
Thank you.Hi,
Thank you for your reply.
Reinstalling SQL is what I would like to avoid. I have to create SQL/Win2K
servers on a regular basis for our clients. I was trying to find a way so I
can simply ghost the server. The base configuration is the same for all our
server so the database do not change until it's been installed. Even then,
only data changes, not the DBs themselves.
Thank you.
"Stressed" <k@.c.co.uk> wrote in message
news:%23I5LeawYDHA.652@.TK2MSFTNGP10.phx.gbl...
> You may be able to install a new copy of sql server, so all the
> settings/registry keys are up to scratch on the new server, and
> then copy everything to the new server, so long as the other server
> has the same configuration and disk letters.
> I've not tried this, though, HTH.
>
> "Dragon" <nopam_baadil@.hotmail.com> wrote in message
> news:eK9VI4oYDHA.2448@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > Is there a way to copy a SQL Server along with its databases and all
other
> > configuration to other servers? Please keep in mind that all the other
> > servers are in different Active Directory/domains.
> >
> > Thank you.
> >
> >
>

create database command in trigger

Hi,

As part of setting up automated replication between two servers, I need an insert trigger on a table in a database on server 1 to run a 'create database xxx' command on server 2. Once I've got that I'm sorted.

I tried using linked servers but didn't get anywhere. Finally, I tried creating a trigger on server 1 which ran a dts package (the dts package contained the SQL to create the database on server 2). The dts pacakge ran on its own (I ran it using dtsrun), but not as part of the trigger.

I know that SQL server doesn't support 'create database' commands in triggers, but I would have thought the dts approach would have got around that. Any suggestions? Here's my trigger

CREATE TRIGGER dblist_trigger
ON dblist
FOR INSERT
AS
commit work
EXEC master..xp_cmdshell 'dtsrun /s mbuksqltst03 /u sa /s /n createdb'

Thanks,

IanPlease explain something more about your process|||Hi,

The application I'm working with uses SQL Server and creates databases as part of its operation. So in essence, I'm trying to replicate an entire server rather than just a particular database. I have a script which will create a full set of replication objects for a given database. The problem I have is that when a database is created on the main server, I can't automatically create a blank database on the replicated server to run my replication objects script against.

I've got the replicated server to maintain a list of databases on the main server (a table called dblist - updated by a trigger on the main server). What I was trying to do was create some sort of trigger which will run a create database command when the dblist table on the replicated server has a row inserted in (indicating a new database has been created on the main server). This syntax works, but not when I use it in a trigger

declare @.sqltxt nvarchar (2000),
@.maxid int,
@.name varchar (256)
set @.maxid=(select max(id) from dblist)
set @.name=(select name from dblist where id=@.maxid)
set @.sqltxt=(select 'create database '+ @.name)
EXEC sp_executesql @.sqlTxt

I have even tried putting this sytax in a separate stored procedure and as a T-SQL object in a dts package. But I still can't get it triggered automatically.

Of course, if there is a more elegant way of setting up the replication, I'm open to suggestions.

I hope this is some use.

Ian