"Sql Broswer" vs. "Hide Sql Instance"

I was never clear what the sql 2005 Browser service is about. But now that I've found the "Hide Instance" property of a server (SQL Server Configuration manager, select Protocols for MSSQLSERVER, right-click, go to Properties) I'm more confused than ever. What is the difference between these two items?

TIA,

Barkingdog

Hiding instance will prevent the broadcast request for instance to list that server instance. YOu will have to directly and explicitly connect to it (and therefore know the name).

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

That makes sense but if I stop the Sql Browser won't that have the same impact?

TIA,

Barkingdog

|||Yes, shutting down your computer will also hide the instance in some way :-)

SQL Browser is not only for enumeration of instances, it will redirect requests to SQL Server instances, although the user did not specify the port to connect to, e.g. Connecting to instance SomeServer\SomeInstance on port 1677 (random port chosen by me) needs to be written in the connection string like this (without SQL Server Browser) SomeServer\SomeInstance,1677. SQL Server browser activated will also redirect the request for SomeServer\SomeInstance to the specific port without naming the port epplicitly.

So, hiding the SQL Server instance means something different. It won′t answer broadcasting request for instance names and only react on connection requests.

HTH, Jens Suessmeyer:

http://www.sqlserver2005.de

"Sql Broswer" vs. "Hide Sql Instance"

I was never clear what the sql 2005 Browser service is about. But now that I've found the "Hide Instance" property of a server (SQL Server Configuration manager, select Protocols for MSSQLSERVER, right-click, go to Properties) I'm more confused than ever. What is the difference between these two items?

TIA,

Barkingdog

Hiding instance will prevent the broadcast request for instance to list that server instance. YOu will have to directly and explicitly connect to it (and therefore know the name).

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

That makes sense but if I stop the Sql Browser won't that have the same impact?

TIA,

Barkingdog

|||Yes, shutting down your computer will also hide the instance in some way :-)

SQL Browser is not only for enumeration of instances, it will redirect requests to SQL Server instances, although the user did not specify the port to connect to, e.g. Connecting to instance SomeServer\SomeInstance on port 1677 (random port chosen by me) needs to be written in the connection string like this (without SQL Server Browser) SomeServer\SomeInstance,1677. SQL Server browser activated will also redirect the request for SomeServer\SomeInstance to the specific port without naming the port epplicitly.

So, hiding the SQL Server instance means something different. It won′t answer broadcasting request for instance names and only react on connection requests.

HTH, Jens Suessmeyer:

http://www.sqlserver2005.de

"Append to Media" Vs "Overwrite an existing media" in SQL Server Database ba

Dear All,

I know the basic defiinition of these two options, but i am not very clear why would someone choose one over another,
currently I am using Append to Media option, and every day backup, I see my backup files growing in size.

can someone give me nice example about these two options,

Thanks,

When you append to the media the previous backups also remain in the backup file. One backup file can contain numerous backups. When you overwrite the backup file is recreated every time so it contains just one file.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Thanks for your reply.

That means, overwrite backup media will occupy less space, then why would i choose Append to media,

any risk involved in overwriting option ?, becuase every body recommends appending, why not ovewritting.

Thanks,|||

It will definitely use less space.

I do not see the risk of overwriting your backup unless you need a full backup that was taken a couple of days earlier to be accessible. In most cases I suppose you will only be interested in the latest version of your database and not the one you had 3 months ago. For the 'special' occasions where a specific version may be required I would simply take a separate backup.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Thanks, your advice is helpful,

but if i want to be able to recover point in time, then I have to go with append to media, along with Transaction long back , RIGHT?

|||

The risk is that if you overwrite the previous backup, and then the current one fails for any reason you are left with no backup for your database. Not a good situation.

What I'd recommend instead, which is a fairly common practice, is to do each backup to a new file which incorporates the current date/time as part of the filename.

That way you always have unique filenames, you can easily prune out old backups, and it's obvious which backup is from what time.

|||

Thanks,

but even if i append to media, and it fails, i will left with no backups , Right?

also for your best suggestion, can you send me sample query or how to do in Enterprise manager.

|||Fahim,
If you are appending to a media daily and if the backup fails today (assumption) then you are atleast left with the backups of previous days since you are appending the previous backups will be present ! but if chose the overwrite option it will overwrite the existing backup file and then will backup the database so if your backup fails due to some reason you are left with no backups for your database.
You can make use of the maintenance plan in sql 2000 to configure backup job and you can specify the number of days to retain the backup file........the backup file will be created with dd/mm/hr format so that it can be deleted by chosing the option "Remove files older than"
http://www.databasejournal.com/features/mssql/article.php/3530486

Friends pls correct me if am wrong

Regards
Deepak