I have a sql2k inside LAN, which is used for a intranet
web site, in the DSN setting of web server side, I am
using DB server's DNS entry for the server name, it
worked properly, and some days ago, when some setting of
our LAN changed, I find this DNS entry can't be used, but
I still can ping that DB server using the same DNS name.
any hint for this problem?if you can ping that DNS name then you should be able to
connect to it through a DSN, try to run this command on a
DOS command prompt:
telnet <dns name of the db server> 1433
if you see a blank screen that means you are connected to
sql server on port 1433 and there is no network
connectivity issue, otherwise you should get an error.
Please also give us more details about your environment,
OS, service packs, mdac version etc. If you can provide us
details of network configuration on db and http servers
then that would really help. Connecting to sql server is
pretty straight forward task if network configuration is
right. Sometime comapnies put even internal webserver on a
DMZ, which would require more network configuration to be
able to connect to a db server inside firewall.
hth.
>--Original Message--
>I have a sql2k inside LAN, which is used for a intranet
>web site, in the DSN setting of web server side, I am
>using DB server's DNS entry for the server name, it
>worked properly, and some days ago, when some setting of
>our LAN changed, I find this DNS entry can't be used, but
>I still can ping that DB server using the same DNS name.
>any hint for this problem?
>.
>
Showing posts with label setting. Show all posts
Showing posts with label setting. Show all posts
Thursday, March 29, 2012
Wednesday, March 21, 2012
Drop the table or Truncate?
I am setting up about 60 or so DTS packages to run every night. These
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.
They are effectively the same, but dropping the table in the right order is
effectively quicker.
Anyway, you could really gain some performance, if you could look into the
possibility of incremental updates, instead of getting rid of the whole lot
and repopulating.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:4092B4F1.6060700@.deltanet.com...
I am setting up about 60 or so DTS packages to run every night. These
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.
|||Narayana Vyas Kondreddi wrote:
> They are effectively the same, but dropping the table in the right order is
> effectively quicker.
I thought so, also. But someone had mentioned that tables are meant to
be permanent, so it would be better to truncate. The problem is just
because they are meant to be permanent, doesn't mean there is a problem
doing it.
Someone else said it would be better to do all the table drops and
creates - then populate them. I'm not sure why that would be better.
Why would dropping them in the correct order be quicker - I assume you
are talking about referential integrity. In my case, I am deleting
everything each time at night.
> Anyway, you could really gain some performance, if you could look into the
> possibility of incremental updates, instead of getting rid of the whole lot
> and repopulating.
I agree. But in our case the tables are already created on a foreign
system and we have no control over the schema, so it would be difficult
to do incremental updates.
Thanks,
Tom.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:4092B4F1.6060700@.deltanet.com...
> I am setting up about 60 or so DTS packages to run every night. These
> packages will be recreating all the tables from a foreign database.
> At the moment, I have them dropping the tables, creating the tables,
> copying all the data to the new table and creating the keys.
> Would it be better to just drop the keys, truncate the tables, copy the
> data and recreate the keys? Or does it really matter? I am, in
> essence, doing the same thing.
> Just trying to see if I am going about the task in the best way.
> Thanks,
> Tom.
>
>
|||Thomas
As Vyas said that dropping the table in the right order is
effectively quicker.
I would a little bit re-phrase his by saying that truncating the table in
the right order is effectively quicker.
As I understood you don't have to drop the table so you have to know
relationship between them and to define the order to truncate tables.( First
truncate a referenced(child) table and then a referncing(father) )
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:40936865.4010204@.deltanet.com...[vbcol=seagreen]
> Narayana Vyas Kondreddi wrote:
is[vbcol=seagreen]
>
> I thought so, also. But someone had mentioned that tables are meant to
> be permanent, so it would be better to truncate. The problem is just
> because they are meant to be permanent, doesn't mean there is a problem
> doing it.
> Someone else said it would be better to do all the table drops and
> creates - then populate them. I'm not sure why that would be better.
> Why would dropping them in the correct order be quicker - I assume you
> are talking about referential integrity. In my case, I am deleting
> everything each time at night.
the[vbcol=seagreen]
lot
>
> I agree. But in our case the tables are already created on a foreign
> system and we have no control over the schema, so it would be difficult
> to do incremental updates.
> Thanks,
> Tom.
>
|||Uri Dimant wrote:
> Thomas
> As Vyas said that dropping the table in the right order is
> effectively quicker.
> I would a little bit re-phrase his by saying that truncating the table in
> the right order is effectively quicker.
> As I understood you don't have to drop the table so you have to know
> relationship between them and to define the order to truncate tables.( First
> truncate a referenced(child) table and then a referncing(father) )
I am running this using DTS and to do a straight copy, the program does
a "Create" with optional "Drop". I was thinking of doing a
"Select/Into". So I would do a "Drop Table" and then do a "Select/Into"
from the foreign database.
Is there a reason why I should use the Create and Insert that DTS does
vs the Select/Into way. I was thinking of doing this so I wouldn't have
to make 60 packages (one for each file copied), since each create would
be different. I would just need to pass the file name in a Global
Variable and let DTS do the Select/Into. This would allow me to create
only one package.
Of course, the other problem would be creating the indexes after the
Select/Into. If I need to have a package for each table just to handle
the Indexes, I might as well let DTS do it's normal Create.
Thanks,
Tom.
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:40936865.4010204@.deltanet.com...
> is
> the
> lot
>
>
|||One thing to consider;
Truncating the table does not get logged in the transaction log. So if
there is a lot of data in the tables, deleting the tables can cause the log
to grow.
Rand
This posting is provided "as is" with no warranties and confers no rights.
sql
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.
They are effectively the same, but dropping the table in the right order is
effectively quicker.
Anyway, you could really gain some performance, if you could look into the
possibility of incremental updates, instead of getting rid of the whole lot
and repopulating.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:4092B4F1.6060700@.deltanet.com...
I am setting up about 60 or so DTS packages to run every night. These
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.
|||Narayana Vyas Kondreddi wrote:
> They are effectively the same, but dropping the table in the right order is
> effectively quicker.
I thought so, also. But someone had mentioned that tables are meant to
be permanent, so it would be better to truncate. The problem is just
because they are meant to be permanent, doesn't mean there is a problem
doing it.
Someone else said it would be better to do all the table drops and
creates - then populate them. I'm not sure why that would be better.
Why would dropping them in the correct order be quicker - I assume you
are talking about referential integrity. In my case, I am deleting
everything each time at night.
> Anyway, you could really gain some performance, if you could look into the
> possibility of incremental updates, instead of getting rid of the whole lot
> and repopulating.
I agree. But in our case the tables are already created on a foreign
system and we have no control over the schema, so it would be difficult
to do incremental updates.
Thanks,
Tom.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:4092B4F1.6060700@.deltanet.com...
> I am setting up about 60 or so DTS packages to run every night. These
> packages will be recreating all the tables from a foreign database.
> At the moment, I have them dropping the tables, creating the tables,
> copying all the data to the new table and creating the keys.
> Would it be better to just drop the keys, truncate the tables, copy the
> data and recreate the keys? Or does it really matter? I am, in
> essence, doing the same thing.
> Just trying to see if I am going about the task in the best way.
> Thanks,
> Tom.
>
>
|||Thomas
As Vyas said that dropping the table in the right order is
effectively quicker.
I would a little bit re-phrase his by saying that truncating the table in
the right order is effectively quicker.
As I understood you don't have to drop the table so you have to know
relationship between them and to define the order to truncate tables.( First
truncate a referenced(child) table and then a referncing(father) )
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:40936865.4010204@.deltanet.com...[vbcol=seagreen]
> Narayana Vyas Kondreddi wrote:
is[vbcol=seagreen]
>
> I thought so, also. But someone had mentioned that tables are meant to
> be permanent, so it would be better to truncate. The problem is just
> because they are meant to be permanent, doesn't mean there is a problem
> doing it.
> Someone else said it would be better to do all the table drops and
> creates - then populate them. I'm not sure why that would be better.
> Why would dropping them in the correct order be quicker - I assume you
> are talking about referential integrity. In my case, I am deleting
> everything each time at night.
the[vbcol=seagreen]
lot
>
> I agree. But in our case the tables are already created on a foreign
> system and we have no control over the schema, so it would be difficult
> to do incremental updates.
> Thanks,
> Tom.
>
|||Uri Dimant wrote:
> Thomas
> As Vyas said that dropping the table in the right order is
> effectively quicker.
> I would a little bit re-phrase his by saying that truncating the table in
> the right order is effectively quicker.
> As I understood you don't have to drop the table so you have to know
> relationship between them and to define the order to truncate tables.( First
> truncate a referenced(child) table and then a referncing(father) )
I am running this using DTS and to do a straight copy, the program does
a "Create" with optional "Drop". I was thinking of doing a
"Select/Into". So I would do a "Drop Table" and then do a "Select/Into"
from the foreign database.
Is there a reason why I should use the Create and Insert that DTS does
vs the Select/Into way. I was thinking of doing this so I wouldn't have
to make 60 packages (one for each file copied), since each create would
be different. I would just need to pass the file name in a Global
Variable and let DTS do the Select/Into. This would allow me to create
only one package.
Of course, the other problem would be creating the indexes after the
Select/Into. If I need to have a package for each table just to handle
the Indexes, I might as well let DTS do it's normal Create.
Thanks,
Tom.
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:40936865.4010204@.deltanet.com...
> is
> the
> lot
>
>
|||One thing to consider;
Truncating the table does not get logged in the transaction log. So if
there is a lot of data in the tables, deleting the tables can cause the log
to grow.
Rand
This posting is provided "as is" with no warranties and confers no rights.
sql
Drop the table or Truncate?
I am setting up about 60 or so DTS packages to run every night. These
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.They are effectively the same, but dropping the table in the right order is
effectively quicker.
Anyway, you could really gain some performance, if you could look into the
possibility of incremental updates, instead of getting rid of the whole lot
and repopulating.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:4092B4F1.6060700@.deltanet.com...
I am setting up about 60 or so DTS packages to run every night. These
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.|||Narayana Vyas Kondreddi wrote:
> They are effectively the same, but dropping the table in the right order i
s
> effectively quicker.
I thought so, also. But someone had mentioned that tables are meant to
be permanent, so it would be better to truncate. The problem is just
because they are meant to be permanent, doesn't mean there is a problem
doing it.
Someone else said it would be better to do all the table drops and
creates - then populate them. I'm not sure why that would be better.
Why would dropping them in the correct order be quicker - I assume you
are talking about referential integrity. In my case, I am deleting
everything each time at night.
> Anyway, you could really gain some performance, if you could look into the
> possibility of incremental updates, instead of getting rid of the whole lo
t
> and repopulating.
I agree. But in our case the tables are already created on a foreign
system and we have no control over the schema, so it would be difficult
to do incremental updates.
Thanks,
Tom.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:4092B4F1.6060700@.deltanet.com...
> I am setting up about 60 or so DTS packages to run every night. These
> packages will be recreating all the tables from a foreign database.
> At the moment, I have them dropping the tables, creating the tables,
> copying all the data to the new table and creating the keys.
> Would it be better to just drop the keys, truncate the tables, copy the
> data and recreate the keys? Or does it really matter? I am, in
> essence, doing the same thing.
> Just trying to see if I am going about the task in the best way.
> Thanks,
> Tom.
>
>|||Thomas
As Vyas said that dropping the table in the right order is
effectively quicker.
I would a little bit re-phrase his by saying that truncating the table in
the right order is effectively quicker.
As I understood you don't have to drop the table so you have to know
relationship between them and to define the order to truncate tables.( First
truncate a referenced(child) table and then a referncing(father) )
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:40936865.4010204@.deltanet.com...
> Narayana Vyas Kondreddi wrote:
>
is[vbcol=seagreen]
>
> I thought so, also. But someone had mentioned that tables are meant to
> be permanent, so it would be better to truncate. The problem is just
> because they are meant to be permanent, doesn't mean there is a problem
> doing it.
> Someone else said it would be better to do all the table drops and
> creates - then populate them. I'm not sure why that would be better.
> Why would dropping them in the correct order be quicker - I assume you
> are talking about referential integrity. In my case, I am deleting
> everything each time at night.
>
the[vbcol=seagreen]
lot[vbcol=seagreen]
>
> I agree. But in our case the tables are already created on a foreign
> system and we have no control over the schema, so it would be difficult
> to do incremental updates.
> Thanks,
> Tom.
>
>|||Uri Dimant wrote:
> Thomas
> As Vyas said that dropping the table in the right order is
> effectively quicker.
> I would a little bit re-phrase his by saying that truncating the table in
> the right order is effectively quicker.
> As I understood you don't have to drop the table so you have to know
> relationship between them and to define the order to truncate tables.( Fir
st
> truncate a referenced(child) table and then a referncing(father) )
I am running this using DTS and to do a straight copy, the program does
a "Create" with optional "Drop". I was thinking of doing a
"Select/Into". So I would do a "Drop Table" and then do a "Select/Into"
from the foreign database.
Is there a reason why I should use the Create and Insert that DTS does
vs the Select/Into way. I was thinking of doing this so I wouldn't have
to make 60 packages (one for each file copied), since each create would
be different. I would just need to pass the file name in a Global
Variable and let DTS do the Select/Into. This would allow me to create
only one package.
Of course, the other problem would be creating the indexes after the
Select/Into. If I need to have a package for each table just to handle
the Indexes, I might as well let DTS do it's normal Create.
Thanks,
Tom.
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:40936865.4010204@.deltanet.com...
>
> is
>
> the
>
> lot
>
>|||One thing to consider;
Truncating the table does not get logged in the transaction log. So if
there is a lot of data in the tables, deleting the tables can cause the log
to grow.
Rand
This posting is provided "as is" with no warranties and confers no rights.
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.They are effectively the same, but dropping the table in the right order is
effectively quicker.
Anyway, you could really gain some performance, if you could look into the
possibility of incremental updates, instead of getting rid of the whole lot
and repopulating.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:4092B4F1.6060700@.deltanet.com...
I am setting up about 60 or so DTS packages to run every night. These
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.|||Narayana Vyas Kondreddi wrote:
> They are effectively the same, but dropping the table in the right order i
s
> effectively quicker.
I thought so, also. But someone had mentioned that tables are meant to
be permanent, so it would be better to truncate. The problem is just
because they are meant to be permanent, doesn't mean there is a problem
doing it.
Someone else said it would be better to do all the table drops and
creates - then populate them. I'm not sure why that would be better.
Why would dropping them in the correct order be quicker - I assume you
are talking about referential integrity. In my case, I am deleting
everything each time at night.
> Anyway, you could really gain some performance, if you could look into the
> possibility of incremental updates, instead of getting rid of the whole lo
t
> and repopulating.
I agree. But in our case the tables are already created on a foreign
system and we have no control over the schema, so it would be difficult
to do incremental updates.
Thanks,
Tom.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:4092B4F1.6060700@.deltanet.com...
> I am setting up about 60 or so DTS packages to run every night. These
> packages will be recreating all the tables from a foreign database.
> At the moment, I have them dropping the tables, creating the tables,
> copying all the data to the new table and creating the keys.
> Would it be better to just drop the keys, truncate the tables, copy the
> data and recreate the keys? Or does it really matter? I am, in
> essence, doing the same thing.
> Just trying to see if I am going about the task in the best way.
> Thanks,
> Tom.
>
>|||Thomas
As Vyas said that dropping the table in the right order is
effectively quicker.
I would a little bit re-phrase his by saying that truncating the table in
the right order is effectively quicker.
As I understood you don't have to drop the table so you have to know
relationship between them and to define the order to truncate tables.( First
truncate a referenced(child) table and then a referncing(father) )
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:40936865.4010204@.deltanet.com...
> Narayana Vyas Kondreddi wrote:
>
is[vbcol=seagreen]
>
> I thought so, also. But someone had mentioned that tables are meant to
> be permanent, so it would be better to truncate. The problem is just
> because they are meant to be permanent, doesn't mean there is a problem
> doing it.
> Someone else said it would be better to do all the table drops and
> creates - then populate them. I'm not sure why that would be better.
> Why would dropping them in the correct order be quicker - I assume you
> are talking about referential integrity. In my case, I am deleting
> everything each time at night.
>
the[vbcol=seagreen]
lot[vbcol=seagreen]
>
> I agree. But in our case the tables are already created on a foreign
> system and we have no control over the schema, so it would be difficult
> to do incremental updates.
> Thanks,
> Tom.
>
>|||Uri Dimant wrote:
> Thomas
> As Vyas said that dropping the table in the right order is
> effectively quicker.
> I would a little bit re-phrase his by saying that truncating the table in
> the right order is effectively quicker.
> As I understood you don't have to drop the table so you have to know
> relationship between them and to define the order to truncate tables.( Fir
st
> truncate a referenced(child) table and then a referncing(father) )
I am running this using DTS and to do a straight copy, the program does
a "Create" with optional "Drop". I was thinking of doing a
"Select/Into". So I would do a "Drop Table" and then do a "Select/Into"
from the foreign database.
Is there a reason why I should use the Create and Insert that DTS does
vs the Select/Into way. I was thinking of doing this so I wouldn't have
to make 60 packages (one for each file copied), since each create would
be different. I would just need to pass the file name in a Global
Variable and let DTS do the Select/Into. This would allow me to create
only one package.
Of course, the other problem would be creating the indexes after the
Select/Into. If I need to have a package for each table just to handle
the Indexes, I might as well let DTS do it's normal Create.
Thanks,
Tom.
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:40936865.4010204@.deltanet.com...
>
> is
>
> the
>
> lot
>
>|||One thing to consider;
Truncating the table does not get logged in the transaction log. So if
there is a lot of data in the tables, deleting the tables can cause the log
to grow.
Rand
This posting is provided "as is" with no warranties and confers no rights.
Drop the table or Truncate?
I am setting up about 60 or so DTS packages to run every night. These
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.They are effectively the same, but dropping the table in the right order is
effectively quicker.
Anyway, you could really gain some performance, if you could look into the
possibility of incremental updates, instead of getting rid of the whole lot
and repopulating.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:4092B4F1.6060700@.deltanet.com...
I am setting up about 60 or so DTS packages to run every night. These
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.|||Narayana Vyas Kondreddi wrote:
> They are effectively the same, but dropping the table in the right order is
> effectively quicker.
I thought so, also. But someone had mentioned that tables are meant to
be permanent, so it would be better to truncate. The problem is just
because they are meant to be permanent, doesn't mean there is a problem
doing it.
Someone else said it would be better to do all the table drops and
creates - then populate them. I'm not sure why that would be better.
Why would dropping them in the correct order be quicker - I assume you
are talking about referential integrity. In my case, I am deleting
everything each time at night.
> Anyway, you could really gain some performance, if you could look into the
> possibility of incremental updates, instead of getting rid of the whole lot
> and repopulating.
I agree. But in our case the tables are already created on a foreign
system and we have no control over the schema, so it would be difficult
to do incremental updates.
Thanks,
Tom.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:4092B4F1.6060700@.deltanet.com...
> I am setting up about 60 or so DTS packages to run every night. These
> packages will be recreating all the tables from a foreign database.
> At the moment, I have them dropping the tables, creating the tables,
> copying all the data to the new table and creating the keys.
> Would it be better to just drop the keys, truncate the tables, copy the
> data and recreate the keys? Or does it really matter? I am, in
> essence, doing the same thing.
> Just trying to see if I am going about the task in the best way.
> Thanks,
> Tom.
>
>|||Thomas
As Vyas said that dropping the table in the right order is
effectively quicker.
I would a little bit re-phrase his by saying that truncating the table in
the right order is effectively quicker.
As I understood you don't have to drop the table so you have to know
relationship between them and to define the order to truncate tables.( First
truncate a referenced(child) table and then a referncing(father) )
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:40936865.4010204@.deltanet.com...
> Narayana Vyas Kondreddi wrote:
> > They are effectively the same, but dropping the table in the right order
is
> > effectively quicker.
>
> I thought so, also. But someone had mentioned that tables are meant to
> be permanent, so it would be better to truncate. The problem is just
> because they are meant to be permanent, doesn't mean there is a problem
> doing it.
> Someone else said it would be better to do all the table drops and
> creates - then populate them. I'm not sure why that would be better.
> Why would dropping them in the correct order be quicker - I assume you
> are talking about referential integrity. In my case, I am deleting
> everything each time at night.
> >
> > Anyway, you could really gain some performance, if you could look into
the
> > possibility of incremental updates, instead of getting rid of the whole
lot
> > and repopulating.
>
> I agree. But in our case the tables are already created on a foreign
> system and we have no control over the schema, so it would be difficult
> to do incremental updates.
> Thanks,
> Tom.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > http://vyaskn.tripod.com/
> > Is .NET important for a database professional?
> > http://vyaskn.tripod.com/poll.htm
> >
> >
> >
> >
> > "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> > news:4092B4F1.6060700@.deltanet.com...
> > I am setting up about 60 or so DTS packages to run every night. These
> > packages will be recreating all the tables from a foreign database.
> >
> > At the moment, I have them dropping the tables, creating the tables,
> > copying all the data to the new table and creating the keys.
> >
> > Would it be better to just drop the keys, truncate the tables, copy the
> > data and recreate the keys? Or does it really matter? I am, in
> > essence, doing the same thing.
> >
> > Just trying to see if I am going about the task in the best way.
> >
> > Thanks,
> >
> > Tom.
> >
> >
> >
> >
>|||Uri Dimant wrote:
> Thomas
> As Vyas said that dropping the table in the right order is
> effectively quicker.
> I would a little bit re-phrase his by saying that truncating the table in
> the right order is effectively quicker.
> As I understood you don't have to drop the table so you have to know
> relationship between them and to define the order to truncate tables.( First
> truncate a referenced(child) table and then a referncing(father) )
I am running this using DTS and to do a straight copy, the program does
a "Create" with optional "Drop". I was thinking of doing a
"Select/Into". So I would do a "Drop Table" and then do a "Select/Into"
from the foreign database.
Is there a reason why I should use the Create and Insert that DTS does
vs the Select/Into way. I was thinking of doing this so I wouldn't have
to make 60 packages (one for each file copied), since each create would
be different. I would just need to pass the file name in a Global
Variable and let DTS do the Select/Into. This would allow me to create
only one package.
Of course, the other problem would be creating the indexes after the
Select/Into. If I need to have a package for each table just to handle
the Indexes, I might as well let DTS do it's normal Create.
Thanks,
Tom.
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:40936865.4010204@.deltanet.com...
>>Narayana Vyas Kondreddi wrote:
>>
>>They are effectively the same, but dropping the table in the right order
> is
>>effectively quicker.
>>
>>I thought so, also. But someone had mentioned that tables are meant to
>>be permanent, so it would be better to truncate. The problem is just
>>because they are meant to be permanent, doesn't mean there is a problem
>>doing it.
>>Someone else said it would be better to do all the table drops and
>>creates - then populate them. I'm not sure why that would be better.
>>Why would dropping them in the correct order be quicker - I assume you
>>are talking about referential integrity. In my case, I am deleting
>>everything each time at night.
>>
>>Anyway, you could really gain some performance, if you could look into
> the
>>possibility of incremental updates, instead of getting rid of the whole
> lot
>>and repopulating.
>>
>>I agree. But in our case the tables are already created on a foreign
>>system and we have no control over the schema, so it would be difficult
>>to do incremental updates.
>>Thanks,
>>Tom.
>>
>>--
>>HTH,
>>Vyas, MVP (SQL Server)
>>http://vyaskn.tripod.com/
>>Is .NET important for a database professional?
>>http://vyaskn.tripod.com/poll.htm
>>
>>
>>"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
>>news:4092B4F1.6060700@.deltanet.com...
>>I am setting up about 60 or so DTS packages to run every night. These
>>packages will be recreating all the tables from a foreign database.
>>At the moment, I have them dropping the tables, creating the tables,
>>copying all the data to the new table and creating the keys.
>>Would it be better to just drop the keys, truncate the tables, copy the
>>data and recreate the keys? Or does it really matter? I am, in
>>essence, doing the same thing.
>>Just trying to see if I am going about the task in the best way.
>>Thanks,
>>Tom.
>>
>>
>|||One thing to consider;
Truncating the table does not get logged in the transaction log. So if
there is a lot of data in the tables, deleting the tables can cause the log
to grow.
Rand
This posting is provided "as is" with no warranties and confers no rights.
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.They are effectively the same, but dropping the table in the right order is
effectively quicker.
Anyway, you could really gain some performance, if you could look into the
possibility of incremental updates, instead of getting rid of the whole lot
and repopulating.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:4092B4F1.6060700@.deltanet.com...
I am setting up about 60 or so DTS packages to run every night. These
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.|||Narayana Vyas Kondreddi wrote:
> They are effectively the same, but dropping the table in the right order is
> effectively quicker.
I thought so, also. But someone had mentioned that tables are meant to
be permanent, so it would be better to truncate. The problem is just
because they are meant to be permanent, doesn't mean there is a problem
doing it.
Someone else said it would be better to do all the table drops and
creates - then populate them. I'm not sure why that would be better.
Why would dropping them in the correct order be quicker - I assume you
are talking about referential integrity. In my case, I am deleting
everything each time at night.
> Anyway, you could really gain some performance, if you could look into the
> possibility of incremental updates, instead of getting rid of the whole lot
> and repopulating.
I agree. But in our case the tables are already created on a foreign
system and we have no control over the schema, so it would be difficult
to do incremental updates.
Thanks,
Tom.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:4092B4F1.6060700@.deltanet.com...
> I am setting up about 60 or so DTS packages to run every night. These
> packages will be recreating all the tables from a foreign database.
> At the moment, I have them dropping the tables, creating the tables,
> copying all the data to the new table and creating the keys.
> Would it be better to just drop the keys, truncate the tables, copy the
> data and recreate the keys? Or does it really matter? I am, in
> essence, doing the same thing.
> Just trying to see if I am going about the task in the best way.
> Thanks,
> Tom.
>
>|||Thomas
As Vyas said that dropping the table in the right order is
effectively quicker.
I would a little bit re-phrase his by saying that truncating the table in
the right order is effectively quicker.
As I understood you don't have to drop the table so you have to know
relationship between them and to define the order to truncate tables.( First
truncate a referenced(child) table and then a referncing(father) )
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:40936865.4010204@.deltanet.com...
> Narayana Vyas Kondreddi wrote:
> > They are effectively the same, but dropping the table in the right order
is
> > effectively quicker.
>
> I thought so, also. But someone had mentioned that tables are meant to
> be permanent, so it would be better to truncate. The problem is just
> because they are meant to be permanent, doesn't mean there is a problem
> doing it.
> Someone else said it would be better to do all the table drops and
> creates - then populate them. I'm not sure why that would be better.
> Why would dropping them in the correct order be quicker - I assume you
> are talking about referential integrity. In my case, I am deleting
> everything each time at night.
> >
> > Anyway, you could really gain some performance, if you could look into
the
> > possibility of incremental updates, instead of getting rid of the whole
lot
> > and repopulating.
>
> I agree. But in our case the tables are already created on a foreign
> system and we have no control over the schema, so it would be difficult
> to do incremental updates.
> Thanks,
> Tom.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > http://vyaskn.tripod.com/
> > Is .NET important for a database professional?
> > http://vyaskn.tripod.com/poll.htm
> >
> >
> >
> >
> > "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> > news:4092B4F1.6060700@.deltanet.com...
> > I am setting up about 60 or so DTS packages to run every night. These
> > packages will be recreating all the tables from a foreign database.
> >
> > At the moment, I have them dropping the tables, creating the tables,
> > copying all the data to the new table and creating the keys.
> >
> > Would it be better to just drop the keys, truncate the tables, copy the
> > data and recreate the keys? Or does it really matter? I am, in
> > essence, doing the same thing.
> >
> > Just trying to see if I am going about the task in the best way.
> >
> > Thanks,
> >
> > Tom.
> >
> >
> >
> >
>|||Uri Dimant wrote:
> Thomas
> As Vyas said that dropping the table in the right order is
> effectively quicker.
> I would a little bit re-phrase his by saying that truncating the table in
> the right order is effectively quicker.
> As I understood you don't have to drop the table so you have to know
> relationship between them and to define the order to truncate tables.( First
> truncate a referenced(child) table and then a referncing(father) )
I am running this using DTS and to do a straight copy, the program does
a "Create" with optional "Drop". I was thinking of doing a
"Select/Into". So I would do a "Drop Table" and then do a "Select/Into"
from the foreign database.
Is there a reason why I should use the Create and Insert that DTS does
vs the Select/Into way. I was thinking of doing this so I wouldn't have
to make 60 packages (one for each file copied), since each create would
be different. I would just need to pass the file name in a Global
Variable and let DTS do the Select/Into. This would allow me to create
only one package.
Of course, the other problem would be creating the indexes after the
Select/Into. If I need to have a package for each table just to handle
the Indexes, I might as well let DTS do it's normal Create.
Thanks,
Tom.
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:40936865.4010204@.deltanet.com...
>>Narayana Vyas Kondreddi wrote:
>>
>>They are effectively the same, but dropping the table in the right order
> is
>>effectively quicker.
>>
>>I thought so, also. But someone had mentioned that tables are meant to
>>be permanent, so it would be better to truncate. The problem is just
>>because they are meant to be permanent, doesn't mean there is a problem
>>doing it.
>>Someone else said it would be better to do all the table drops and
>>creates - then populate them. I'm not sure why that would be better.
>>Why would dropping them in the correct order be quicker - I assume you
>>are talking about referential integrity. In my case, I am deleting
>>everything each time at night.
>>
>>Anyway, you could really gain some performance, if you could look into
> the
>>possibility of incremental updates, instead of getting rid of the whole
> lot
>>and repopulating.
>>
>>I agree. But in our case the tables are already created on a foreign
>>system and we have no control over the schema, so it would be difficult
>>to do incremental updates.
>>Thanks,
>>Tom.
>>
>>--
>>HTH,
>>Vyas, MVP (SQL Server)
>>http://vyaskn.tripod.com/
>>Is .NET important for a database professional?
>>http://vyaskn.tripod.com/poll.htm
>>
>>
>>"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
>>news:4092B4F1.6060700@.deltanet.com...
>>I am setting up about 60 or so DTS packages to run every night. These
>>packages will be recreating all the tables from a foreign database.
>>At the moment, I have them dropping the tables, creating the tables,
>>copying all the data to the new table and creating the keys.
>>Would it be better to just drop the keys, truncate the tables, copy the
>>data and recreate the keys? Or does it really matter? I am, in
>>essence, doing the same thing.
>>Just trying to see if I am going about the task in the best way.
>>Thanks,
>>Tom.
>>
>>
>|||One thing to consider;
Truncating the table does not get logged in the transaction log. So if
there is a lot of data in the tables, deleting the tables can cause the log
to grow.
Rand
This posting is provided "as is" with no warranties and confers no rights.
Sunday, February 26, 2012
Drives in a cluster environment
Hi,
I have a SAN and configuring a cluster on SQL 2005. I initially created a Quorum drive when setting up the cluster and now added 4 more drives to the physical node but when I try to install SQL that drive cannot be located.
Do we need to create all the drives when installing the cluster or what is the way to add the drives later on.
Thanks
Anup
I remember that adding a drive for sql requires the drive to be added to the resource group, I assume you have to create a resource group prior to install?|||It's one thing to add them locally - did you add them to the cluster via Cluster Admin as well? There's a procedure for adding drives after the cluster is already configured. Are they seen in CluAdmin? If not, you did not add them properly.
You also didn't mention MS DTC. That needs its own drive now as well in its own group with an IP, name, and DTC resource.
|||Thanks All.
With some troubleshooting and tips from this forums the cluster is up and running with SQL 2005. However I do not understand whi we need a seperate drive for MSDTC cant we share the quorum drive to do this. Can you explain how to size this like perf issues, disks needed etc.
Thanks
|||
For failover clusters, you always need to think in terms of "units of failover". If you put the MSDTC data on the same drive as the quorum, you would need to tie MSDTC to the cluster service. You then create dependancies between MSDTC and the cluster service that shouldn't be there.
In general, you want to think in terms of a service and its associated resources. That bundle needs to be independant from all other bundles on the cluster so that it can move from node to node independant of other bundles (resource groups).
For disk storage, the unit of availability is the physical disk (or LUN in the case of a SAN array). You can't have one partition of a disk mounted to one node and another partition mounted to another node.
So, it's not a matter of capacity or throughput or perf issues, it is an availability issue.
|||Kevin Farlee wrote:
For failover clusters, you always need to think in terms of "units of failover". If you put the MSDTC data on the same drive as the quorum, you would need to tie MSDTC to the cluster service. You then create dependancies between MSDTC and the cluster service that shouldn't be there.
Just to reinforce - this is not a recommended configuration. It only existed with Windows 2000 because of comclust and that has (thankfully) been taken away in W2K3. Always put MS DTC in a separate group.
In the cases where MS DTC is heavily used (such as BTS), it could potentially affect the availability of the quorum disk (i.e. if it gets filled up with DTC log). You don't want to go there.
|||
Assuming you added the new drives as a disk resource in cluster manager, did you also make those new drives a Dependency of SQL Server? If not, SQL Server will not be able to see them.
|||
Thanks. Now I am getting to understand the concepts. However one last question if I have a active/active cluster then do i need to setup 2 MSDTC groups one in each node?
I appreciate all the help throughout .
|||
No, one DTC per cluster. It is shared with everything else in the cluster.
You will need separate dedicated disks and such for your other SQL instance(s) though.
Subscribe to:
Posts (Atom)