Load balancing for read-only databases

We planning to setup a database and use the Windows 2003 NLB features to
balance query load between two database-nodes. The database is accessed for
read-only queries from a webapplication.
To update the database contents, a seperate application will insert new
content into one of the two databases. This database acts as the publisher
for the other database, the subscriber. That way, both databases will be able
to handle the queries. We will not use any shared disk technology.
Questions are:
1. are there any caveats in this setup?
2. Will NLB effectively be able to distribute the (select-)query-load
amongst the two servers? Provided we disable connectionpooling, as it reads
somewhere in the docs for Commerce Server 2000.
3. if one the servers is switched off, will the web application still be
able to perform queries on the remaining server? Will there be any delay?
4. are both nodes still available seperately to host other databases that
are not load-balanced?
5. does this setup allow for scale out, by adding an additional
database-node to act as another subscriber?
6. do you know of any documentation that describes this type of setup?
See inline
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Sander" <Sander@.discussions.microsoft.com> wrote in message
news:EB91D60B-B298-4176-B99E-7403FFD772B5@.microsoft.com...
> We planning to setup a database and use the Windows 2003 NLB features to
> balance query load between two database-nodes. The database is accessed
for
> read-only queries from a webapplication.
> To update the database contents, a seperate application will insert new
> content into one of the two databases. This database acts as the publisher
> for the other database, the subscriber. That way, both databases will be
able
> to handle the queries. We will not use any shared disk technology.
> Questions are:
> 1. are there any caveats in this setup?
Ensure your business needs regarding latency and currency are met .. ( One
will be slightly behind the other.)

> 2. Will NLB effectively be able to distribute the (select-)query-load
> amongst the two servers? Provided we disable connectionpooling, as it
reads
> somewhere in the docs for Commerce Server 2000.
I do not think there should be a problem.

> 3. if one the servers is switched off, will the web application still be
> able to perform queries on the remaining server? Will there be any delay?
If one server is switched off, all of the connections will be broken, the
app will have to reconnect and get routed to the other server.. You can make
this invisible to the end users if you wish ( but you do this in the
application..) For instance, if you are using query analyzer, and the you
are looking at the results of a query... the DB server goes down and comes
back up... Then you send another query, Query Analyzer will automatically
re-connect and send your query, instead of simply reporting that you no
longer have a connection... That is a nice way to go...

> 4. are both nodes still available seperately to host other databases that
> are not load-balanced?
Although this is not my strength, I believe the entire Server is load
balanced , or at least at the port level, which would include all of SQL
Server... If you wish some databases to exist on one server which are not
LBs, you might have to do something special ,(although I do not know
at - another port?... another instance on a different port?)

> 5. does this setup allow for scale out, by adding an additional
> database-node to act as another subscriber?
What many people do is to use log shipping for this... It is better than
replication for this use in my opinion because replication does NOT
replicate system tables, (permissions, etc), and log shipping does... Log
shipping can ship to several destinations, allowing a nice scale out
solution which should be fairly easy to implement...
> 6. do you know of any documentation that describes this type of setup?
Log Shipping is doc's in books on line... You will have to learn about LB
from other docs(not in SQL)

Load balancing for read-only databases

We planning to setup a database and use the Windows 2003 NLB features to
balance query load between two database-nodes. The database is accessed for
read-only queries from a webapplication.
To update the database contents, a seperate application will insert new
content into one of the two databases. This database acts as the publisher
for the other database, the subscriber. That way, both databases will be able
to handle the queries. We will not use any shared disk technology.
Questions are:
1. are there any caveats in this setup?
2. Will NLB effectively be able to distribute the (select-)query-load
amongst the two servers? Provided we disable connectionpooling, as it reads
somewhere in the docs for Commerce Server 2000.
3. if one the servers is switched off, will the web application still be
able to perform queries on the remaining server? Will there be any delay?
4. are both nodes still available seperately to host other databases that
are not load-balanced?
5. does this setup allow for scale out, by adding an additional
database-node to act as another subscriber?
6. do you know of any documentation that describes this type of setup?See inline
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Sander" <Sander@.discussions.microsoft.com> wrote in message
news:EB91D60B-B298-4176-B99E-7403FFD772B5@.microsoft.com...
> We planning to setup a database and use the Windows 2003 NLB features to
> balance query load between two database-nodes. The database is accessed
for
> read-only queries from a webapplication.
> To update the database contents, a seperate application will insert new
> content into one of the two databases. This database acts as the publisher
> for the other database, the subscriber. That way, both databases will be
able
> to handle the queries. We will not use any shared disk technology.
> Questions are:
> 1. are there any caveats in this setup?
Ensure your business needs regarding latency and currency are met .. ( One
will be slightly behind the other.)
> 2. Will NLB effectively be able to distribute the (select-)query-load
> amongst the two servers? Provided we disable connectionpooling, as it
reads
> somewhere in the docs for Commerce Server 2000.
I do not think there should be a problem.
> 3. if one the servers is switched off, will the web application still be
> able to perform queries on the remaining server? Will there be any delay?
If one server is switched off, all of the connections will be broken, the
app will have to reconnect and get routed to the other server.. You can make
this invisible to the end users if you wish ( but you do this in the
application..) For instance, if you are using query analyzer, and the you
are looking at the results of a query... the DB server goes down and comes
back up... Then you send another query, Query Analyzer will automatically
re-connect and send your query, instead of simply reporting that you no
longer have a connection... That is a nice way to go...
> 4. are both nodes still available seperately to host other databases that
> are not load-balanced?
Although this is not my strength, I believe the entire Server is load
balanced , or at least at the port level, which would include all of SQL
Server... If you wish some databases to exist on one server which are not
LBs, you might have to do something special ,(although I do not know
at - another port?... another instance on a different port?)
> 5. does this setup allow for scale out, by adding an additional
> database-node to act as another subscriber?
What many people do is to use log shipping for this... It is better than
replication for this use in my opinion because replication does NOT
replicate system tables, (permissions, etc), and log shipping does... Log
shipping can ship to several destinations, allowing a nice scale out
solution which should be fairly easy to implement...
> 6. do you know of any documentation that describes this type of setup?
Log Shipping is doc's in books on line... You will have to learn about LB
from other docs(not in SQL)

Load balancing for read-only databases

We planning to setup a database and use the Windows 2003 NLB features to
balance query load between two database-nodes. The database is accessed for
read-only queries from a webapplication.
To update the database contents, a seperate application will insert new
content into one of the two databases. This database acts as the publisher
for the other database, the subscriber. That way, both databases will be abl
e
to handle the queries. We will not use any shared disk technology.
Questions are:
1. are there any caveats in this setup?
2. Will NLB effectively be able to distribute the (select-)query-load
amongst the two servers? Provided we disable connectionpooling, as it reads
somewhere in the docs for Commerce Server 2000.
3. if one the servers is switched off, will the web application still be
able to perform queries on the remaining server? Will there be any delay?
4. are both nodes still available seperately to host other databases that
are not load-balanced?
5. does this setup allow for scale out, by adding an additional
database-node to act as another subscriber?
6. do you know of any documentation that describes this type of setup?See inline
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Sander" <Sander@.discussions.microsoft.com> wrote in message
news:EB91D60B-B298-4176-B99E-7403FFD772B5@.microsoft.com...
> We planning to setup a database and use the Windows 2003 NLB features to
> balance query load between two database-nodes. The database is accessed
for
> read-only queries from a webapplication.
> To update the database contents, a seperate application will insert new
> content into one of the two databases. This database acts as the publisher
> for the other database, the subscriber. That way, both databases will be
able
> to handle the queries. We will not use any shared disk technology.
> Questions are:
> 1. are there any caveats in this setup?
Ensure your business needs regarding latency and currency are met .. ( One
will be slightly behind the other.)

> 2. Will NLB effectively be able to distribute the (select-)query-load
> amongst the two servers? Provided we disable connectionpooling, as it
reads
> somewhere in the docs for Commerce Server 2000.
I do not think there should be a problem.

> 3. if one the servers is switched off, will the web application still be
> able to perform queries on the remaining server? Will there be any delay?
If one server is switched off, all of the connections will be broken, the
app will have to reconnect and get routed to the other server.. You can make
this invisible to the end users if you wish ( but you do this in the
application..) For instance, if you are using query analyzer, and the you
are looking at the results of a query... the DB server goes down and comes
back up... Then you send another query, Query Analyzer will automatically
re-connect and send your query, instead of simply reporting that you no
longer have a connection... That is a nice way to go...

> 4. are both nodes still available seperately to host other databases that
> are not load-balanced?
Although this is not my strength, I believe the entire Server is load
balanced , or at least at the port level, which would include all of SQL
Server... If you wish some databases to exist on one server which are not
LBs, you might have to do something special ,(although I do not know
at - another port?... another instance on a different port?)

> 5. does this setup allow for scale out, by adding an additional
> database-node to act as another subscriber?
What many people do is to use log shipping for this... It is better than
replication for this use in my opinion because replication does NOT
replicate system tables, (permissions, etc), and log shipping does... Log
shipping can ship to several destinations, allowing a nice scale out
solution which should be fairly easy to implement...
> 6. do you know of any documentation that describes this type of setup?
Log Shipping is doc's in books on line... You will have to learn about LB
from other docs(not in SQL)sql

load balanced scale out deployment issues

Hello,
I have a scale out deployment of Reporting Services consisting of 2 Report
Servers in a load balanced cluster using an F5 BigIP setup in RoundRobin
mode, and the report catalog db sits on on a separate sql server. We use a
vIP with a DNS name that hits the F5. The F5, in turn, is setup to "choose"
one of the 2 Report Servers using the machine name address. Note that this
is for an intranet.
We are not currently using HTTPS (SSL) in our environment, but eventually
we'll turn it on, so ignore that piece of the puzzle for this scenario.
When configuring the scale-out deployment, we followed the instructions on
the Books Online article "Configuring a Report Server Scale-out Deployment."
The last piece of this document instructs us to modify the
RSWebApplication.config file for all the servers in the scale out deployment,
setting the <ReportServerUrl> element to use the virtual server name (vIP
DNS, we assumed), instead of the machine name. However, this element did not
contain a value originally.
The next step was to modify the RSReportServer.config file, to set the
<UrlRoot> element with the virtual server name (again, vIP dns). UrlRoot's
original value as the url <servername>/reportserver.
Once this change was made, I reset IIS on both servers, and hit each
localhost's Report manager to fire up the applications. However, I received
the following error "ReportServerUrl Configuration element is not a valid
configuration..." on both Report Manager web apps.
Note that before we made the above change to ReportServerUrl and UrlRoot,
both servers worked fine. So, I went back in and removed the value from
ReportServerUrl, but left the UrlRoot with the vIP name. Reset IIS, and the
error went away!
I am a little confused as to what these two settings are used for, since the
original value of ReportServerUrl was blank (UrlRoot actually had the machine
name address).
Could someone explain the cause of this error, and suggest a best practice
for this scenario. I would like to use the most efficient configuration
between the Report Servers and the F5 load balancer, and what other changes
would we be looking at once we turn on SSL?
Thanks!
--
Regards,
Thiago Silvabtw, this is using SSRS 2005 (Enterprise Edition, of course, since it's a web
farm).
--
Regards,
"HC" wrote:
> Hello,
> I have a scale out deployment of Reporting Services consisting of 2 Report
> Servers in a load balanced cluster using an F5 BigIP setup in RoundRobin
> mode, and the report catalog db sits on on a separate sql server. We use a
> vIP with a DNS name that hits the F5. The F5, in turn, is setup to "choose"
> one of the 2 Report Servers using the machine name address. Note that this
> is for an intranet.
> We are not currently using HTTPS (SSL) in our environment, but eventually
> we'll turn it on, so ignore that piece of the puzzle for this scenario.
> When configuring the scale-out deployment, we followed the instructions on
> the Books Online article "Configuring a Report Server Scale-out Deployment."
> The last piece of this document instructs us to modify the
> RSWebApplication.config file for all the servers in the scale out deployment,
> setting the <ReportServerUrl> element to use the virtual server name (vIP
> DNS, we assumed), instead of the machine name. However, this element did not
> contain a value originally.
> The next step was to modify the RSReportServer.config file, to set the
> <UrlRoot> element with the virtual server name (again, vIP dns). UrlRoot's
> original value as the url <servername>/reportserver.
> Once this change was made, I reset IIS on both servers, and hit each
> localhost's Report manager to fire up the applications. However, I received
> the following error "ReportServerUrl Configuration element is not a valid
> configuration..." on both Report Manager web apps.
> Note that before we made the above change to ReportServerUrl and UrlRoot,
> both servers worked fine. So, I went back in and removed the value from
> ReportServerUrl, but left the UrlRoot with the vIP name. Reset IIS, and the
> error went away!
> I am a little confused as to what these two settings are used for, since the
> original value of ReportServerUrl was blank (UrlRoot actually had the machine
> name address).
> Could someone explain the cause of this error, and suggest a best practice
> for this scenario. I would like to use the most efficient configuration
> between the Report Servers and the F5 load balancer, and what other changes
> would we be looking at once we turn on SSL?
> Thanks!
> --
> Regards,
> Thiago Silva

List the folder contents using SQL

Hi all,
I have built a Disaster Recovery Site for my DB.on periodic basis, trn files from production server reach DR server and DR setup will apply them locally.

If the flow is smooth then no issues, if one of the file does not reach DR
the entire setup will halt for the want of the file and I dn't have any means to
know which file is missing.

Both my production and DR site located remotely behind firewalls, i.e i can't
physically access the servers or remotely login to the server.

Can anyone tell me how to see the contents in a folder in the server using
SQL query .

Any help is highly appreciated

Thanks and Regards
Srinivas VaranasiI'd use xp_cmdshell (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp).

-PatP|||use

master..xp_cmdshell 'dir c:\'|||Thanks Pat.|||Harshal, sorry to find "STUPID" as Ur Signature

List of options for the .ini file

I am looking for a resource online or a document that
lists all of the possible options that can be placed in
the setup.ini file. Does anyone possibly have this
information or know where I can find it?
Thank you very much!
3.4.3 MSDE 2000 Setup Parameters
MSDE 2000 is designed to be distributed with applications and installed
by the setup program of the application. The Desktop Engine Setup.exe
utility is usually called by an application setup utility, but can also
be run from a command prompt window. The MSDE 2000 setup utility does
not have a graphical user interface. Instead, this utility accepts a set
of switches and parameters that specify the actions the utility should
take.
You can only use MSDE 2000 Release A to install new instances of MSDE
2000. Do not use it to upgrade instances running earlier versions of
MSDE 2000. When running the MSDE 2000 Release A version of Desktop
Engine Setup.exe, do not use these switches or parameters: UPGRADE,
UPGRADEUSER, UPGRADEPWD, or /upgradesp. Use SQL Server 2000 SP3a to
upgrade existing instances of MSDE 2000 to MSDE 2000 SP3a. For more
information about upgrades, see 1.0 Introduction.
This readme only discusses the more commonly used Setup parameters and
switches. All of the switches and parameters supported by Desktop Engine
Setup.exe are documented in "Customizing Desktop Engine Setup.exe" in
SQL Server 2000 Books Online. The version of this topic that describes
the behavior of Desktop Engine Setup.exe included in MSDE 2000 Release A
is at this Microsoft Web page. For more information about setup
documentation, see 1.1 MSDE 2000 Documentation.
You must enclose the values for MSDE Setup parameters in double
quotation marks if the values specified have special characters, such as
blanks. Otherwise quotation marks are optional.
Most installations of MSDE 2000 Release A are made using only these
Setup parameters:
Parameter Description
SAPWD="AStrongPassword" Specifies a strong password to be assigned to
the sa administrator login.
INSTANCENAME="InstanceName" Specifies the name of the instance. If
INSTANCENAME is not specified, Setup installs a default instance.
Other parameters often used to tailor an installation are:
Parameter Description
DISABLENETWORKPROTOCOLS=n Specifies whether the instance will accept
network connections from applications running on other computers. By
default, or if you specify DISABLENTWORKPROTOCOL=1, Setup configures the
instance to not accept network connections. Specify
DISABLENETWORKPROTOCOLS=0 to enable network connections.
SECURITYMODE=SQL Specifies that the instance be installed in Mixed Mode,
where the instance supports both Windows Authentication and SQL
Authentication logins.
DATADIR="data_folder_path" Specifies the folder where Setup installs the
system databases, error logs, and installation scripts. The value
specified for data_folder_path must end with a backslash (\). For a
default instance, Setup appends MSSQL\ to the value specified. For a
named instance, Setup appends MSSQL$InstanceName\, where InstanceName is
the value specified with the INSTANCENAME parameter. Setup builds three
folders at the specified location: a Data folder, a Log folder, and a
Script folder.
TARGETDIR="executable_folder_path" Specifies the folder where Setup
installs the MSDE 2000 executable files. The value specified for
executable_folder_path must end with a backslash (\). For a default
instance, Setup appends MSSQL\Binn to the value specified. For a named
instance, Setup appends MSSQL$InstanceName\Binn, where InstanceName is
the value specified with the INSTANCENAME parameter.
When you use DISABLENETWORKPROTOCOLS=0 to enable network support for an
instance of MSDE 2000, applications connecting to the instance over a
network use Microsoft Data Access Components (MDAC). All versions of
Windows supported for use with MSDE 2000 include a version of the MDAC
software that works with MSDE 2000 Release A. For more information about
network communications, see this Microsoft Web page.
Using an .ini File
Desktop Engine Setup.exe parameters can be specified in two locations:
On the command prompt when running Setup.exe.
In an .ini file whose location is specified by a /settings switch. An
..ini file is a text file, such as a file created using Notepad, and
saved with a file name that has an extension of .ini. In the .ini file,
the first line is [Options], and then the parameters are specified, one
parameter per line.
Important If you use an .ini file during setup, avoid storing security
credentials in it.
This example specifies the parameters on the command prompt:
setup SAPWD="AStrongPassword" INSTANCENAME="InstanceName"
TARGETDIR="C:\MyInstanceFolder"
To run Setup with the same parameters using an .ini file, use Notepad to
create a file named MyParameters.ini with these contents:
[Options]
INSTANCENAME="InstanceName"
TARGETDIR="C:\MyInstanceFolder"
Then run Setup using the /settings switch to point to the .ini file:
setup /settings "MyParameters.ini" SAPWD="AStrongPassword"
Requesting a Setup Log
You will need a verbose log to verify that the installation succeeded or
to assist in debugging any problems that occur.
To generate a verbose log, specify /L*v <LogFileName>. <LogFileName> is
the name of a log file where Setup will record all of its actions. If
you do not specify a path as part of the name, the log file is created
in the current folder. If you are executing Setup from a compact disc,
you must specify the full path to a folder on the hard disk of your
computer.
This example creates a log file, MSDELog.log, in the root folder of the
C: drive:
setup SAPWD="AStrongSAPassword" /L*v C:/MSDELog.log
If installation succeeds, an entry similar to the following will be at
the end of the log:
=== Logging stopped: 5/16/03 0:06:10 ===
MSI (s) (BC:7C): Product: Microsoft SQL Server Desktop Engine --
Installation operation completed successfully.
If the installation does not succeed, an entry similar to the following
will be at the end of the log:
=== Logging stopped: 5/15/03 23:50:34 ===
MSI (c) (6A:CE): Product: Microsoft SQL Server Desktop Engine --
Installation operation failed.
If the installation failed, search for the string "value 3" in the error
log. Within 10 lines of the string will be a failure notice for a custom
action. The notice will have additional information about the nature of
the failure.
[Top]
--Original Message--
From: Scott [mailto:sts@.weiland-wfg.com]
Posted At: Wednesday, 19 January 2005 8:19 AM
Posted To: microsoft.public.sqlserver.msde
Conversation: List of options for the .ini file
Subject: List of options for the .ini file
I am looking for a resource online or a document that lists all of the
possible options that can be placed in the setup.ini file. Does anyone
possibly have this information or know where I can find it?
Thank you very much!
|||Have a look at this topic in the copy of the SQL Server 2000 Books Online
published in the MSDN Library.
http://msdn.microsoft.com/library/?u...asp?frame=true
You can also download a local copy of the SQL Server 2000 Books Online from:
http://www.microsoft.com/sql/techinf...2000/books.asp
Alan Brewer [MSFT]
Content Architect
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights