Showing posts with label guys. Show all posts
Showing posts with label guys. Show all posts

Friday, March 23, 2012

Name Resolution and Dynamic IPs

Hi Guys - This may fall into a connection category, but I was wondering
if you had the same problem specially when dealing with replication.
One of our clients is having the following problems:
1) My client computers can see the server computer by name and/or by IP
address
2) My server computer cannot see my client computers by name, only by IP
address which is dynamic
3) Client computers have a dynamic IP address
4) Client computers have a machine name but this name cannot be resolved by
a DNS server
Is there any way around this problem? How does SQL Server's name get
resolved in this scenario?
Since we are using merge replication and we cannot configure it if the IP on
the client is dynamic, then the name must be resolved somehow, right?
Any help will be appreciated,
Maer
Maer,
AFAIK what you're talking about should be possible using pull subscriptions.
If there are any IP issues during setup you could use a HOSTS file (on the
publisher/distributor) to help out.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||The absolute best way to do this is to
1) configure your publication for anonymous subscribers
2) use ActiveX Controls to pull your subscription. ActiveX Controls have
DistributorNetwork/DistributorAddress and PublisherNetwork/PublisherAddress
properties which allow your Subscriber to connect to your Publisher on the
fly without having to use hosts files or Client Network Utility on each
Subscriber.
"Maer" <maer@.auditleverage.com> wrote in message
news:uZ9xkrS6FHA.3976@.TK2MSFTNGP15.phx.gbl...
> Hi Guys - This may fall into a connection category, but I was wondering
> if you had the same problem specially when dealing with replication.
> One of our clients is having the following problems:
> 1) My client computers can see the server computer by name and/or by IP
> address
> 2) My server computer cannot see my client computers by name, only by IP
> address which is dynamic
> 3) Client computers have a dynamic IP address
> 4) Client computers have a machine name but this name cannot be resolved
> by a DNS server
> Is there any way around this problem? How does SQL Server's name get
> resolved in this scenario?
> Since we are using merge replication and we cannot configure it if the IP
> on the client is dynamic, then the name must be resolved somehow, right?
> Any help will be appreciated,
> Maer
>
>
|||I think you got a DNS problem.
If you are using VPN you can consider changing your VPN properties so it
registers in the DNS server or fixing the IP Address for the dial up user.
Also, using pull anonymous subscriptions your publisher does not need to be
to 'get' to your subscribers.
Jos.
"Maer" <maer@.auditleverage.com> wrote in message
news:uZ9xkrS6FHA.3976@.TK2MSFTNGP15.phx.gbl...
> Hi Guys - This may fall into a connection category, but I was
wondering
> if you had the same problem specially when dealing with replication.
> One of our clients is having the following problems:
> 1) My client computers can see the server computer by name and/or by IP
> address
> 2) My server computer cannot see my client computers by name, only by IP
> address which is dynamic
> 3) Client computers have a dynamic IP address
> 4) Client computers have a machine name but this name cannot be resolved
by
> a DNS server
> Is there any way around this problem? How does SQL Server's name get
> resolved in this scenario?
> Since we are using merge replication and we cannot configure it if the IP
on
> the client is dynamic, then the name must be resolved somehow, right?
> Any help will be appreciated,
> Maer
>
>

Monday, March 12, 2012

MySQL to MSSQL or vice versa

Hey Guys, are you aware of anyone moving from MYSQL to MSSQL or vice versa - what were your experiences - Mark Smith

I don't have any personal experience using SSIS with MySQL, but there is a decent volume of discussion on these forums on the subject:

http://forums.microsoft.com/MSDN/Search/Search.aspx?words=mysql&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80

There's also quite a few articles out there in general:

http://www.google.com/search?hl=en&q=ssis+mysql

http://groups.google.com/groups?um=1&tab=wg&hl=en&q=ssis%20mysql

Saturday, February 25, 2012

My SQL is rusty

Hi Guys,
Im really rusty on SQL queries so a little help would be appreciated. I have
a users table
UserID Int
UserName String
Authoriser Int
I am trying to work out one query to return the authorisers name given the
users ID. Can this be done in one query string ?
Best Regards
The Inimitable Mr NewbieHope I'm not wrong here, but just a simple select statement would suffice.
Select Authoriser
From users
Where UserID = <whatever userid is passed>
"Mr Newbie" wrote:

> Hi Guys,
> Im really rusty on SQL queries so a little help would be appreciated. I ha
ve
> a users table
> UserID Int
> UserName String
> Authoriser Int
> I am trying to work out one query to return the authorisers name given the
> users ID. Can this be done in one query string ?
>
> --
> Best Regards
> The Inimitable Mr Newbie o?o
>
>|||
select Authorisers.AuthoriserName
from Users,
Authorisers
where UserID = @.UserId (variable or literal)
and Authorisers.Authoriser = Users.Authoriser
Assuming you have a table "Authorisers"
Authoriser int
AuthoriserName String
"Todd S" <Todd S@.discussions.microsoft.com> wrote in message
news:5C2ED2DF-BAEB-4DD4-A7A6-4B060E96C2E8@.microsoft.com...
> Hope I'm not wrong here, but just a simple select statement would suffice.
> Select Authoriser
> From users
> Where UserID = <whatever userid is passed>
>
> "Mr Newbie" wrote:
>|||Actually, I only have the one table so I was looking for a way to select the
Authorisers name using the ID stored in same table.
Best Regards
The Inimitable Mr Newbie
"Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
news:u6CvscU6FHA.808@.TK2MSFTNGP09.phx.gbl...
>
> select Authorisers.AuthoriserName
> from Users,
> Authorisers
> where UserID = @.UserId (variable or literal)
> and Authorisers.Authoriser = Users.Authoriser
> Assuming you have a table "Authorisers"
> Authoriser int
> AuthoriserName String
>
> "Todd S" <Todd S@.discussions.microsoft.com> wrote in message
> news:5C2ED2DF-BAEB-4DD4-A7A6-4B060E96C2E8@.microsoft.com...
>|||SELECT Users.UserName,
Authorisers.UserName as AuthoriserName
FROM Users JOIN Users as Authorisers ON Users.Authoriser =
Authorisers.UserID
HTH,
Stu|||That looks right.
Thanks very much for your help, I will test it out and post a feedback post.
Best Regards
The Inimitable Mr Newbie
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1131993036.132049.243520@.z14g2000cwz.googlegroups.com...
> SELECT Users.UserName,
> Authorisers.UserName as AuthoriserName
> FROM Users JOIN Users as Authorisers ON Users.Authoriser =
> Authorisers.UserID
> HTH,
> Stu
>|||That does not work it returns all the rows, its very odd
Best Regards
The Inimitable Mr Newbie
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1131993036.132049.243520@.z14g2000cwz.googlegroups.com...
> SELECT Users.UserName,
> Authorisers.UserName as AuthoriserName
> FROM Users JOIN Users as Authorisers ON Users.Authoriser =
> Authorisers.UserID
> HTH,
> Stu
>|||Try
SELECT Users.UserName,
Authorisers.UserName as AuthoriserName
FROM Users JOIN Users as Authorisers ON Users.Authoriser =
Authorisers.UserID
Where Users.UserID= [the value of the user]
"Mr Newbie" wrote:

> That does not work it returns all the rows, its very odd
> --
> Best Regards
> The Inimitable Mr Newbie o?o
> "Stu" <stuart.ainsworth@.gmail.com> wrote in message
> news:1131993036.132049.243520@.z14g2000cwz.googlegroups.com...
>
>|||Perfect !!!
Many Thanks Mike. - Appreciated Greatly
Best Regards
The Inimitable Mr Newbie
"Mike K" <MikeK@.discussions.microsoft.com> wrote in message
news:939CA4BD-3119-4013-B35C-F56B972904A3@.microsoft.com...
> Try
> SELECT Users.UserName,
> Authorisers.UserName as AuthoriserName
> FROM Users JOIN Users as Authorisers ON Users.Authoriser =
> Authorisers.UserID
> Where Users.UserID= [the value of the user]
>
> "Mr Newbie" wrote:
>|||Sorry, I misread your original post. I thought you were tyring to
return all rows.
Stu

Monday, February 20, 2012

My new SQL Server

Hey guys,
I was hoping some of you people could shed some light on something
that's been puzzling me this morning. I had a SQL 7 machine with a DB that
contained millions of images. It was on a dual 1GHz P3 box with 2 GB of ram
running Windows NT. This machine was really old and so mission critical no
one wanted to touch it. Anyway, the nightly backup of this DB was
approaching 15 hours and the size was approaching the 137gb limitation in
both NT and the BIOS. I convinced my work that to fix these 2 problems we
need to do 2 things.
1.) Buy a new server, Dual 2.6GHz 2Gb RAM running windows 2000 server.
This will improve performance and upgrade a server that's in dire need of an
upgrade anyway and, most importantly, jump the 137gb hurdle.
2.) Move these 1 million plus images out of a DB structure and onto a
mirrored file structure. This will greatly reduce the backup time and the
actual size of the DB.
Well, I got the server...and I set it all up like I wanted except for
moving the images out of the DB. The DB backed up like normal last night
and I noticed that instead of 15 hours it successfully completed in 2.
Can the new servers speed really have been that effective in shortening
the backup time. I was expecting maybe a little faster...like from 15 hours
to maybe...at best 10, but, 2 hours. Wow!! Is this even possible or should
I look into some problem with the backup? If this is correct I wouldn't
need to move the images into a file structure. From what I have read,
because these images are small and used on our web pages, a database
structure like we have currently would be the better choice.
If anyone can shed some light on why the huge difference in backup times
it would be much appreciated. Any suggestions on what I should do with
these images are welcome too.
Thanks,
-Scott
Hi
Windows 2003 would probably bring better IO performance as Microsoft did a
lot of work on it.
If your DB is not yet dived up into file groups, do so. Then you can backup
and restore a single file group. This will help a lot in a DR scenario.
I like to store Images in a DB. A file system needs complicated directory
structure to support than number of images. Keeping the DB and images in sync
is another issue too.
Microsoft has proven with the TerraServer project that SQL server is
efficient when storing images in a DB.
Regards
Mike
"Scott Elgram" wrote:

> Hey guys,
> I was hoping some of you people could shed some light on something
> that's been puzzling me this morning. I had a SQL 7 machine with a DB that
> contained millions of images. It was on a dual 1GHz P3 box with 2 GB of ram
> running Windows NT. This machine was really old and so mission critical no
> one wanted to touch it. Anyway, the nightly backup of this DB was
> approaching 15 hours and the size was approaching the 137gb limitation in
> both NT and the BIOS. I convinced my work that to fix these 2 problems we
> need to do 2 things.
> 1.) Buy a new server, Dual 2.6GHz 2Gb RAM running windows 2000 server.
> This will improve performance and upgrade a server that's in dire need of an
> upgrade anyway and, most importantly, jump the 137gb hurdle.
> 2.) Move these 1 million plus images out of a DB structure and onto a
> mirrored file structure. This will greatly reduce the backup time and the
> actual size of the DB.
> Well, I got the server...and I set it all up like I wanted except for
> moving the images out of the DB. The DB backed up like normal last night
> and I noticed that instead of 15 hours it successfully completed in 2.
> Can the new servers speed really have been that effective in shortening
> the backup time. I was expecting maybe a little faster...like from 15 hours
> to maybe...at best 10, but, 2 hours. Wow!! Is this even possible or should
> I look into some problem with the backup? If this is correct I wouldn't
> need to move the images into a file structure. From what I have read,
> because these images are small and used on our web pages, a database
> structure like we have currently would be the better choice.
> If anyone can shed some light on why the huge difference in backup times
> it would be much appreciated. Any suggestions on what I should do with
> these images are welcome too.
> Thanks,
> --
> -Scott
>
>
|||Have you considered a third-party backup tool, e.g. SQL LiteSpeed? I think
you'll find even greater performance increases (although images may not
compress well depending on the format so that's hard to say)...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:OKM3nkcGFHA.3068@.tk2msftngp13.phx.gbl...
> Hey guys,
> I was hoping some of you people could shed some light on something
> that's been puzzling me this morning. I had a SQL 7 machine with a DB
that
> contained millions of images. It was on a dual 1GHz P3 box with 2 GB of
ram
> running Windows NT. This machine was really old and so mission critical
no
> one wanted to touch it. Anyway, the nightly backup of this DB was
> approaching 15 hours and the size was approaching the 137gb limitation in
> both NT and the BIOS. I convinced my work that to fix these 2 problems we
> need to do 2 things.
> 1.) Buy a new server, Dual 2.6GHz 2Gb RAM running windows 2000 server.
> This will improve performance and upgrade a server that's in dire need of
an
> upgrade anyway and, most importantly, jump the 137gb hurdle.
> 2.) Move these 1 million plus images out of a DB structure and onto a
> mirrored file structure. This will greatly reduce the backup time and the
> actual size of the DB.
> Well, I got the server...and I set it all up like I wanted except for
> moving the images out of the DB. The DB backed up like normal last night
> and I noticed that instead of 15 hours it successfully completed in 2.
> Can the new servers speed really have been that effective in
shortening
> the backup time. I was expecting maybe a little faster...like from 15
hours
> to maybe...at best 10, but, 2 hours. Wow!! Is this even possible or
should
> I look into some problem with the backup? If this is correct I wouldn't
> need to move the images into a file structure. From what I have read,
> because these images are small and used on our web pages, a database
> structure like we have currently would be the better choice.
> If anyone can shed some light on why the huge difference in backup
times
> it would be much appreciated. Any suggestions on what I should do with
> these images are welcome too.
> Thanks,
> --
> -Scott
>
|||Hey thanks,
Yeah, I looked into windows 2003 but given the size of the company and
the cost of the new server they wanted to save money. We already had a
spare copy of windows 2000 server so I used that. I also wanted to upgrade
to SQL 2000 but like with server 2003 with all the licenses I'd need they
said it would be too expensive.
What do you mean by my DB being dived up into file groups? Are you
suggesting I divide the actual database into multiple databases, i.e. a 2003
database containing images from 2003 and so on?
So is it possible then that just the huge jump in speed between my old
server and the new one solved my problem of the 15 hour backup? I still
find it hard to believe that it would make 86% reduction in time.
-Scott
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:7153294A-229F-4B97-95B1-D6A055450CC2@.microsoft.com...
> Hi
> Windows 2003 would probably bring better IO performance as Microsoft did a
> lot of work on it.
> If your DB is not yet dived up into file groups, do so. Then you can
backup
> and restore a single file group. This will help a lot in a DR scenario.
> I like to store Images in a DB. A file system needs complicated directory
> structure to support than number of images. Keeping the DB and images in
sync[vbcol=seagreen]
> is another issue too.
> Microsoft has proven with the TerraServer project that SQL server is
> efficient when storing images in a DB.
> Regards
> Mike
> "Scott Elgram" wrote:
that[vbcol=seagreen]
ram[vbcol=seagreen]
no[vbcol=seagreen]
in[vbcol=seagreen]
we[vbcol=seagreen]
of an[vbcol=seagreen]
the[vbcol=seagreen]
for[vbcol=seagreen]
night[vbcol=seagreen]
shortening[vbcol=seagreen]
hours[vbcol=seagreen]
should[vbcol=seagreen]
times[vbcol=seagreen]

My new SQL Server

Hey guys,
I was hoping some of you people could shed some light on something
that's been puzzling me this morning. I had a SQL 7 machine with a DB that
contained millions of images. It was on a dual 1GHz P3 box with 2 GB of ram
running Windows NT. This machine was really old and so mission critical no
one wanted to touch it. Anyway, the nightly backup of this DB was
approaching 15 hours and the size was approaching the 137gb limitation in
both NT and the BIOS. I convinced my work that to fix these 2 problems we
need to do 2 things.
1.) Buy a new server, Dual 2.6GHz 2Gb RAM running Windows 2000 server.
This will improve performance and upgrade a server that's in dire need of an
upgrade anyway and, most importantly, jump the 137gb hurdle.
2.) Move these 1 million plus images out of a DB structure and onto a
mirrored file structure. This will greatly reduce the backup time and the
actual size of the DB.
Well, I got the server...and I set it all up like I wanted except for
moving the images out of the DB. The DB backed up like normal last night
and I noticed that instead of 15 hours it successfully completed in 2.
Can the new servers speed really have been that effective in shortening
the backup time. I was expecting maybe a little faster...like from 15 hours
to maybe...at best 10, but, 2 hours. Wow!! Is this even possible or should
I look into some problem with the backup? If this is correct I wouldn't
need to move the images into a file structure. From what I have read,
because these images are small and used on our web pages, a database
structure like we have currently would be the better choice.
If anyone can shed some light on why the huge difference in backup times
it would be much appreciated. Any suggestions on what I should do with
these images are welcome too.
Thanks,
--
-ScottHi
Windows 2003 would probably bring better IO performance as Microsoft did a
lot of work on it.
If your DB is not yet dived up into file groups, do so. Then you can backup
and restore a single file group. This will help a lot in a DR scenario.
I like to store Images in a DB. A file system needs complicated directory
structure to support than number of images. Keeping the DB and images in syn
c
is another issue too.
Microsoft has proven with the TerraServer project that SQL server is
efficient when storing images in a DB.
Regards
Mike
"Scott Elgram" wrote:

> Hey guys,
> I was hoping some of you people could shed some light on something
> that's been puzzling me this morning. I had a SQL 7 machine with a DB tha
t
> contained millions of images. It was on a dual 1GHz P3 box with 2 GB of r
am
> running Windows NT. This machine was really old and so mission critical n
o
> one wanted to touch it. Anyway, the nightly backup of this DB was
> approaching 15 hours and the size was approaching the 137gb limitation in
> both NT and the BIOS. I convinced my work that to fix these 2 problems we
> need to do 2 things.
> 1.) Buy a new server, Dual 2.6GHz 2Gb RAM running Windows 2000 server.
> This will improve performance and upgrade a server that's in dire need of
an
> upgrade anyway and, most importantly, jump the 137gb hurdle.
> 2.) Move these 1 million plus images out of a DB structure and onto a
> mirrored file structure. This will greatly reduce the backup time and the
> actual size of the DB.
> Well, I got the server...and I set it all up like I wanted except for
> moving the images out of the DB. The DB backed up like normal last night
> and I noticed that instead of 15 hours it successfully completed in 2.
> Can the new servers speed really have been that effective in shortenin
g
> the backup time. I was expecting maybe a little faster...like from 15 hou
rs
> to maybe...at best 10, but, 2 hours. Wow!! Is this even possible or shou
ld
> I look into some problem with the backup? If this is correct I wouldn't
> need to move the images into a file structure. From what I have read,
> because these images are small and used on our web pages, a database
> structure like we have currently would be the better choice.
> If anyone can shed some light on why the huge difference in backup tim
es
> it would be much appreciated. Any suggestions on what I should do with
> these images are welcome too.
> Thanks,
> --
> -Scott
>
>|||Have you considered a third-party backup tool, e.g. SQL LiteSpeed? I think
you'll find even greater performance increases (although images may not
compress well depending on the format so that's hard to say)...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:OKM3nkcGFHA.3068@.tk2msftngp13.phx.gbl...
> Hey guys,
> I was hoping some of you people could shed some light on something
> that's been puzzling me this morning. I had a SQL 7 machine with a DB
that
> contained millions of images. It was on a dual 1GHz P3 box with 2 GB of
ram
> running Windows NT. This machine was really old and so mission critical
no
> one wanted to touch it. Anyway, the nightly backup of this DB was
> approaching 15 hours and the size was approaching the 137gb limitation in
> both NT and the BIOS. I convinced my work that to fix these 2 problems we
> need to do 2 things.
> 1.) Buy a new server, Dual 2.6GHz 2Gb RAM running Windows 2000 server.
> This will improve performance and upgrade a server that's in dire need of
an
> upgrade anyway and, most importantly, jump the 137gb hurdle.
> 2.) Move these 1 million plus images out of a DB structure and onto a
> mirrored file structure. This will greatly reduce the backup time and the
> actual size of the DB.
> Well, I got the server...and I set it all up like I wanted except for
> moving the images out of the DB. The DB backed up like normal last night
> and I noticed that instead of 15 hours it successfully completed in 2.
> Can the new servers speed really have been that effective in
shortening
> the backup time. I was expecting maybe a little faster...like from 15
hours
> to maybe...at best 10, but, 2 hours. Wow!! Is this even possible or
should
> I look into some problem with the backup? If this is correct I wouldn't
> need to move the images into a file structure. From what I have read,
> because these images are small and used on our web pages, a database
> structure like we have currently would be the better choice.
> If anyone can shed some light on why the huge difference in backup
times
> it would be much appreciated. Any suggestions on what I should do with
> these images are welcome too.
> Thanks,
> --
> -Scott
>|||Hey thanks,
Yeah, I looked into windows 2003 but given the size of the company and
the cost of the new server they wanted to save money. We already had a
spare copy of Windows 2000 server so I used that. I also wanted to upgrade
to SQL 2000 but like with server 2003 with all the licenses I'd need they
said it would be too expensive.
What do you mean by my DB being dived up into file groups? Are you
suggesting I divide the actual database into multiple databases, i.e. a 2003
database containing images from 2003 and so on?
So is it possible then that just the huge jump in speed between my old
server and the new one solved my problem of the 15 hour backup? I still
find it hard to believe that it would make 86% reduction in time.
-Scott
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:7153294A-229F-4B97-95B1-D6A055450CC2@.microsoft.com...
> Hi
> Windows 2003 would probably bring better IO performance as Microsoft did a
> lot of work on it.
> If your DB is not yet dived up into file groups, do so. Then you can
backup
> and restore a single file group. This will help a lot in a DR scenario.
> I like to store Images in a DB. A file system needs complicated directory
> structure to support than number of images. Keeping the DB and images in
sync[vbcol=seagreen]
> is another issue too.
> Microsoft has proven with the TerraServer project that SQL server is
> efficient when storing images in a DB.
> Regards
> Mike
> "Scott Elgram" wrote:
>
that[vbcol=seagreen]
ram[vbcol=seagreen]
no[vbcol=seagreen]
in[vbcol=seagreen]
we[vbcol=seagreen]
of an[vbcol=seagreen]
the[vbcol=seagreen]
for[vbcol=seagreen]
night[vbcol=seagreen]
shortening[vbcol=seagreen]
hours[vbcol=seagreen]
should[vbcol=seagreen]
times[vbcol=seagreen]

My new SQL Server

Hey guys,
I was hoping some of you people could shed some light on something
that's been puzzling me this morning. I had a SQL 7 machine with a DB that
contained millions of images. It was on a dual 1GHz P3 box with 2 GB of ram
running Windows NT. This machine was really old and so mission critical no
one wanted to touch it. Anyway, the nightly backup of this DB was
approaching 15 hours and the size was approaching the 137gb limitation in
both NT and the BIOS. I convinced my work that to fix these 2 problems we
need to do 2 things.
1.) Buy a new server, Dual 2.6GHz 2Gb RAM running windows 2000 server.
This will improve performance and upgrade a server that's in dire need of an
upgrade anyway and, most importantly, jump the 137gb hurdle.
2.) Move these 1 million plus images out of a DB structure and onto a
mirrored file structure. This will greatly reduce the backup time and the
actual size of the DB.
Well, I got the server...and I set it all up like I wanted except for
moving the images out of the DB. The DB backed up like normal last night
and I noticed that instead of 15 hours it successfully completed in 2.
Can the new servers speed really have been that effective in shortening
the backup time. I was expecting maybe a little faster...like from 15 hours
to maybe...at best 10, but, 2 hours. Wow!! Is this even possible or should
I look into some problem with the backup? If this is correct I wouldn't
need to move the images into a file structure. From what I have read,
because these images are small and used on our web pages, a database
structure like we have currently would be the better choice.
If anyone can shed some light on why the huge difference in backup times
it would be much appreciated. Any suggestions on what I should do with
these images are welcome too.
Thanks,
--
-ScottHi
Windows 2003 would probably bring better IO performance as Microsoft did a
lot of work on it.
If your DB is not yet dived up into file groups, do so. Then you can backup
and restore a single file group. This will help a lot in a DR scenario.
I like to store Images in a DB. A file system needs complicated directory
structure to support than number of images. Keeping the DB and images in sync
is another issue too.
Microsoft has proven with the TerraServer project that SQL server is
efficient when storing images in a DB.
Regards
Mike
"Scott Elgram" wrote:
> Hey guys,
> I was hoping some of you people could shed some light on something
> that's been puzzling me this morning. I had a SQL 7 machine with a DB that
> contained millions of images. It was on a dual 1GHz P3 box with 2 GB of ram
> running Windows NT. This machine was really old and so mission critical no
> one wanted to touch it. Anyway, the nightly backup of this DB was
> approaching 15 hours and the size was approaching the 137gb limitation in
> both NT and the BIOS. I convinced my work that to fix these 2 problems we
> need to do 2 things.
> 1.) Buy a new server, Dual 2.6GHz 2Gb RAM running windows 2000 server.
> This will improve performance and upgrade a server that's in dire need of an
> upgrade anyway and, most importantly, jump the 137gb hurdle.
> 2.) Move these 1 million plus images out of a DB structure and onto a
> mirrored file structure. This will greatly reduce the backup time and the
> actual size of the DB.
> Well, I got the server...and I set it all up like I wanted except for
> moving the images out of the DB. The DB backed up like normal last night
> and I noticed that instead of 15 hours it successfully completed in 2.
> Can the new servers speed really have been that effective in shortening
> the backup time. I was expecting maybe a little faster...like from 15 hours
> to maybe...at best 10, but, 2 hours. Wow!! Is this even possible or should
> I look into some problem with the backup? If this is correct I wouldn't
> need to move the images into a file structure. From what I have read,
> because these images are small and used on our web pages, a database
> structure like we have currently would be the better choice.
> If anyone can shed some light on why the huge difference in backup times
> it would be much appreciated. Any suggestions on what I should do with
> these images are welcome too.
> Thanks,
> --
> -Scott
>
>|||Have you considered a third-party backup tool, e.g. SQL LiteSpeed? I think
you'll find even greater performance increases (although images may not
compress well depending on the format so that's hard to say)...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:OKM3nkcGFHA.3068@.tk2msftngp13.phx.gbl...
> Hey guys,
> I was hoping some of you people could shed some light on something
> that's been puzzling me this morning. I had a SQL 7 machine with a DB
that
> contained millions of images. It was on a dual 1GHz P3 box with 2 GB of
ram
> running Windows NT. This machine was really old and so mission critical
no
> one wanted to touch it. Anyway, the nightly backup of this DB was
> approaching 15 hours and the size was approaching the 137gb limitation in
> both NT and the BIOS. I convinced my work that to fix these 2 problems we
> need to do 2 things.
> 1.) Buy a new server, Dual 2.6GHz 2Gb RAM running windows 2000 server.
> This will improve performance and upgrade a server that's in dire need of
an
> upgrade anyway and, most importantly, jump the 137gb hurdle.
> 2.) Move these 1 million plus images out of a DB structure and onto a
> mirrored file structure. This will greatly reduce the backup time and the
> actual size of the DB.
> Well, I got the server...and I set it all up like I wanted except for
> moving the images out of the DB. The DB backed up like normal last night
> and I noticed that instead of 15 hours it successfully completed in 2.
> Can the new servers speed really have been that effective in
shortening
> the backup time. I was expecting maybe a little faster...like from 15
hours
> to maybe...at best 10, but, 2 hours. Wow!! Is this even possible or
should
> I look into some problem with the backup? If this is correct I wouldn't
> need to move the images into a file structure. From what I have read,
> because these images are small and used on our web pages, a database
> structure like we have currently would be the better choice.
> If anyone can shed some light on why the huge difference in backup
times
> it would be much appreciated. Any suggestions on what I should do with
> these images are welcome too.
> Thanks,
> --
> -Scott
>|||Hey thanks,
Yeah, I looked into windows 2003 but given the size of the company and
the cost of the new server they wanted to save money. We already had a
spare copy of windows 2000 server so I used that. I also wanted to upgrade
to SQL 2000 but like with server 2003 with all the licenses I'd need they
said it would be too expensive.
What do you mean by my DB being dived up into file groups? Are you
suggesting I divide the actual database into multiple databases, i.e. a 2003
database containing images from 2003 and so on?
So is it possible then that just the huge jump in speed between my old
server and the new one solved my problem of the 15 hour backup? I still
find it hard to believe that it would make 86% reduction in time.
-Scott
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:7153294A-229F-4B97-95B1-D6A055450CC2@.microsoft.com...
> Hi
> Windows 2003 would probably bring better IO performance as Microsoft did a
> lot of work on it.
> If your DB is not yet dived up into file groups, do so. Then you can
backup
> and restore a single file group. This will help a lot in a DR scenario.
> I like to store Images in a DB. A file system needs complicated directory
> structure to support than number of images. Keeping the DB and images in
sync
> is another issue too.
> Microsoft has proven with the TerraServer project that SQL server is
> efficient when storing images in a DB.
> Regards
> Mike
> "Scott Elgram" wrote:
> > Hey guys,
> > I was hoping some of you people could shed some light on something
> > that's been puzzling me this morning. I had a SQL 7 machine with a DB
that
> > contained millions of images. It was on a dual 1GHz P3 box with 2 GB of
ram
> > running Windows NT. This machine was really old and so mission critical
no
> > one wanted to touch it. Anyway, the nightly backup of this DB was
> > approaching 15 hours and the size was approaching the 137gb limitation
in
> > both NT and the BIOS. I convinced my work that to fix these 2 problems
we
> > need to do 2 things.
> > 1.) Buy a new server, Dual 2.6GHz 2Gb RAM running windows 2000 server.
> > This will improve performance and upgrade a server that's in dire need
of an
> > upgrade anyway and, most importantly, jump the 137gb hurdle.
> > 2.) Move these 1 million plus images out of a DB structure and onto a
> > mirrored file structure. This will greatly reduce the backup time and
the
> > actual size of the DB.
> > Well, I got the server...and I set it all up like I wanted except
for
> > moving the images out of the DB. The DB backed up like normal last
night
> > and I noticed that instead of 15 hours it successfully completed in 2.
> > Can the new servers speed really have been that effective in
shortening
> > the backup time. I was expecting maybe a little faster...like from 15
hours
> > to maybe...at best 10, but, 2 hours. Wow!! Is this even possible or
should
> > I look into some problem with the backup? If this is correct I wouldn't
> > need to move the images into a file structure. From what I have read,
> > because these images are small and used on our web pages, a database
> > structure like we have currently would be the better choice.
> >
> > If anyone can shed some light on why the huge difference in backup
times
> > it would be much appreciated. Any suggestions on what I should do with
> > these images are welcome too.
> >
> > Thanks,
> > --
> > -Scott
> >
> >
> >

My merge replication is going haywire!

Hi guys, hoping someone can help me! Im desperate now.
I have a SQL Server 2005 database, configured for merge replication on a 4gb database to a SQL Server 2000 server. All was working fine and then yesterday everything stopped because the distribution database has reached 67Gb (Yes gigabytes) filling up the 80Gb C drive.
Now, Ive tried deleteing the subscription but it wont let me as there isnt enough disk space. Is there a way that I can manually truncate data in the distribution. I know it will prob break replication but I want to rebuild the entire replication set up now and just get it working as soon as possible.

Can someone please help! Im nearly out of hair to pull out!

Shane

I'm confused why the distribution database is so large since merge replication barely touches the distribution database with exception to the history tables. Are you sure it's the distribution database and not the published database? And which part of the database is taking up space - is it the data file or the log file?

If it's the distribution database, and assuming it's the data file, can you tell me what tables are taking up all the space, SSMS should have some reports you can run that will show you this. If it's the published database, that would make sense since there are a bunch of metadata tables. You might be able to change the retention to something smaller and then manually run metadata cleanup jobs to remove metadata. However I would change the retention rate little by little, otherwise the larger it is, the more log space at once it might consume and you'll be back to square one, running out of disk space.

If it's the log file, your best bet would be backup the log to some network share, then shrink the file.|||Thanks for your reply Greg.
Its definately the distribution database. The distribution.LDF file is now 59GB and the distribution.mdf file is 12Gb, I dont understand what is going on!
There is only one table in the databas ewith many records, MSMergeHistory, with about 6 million records, seems a lot but then again maybe not. The database being replicated is our main intranet database used by 140 people daily.

Anyways, what can you suggest I do?
|||I should add that the distribution database recovery model is set to simple, so Im a little confused as to how this could happen. ie the log files growing so much...
|||Is it possible for me to truncate that MSMergeHistory table and then shrink perhaps? Or move the distribution to another 1Tb disk NAS I have attached. At the moment I cant to anything with the machine as this one DB is hogging all the space so Im lost in trying to find a way to find space. Every time I delete something the database expands immediately, Ive now got a bare C drive with only Windows and these databases. So frustrating. Any ideas or thoughts are greatly appreciated, Im desperate to get this working. Im now to the point of considering uninstalling and reinstalling SQL Server which is ridiculous...
|||

I agree it's ridiculous, however i don't believe 6 million rows in the history table would take up so many GB. Can you run those reports in Object Explorer which can tell you what tables take up what space to verify it's just the history table?

Let's assume it is the history table, then you can change the history retention so it's more aggressive to clean it up.

As for why your distribution log isn't truncating, I can't answer that, you'll have to do more investigating. There are articles in msdn that detailing what causes this to happen, log could be continuously growing, corruption ,etc. As I mentioned earlier, do a log backup to your NAS to free of the log space, then shrink the file to free of disk space.

|||The problem I have is that I cant run *anything* because I get Out of disk space errors. Ive tried stopping sql server and manually moving the mdf and LDF files to another disk but thats not helped. I will try moving them back now, doing a recovery but change the location to recover to. Maybe that way I can start running queries to reduce stuff.

|||Then attach your network storage and add another data file or log file to the distribution database.|||OK, Ive got the database currently restoring to a much bigger disk.

The results of sp_helpfile and spaceused are:

name fileid filename filegroup size maxsize growth usage
-- - --
distribution 1 E:\SQLRoot\Data\distribution.MDF PRIMARY 12012160 KB Unlimited 1024 KB data only
distribution_log 2 E:\SQLRoot\Data\distribution.LDF NULL 59653312 KB 2147483648 KB 10% log only

database_name database_size unallocated space
--
distribution 69985.81 MB 20.97 MB

reserved data index_size unused
11990688 KB 11299568 KB 687784 KB 3336 KB

|||i could have sworn I said to back up the distribution log to the NAS, then shrink it. DId that not work?|||

Are you using merge agent in a continuous mode?

can you move the distribution database to the SAN: checkout: http://msdn2.microsoft.com/en-us/library/ms345408.aspx for more info.

|||Backing up the log did nothing to the log on size of the log, once it was backed up I tried to shrink it but still no joy, because I was out of disk space it couldnt execute anything.

I have just now resolved the issue, by backing up the entire Distribution to the SAN, then doing a restore but changing the restoe location to the NAS drive (that had 1 terabyte free), once that was done I had free space on the C, room for the Distribution to expand while I ra\n the scripts to drop all replication.

The scripts I ran in order were then
1, Manually remove the Subscription,
2 Manually remove the Publication
3 Shrink the Distribution database
4 Run Exec sp_removedbreplication, then add it again as a replicated database to repair all the sysmergearticle tables etc
5 Ran my scripts then for recreating and initializing the Pub/Sub and its currently bulk copying.

As yet I still dont know why this happened but it seems to have been corrupted so rebuilding looks to have fixed although will know for sure ina day or 2 after the database has been used in anger.

Anyways, thanks for all the help and advice. This is a great resource and will be sure to return the favour one day :-)