easy questions related with A-P cluster

Hi everyone,

I'm just a newbie with clustering.

We've got a 64-bit A-P cluster running with Sql25k 64-bit too.

Each node own two local network adapters. One of them (the one currently is Active) own three IP and the another one only one.

Why? From CLUADMIN I see as one of these three is set as Cluster IP Address in Active Resources. So I see the following configuration for the Active node:

Adapter 1: IP public for Active Directory

Adapter2:IP1 (reserved or ?)

IP2 (used as SQL IP Address 1 in Active Resources)

IP3 (used as Cluster IP Address in Active Resources)

Passive node:

Adapter 1: IP public for Active Directory

Adapter2:IP (only have one ?)

Is this correct/necessary? Does anyone have similar configuration?

Thanks for your time,

Does anyone have any idea?|||

This issue has already solved.

Ip1: physical node

Ip2: virtual cluster

Ip3: virtual Sql

sql

easy questions related with A-P cluster

Hi everyone,

I'm just a newbie with clustering.

We've got a 64-bit A-P cluster running with Sql25k 64-bit too.

Each node own two local network adapters. One of them (the one currently is Active) own three IP and the another one only one.

Why? From CLUADMIN I see as one of these three is set as Cluster IP Address in Active Resources. So I see the following configuration for the Active node:

Adapter 1: IP public for Active Directory

Adapter2:IP1 (reserved or ?)

IP2 (used as SQL IP Address 1 in Active Resources)

IP3 (used as Cluster IP Address in Active Resources)

Passive node:

Adapter 1: IP public for Active Directory

Adapter2:IP (only have one ?)

Is this correct/necessary? Does anyone have similar configuration?

Thanks for your time,

Does anyone have any idea?|||

This issue has already solved.

Ip1: physical node

Ip2: virtual cluster

Ip3: virtual Sql

Easy Questions - get me started

I have 3 retail sites running MSDE and I want to aggregate sales data at a
separate SQL Server. I run a VPN and I guess I need to use merge replication
to a full SQL Server Database. My questions are
1. Is the central server the publisher or subscriber
2. The three sites tables are identical and I would like a siteID added at
the table in the central server to distinguish which the data came from
3. If the central server is the publisher will unwanted data from one site
flow across to another.
4. Is what I am asking clear enough and is merge replication appropriate
For merge, the central SQL Server should be the publisher. If you filter
your articles by site name data will not flow to the other subscribers. For
your version of sql server, yes merge is the most appropriate.
However, does data only flow centrally? If so, transactional replication
would be faster, not require a guid column on each table (assuming you have
pks on each table). This would require at least sql server standard edition
everywhere.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Steve Hall" <SteveHall@.discussions.microsoft.com> wrote in message
news:A3AF25D9-8D4B-4C0E-80B6-E95ED675E82E@.microsoft.com...
>I have 3 retail sites running MSDE and I want to aggregate sales data at a
> separate SQL Server. I run a VPN and I guess I need to use merge
> replication
> to a full SQL Server Database. My questions are
> 1. Is the central server the publisher or subscriber
> 2. The three sites tables are identical and I would like a siteID added
> at
> the table in the central server to distinguish which the data came from
> 3. If the central server is the publisher will unwanted data from one site
> flow across to another.
> 4. Is what I am asking clear enough and is merge replication appropriate
|||Steve pls look at this:
http://www.replicationanswers.com/CentralSubscriberArticle.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||You guys rock!
That is two great replies within 30 minutes of posting.
Steve
"Paul Ibison" wrote:

> Steve pls look at this:
> http://www.replicationanswers.com/CentralSubscriberArticle.asp
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>

Easy question about server log files

SQL Server 2000 running on Windows 2000

Is there a way to set up the SQL Server Log files so that it automatically creates a new one (or overwrites the old one) when the old one is full?

We keep having people shut out of our web site due to the log file being full. Since we have no DBA I volunteered to try to find out how we can avoid this faux pas in the future.

Thanks for your help!New idea for Microsoft !!!

If seriously may be it will be easier to backup transaction log more frequently (if full model) or increase maximal size of log because additional log files will only consume disk space until it is full.

It needs to find out why log is growing so fast - may be it is because of not closed transaction or something else.|||Sorry, but I'm new at this. Are we both talking about the same thing?

I'm not talking about the transaction log files that are backed up but of the log found under:

Management>SQL Server Logs

This log just shows what SQL Server has been doing, not what the users have been doing.

Perhaps you can tell me where the interface to make settings for this log can be found?|||Originally posted by Fly Girl
Sorry, but I'm new at this. Are we both talking about the same thing?

I'm not talking about the transaction log files that are backed up but of the log found under:

Management>SQL Server Logs

This log just shows what SQL Server has been doing, not what the users have been doing.

Perhaps you can tell me where the interface to make settings for this log can be found?

I am taking my words back...

Right click on SQL Server Log files/configure:

BOL:

Configure SQL Server Error Logs
Use this dialog box to view or specify the following options.

Options
Limiting the number of the error log files before they are recycled.

Check to limit the number of error logs created before they are recycled. A new error log is created each time an instance of Microsoft SQL Server is started. Typically, SQL Server retains backups of the previous six logs, unless you check this option, and specify a different maximum number of error log files below.

Maximum number of the error log files.

Specify the maximum number of error log files created before they are recycled. The default is six, which is the number of previous backup logs SQL Server retains before recycling them.|||Ok ... we need to get this clear ...

First of all .. I dont think it would be the SQL server error logs that are causing the problem.

Connect to the server ... using QA and run the following commands

exec xp_fixeddrives

and

sp_helpdb 'production_databasename'

Copy paste results and we will go further.|||Originally posted by snail
Specify the maximum number of error log files created before they are recycled. The default is six, which is the number of previous backup logs SQL Server retains before recycling them.

And you can create a scheduled to be run daily in SQL Server
Command to be executed in Job :-
dbcc errorlog|||OK, I think I've got this one ironed out now.

Thanks for the help!

easy parameter Q

I think this is probably easy but haven't found the setting yet and am
running out of time.
My report has a column I want to use as a parameter(parameter query). The
value of the field repeats several times and I want to limit the drop down
list to the first occurance of each value.
Hope this is an easy one.Have you tried select Distinct(column) ?
"HollyylloH" wrote:
> I think this is probably easy but haven't found the setting yet and am
> running out of time.
> My report has a column I want to use as a parameter(parameter query). The
> value of the field repeats several times and I want to limit the drop down
> list to the first occurance of each value.
> Hope this is an easy one.|||Darwin,
Thanks for your reply. I am using report parameters and am not sure how to
use a distinct() within the confines of the the parameter options. If you can
help I would much appriciate it.
I don't want to affect the report query but rather the parameter drop-down
menu options.
"darwin" wrote:
> Have you tried select Distinct(column) ?
> "HollyylloH" wrote:
> > I think this is probably easy but haven't found the setting yet and am
> > running out of time.
> >
> > My report has a column I want to use as a parameter(parameter query). The
> > value of the field repeats several times and I want to limit the drop down
> > list to the first occurance of each value.
> >
> > Hope this is an easy one.|||create a new dataset to use to populate the parameter. You can create
multiple datasets to populate your parameters.
then change your parameter properties to use the new data set. select the
parameter you want to change, then click the From Query radio button, select
the new dataset name under dataset, select the Value Field value and the
label field. This is generally an Id and description.
hope that helps.. there should be something thats helps in the help files
"HollyylloH" wrote:
> Darwin,
> Thanks for your reply. I am using report parameters and am not sure how to
> use a distinct() within the confines of the the parameter options. If you can
> help I would much appriciate it.
> I don't want to affect the report query but rather the parameter drop-down
> menu options.
> "darwin" wrote:
> > Have you tried select Distinct(column) ?
> >
> > "HollyylloH" wrote:
> >
> > > I think this is probably easy but haven't found the setting yet and am
> > > running out of time.
> > >
> > > My report has a column I want to use as a parameter(parameter query). The
> > > value of the field repeats several times and I want to limit the drop down
> > > list to the first occurance of each value.
> > >
> > > Hope this is an easy one.|||Thanks a million! That did it for me!
"darwin" wrote:
> create a new dataset to use to populate the parameter. You can create
> multiple datasets to populate your parameters.
> then change your parameter properties to use the new data set. select the
> parameter you want to change, then click the From Query radio button, select
> the new dataset name under dataset, select the Value Field value and the
> label field. This is generally an Id and description.
> hope that helps.. there should be something thats helps in the help files
>
> "HollyylloH" wrote:
> > Darwin,
> >
> > Thanks for your reply. I am using report parameters and am not sure how to
> > use a distinct() within the confines of the the parameter options. If you can
> > help I would much appriciate it.
> >
> > I don't want to affect the report query but rather the parameter drop-down
> > menu options.
> >
> > "darwin" wrote:
> >
> > > Have you tried select Distinct(column) ?
> > >
> > > "HollyylloH" wrote:
> > >
> > > > I think this is probably easy but haven't found the setting yet and am
> > > > running out of time.
> > > >
> > > > My report has a column I want to use as a parameter(parameter query). The
> > > > value of the field repeats several times and I want to limit the drop down
> > > > list to the first occurance of each value.
> > > >
> > > > Hope this is an easy one.

Easier way to change collation on columns.

Hi everybody
Running a SQL Server 2000 std edition + SP3.
Restored a couple of databases from a SQL Server 7 to 2000.
I need to change the collation of these databases.
So I used alter database to change the default collation.
Now I need to know if there is a way to change all the
columns in the existing tables to the new default
collation other than having to change them by hand.
Is there a script or some wizard that can help me or am I
stuck with this monster assignment to change every string
datatype to the new default collation?
Thanks in advance
RisunRisun,
You have to script it and do it manually. There are some scripts on the
internet, I believe that will do this for you. One easy way is to create
a new database with the new collation and then copy the data over using
a script or dts.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Risun wrote:
> Hi everybody
> Running a SQL Server 2000 std edition + SP3.
> Restored a couple of databases from a SQL Server 7 to 2000.
> I need to change the collation of these databases.
> So I used alter database to change the default collation.
> Now I need to know if there is a way to change all the
> columns in the existing tables to the new default
> collation other than having to change them by hand.
> Is there a script or some wizard that can help me or am I
> stuck with this monster assignment to change every string
> datatype to the new default collation?
> Thanks in advance
> Risun

DynamicSQL & the Index Tuning wizard.

Some facts first:
1. Large application running on SQL Server 2000.
2. Stored Procedures use a lot of dynamic SQL.
3. Performance is a major issue.
4. Focussing on the database:
4.1 Start up SQL Profiler.
4.2 Run the application for a period of time (normal customer usage) to
generate the workload file.
4.3 Run the ITW on this workload file.
Questions:
(a) What kind of output can I expect from the ITW since the t-sql code is
heavily using dynamic SQL?
(b) Will the ITW be able to properly analyze the workload file in this
scenario?
(c) Are there any other issues that I should be aware of when using the SQL
Profiler and ITW for dynamic SQL code analysis?
TIA
Cheers!
SQLCatZ
SQLCatz,
Yes, the ITW will work fine with dynamic SQL. You might also want to
look at the profiler trace yourself and run some queries against it. I
look for:
1) CPU intensive queries (CPU column)
2) IO intensive queries (reads, writes columns)
3) Long running queries (duration column)
If you get the top 10 culprits from each of those categories, you will
have eliminated 90% of your poor performing queries. You want to get the
biggest bangs for your buck, and not waste time on things that don't
really matter.
Use the ITW as a guide to making decisions about which indexes to apply,
in most cases you won't want to blindly implement what it suggests.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
SQLCatz wrote:
> Some facts first:
> 1. Large application running on SQL Server 2000.
> 2. Stored Procedures use a lot of dynamic SQL.
> 3. Performance is a major issue.
> 4. Focussing on the database:
> 4.1 Start up SQL Profiler.
> 4.2 Run the application for a period of time (normal customer usage) to
> generate the workload file.
> 4.3 Run the ITW on this workload file.
> Questions:
> (a) What kind of output can I expect from the ITW since the t-sql code is
> heavily using dynamic SQL?
> (b) Will the ITW be able to properly analyze the workload file in this
> scenario?
> (c) Are there any other issues that I should be aware of when using the SQL
> Profiler and ITW for dynamic SQL code analysis?
> TIA
> Cheers!
> SQLCatZ
>
sql

Dynamic y axis labeling

Hi

I'm trying to label the y axis from a dataset but keep running in to problems as it only takes ints and the data from the database is text, does anyone know the best way to do this?

Thanks

L

I would suggest using a bar graph. This will enable you to put text on the y-axis and the values will be on the x-axis.

Put the dataset field you want on the y-axis in the category groups and you will be good to go.

Dynamic SQL vs OSQL

Question for the experts here:

Is there any advantage of running an SQL statement through osql with the database information over using dynamic sql?

Example:

DECLARE @.DB Varchar(50)
DECLARE @.SQL Varchar(4000)
SET @.DB = '<nvr_changing_server>.<my_dynamic_db_name>'
SET @.SQL = 'SELECT admissiontype_id AS atype, admissiontype AS atype_desc, start_date, end_date
INTO tlkAdmitType
FROM <nvr_changing_server>.<nvr_changing_DB>.dbo.tlkAdmissionTypes'

exec master..xp_cmdShell 'osql -U sa -P sapwd -S nvr_changing_server -d my_dynamic_db_name -Q @.SQL...'

vs. something like:

DECLARE @.DB Varchar(50)
DECLARE @.SQL Varchar(4000)
SET @.DB = '<nvr_changing_server>.<my_dynamic_db_name>'
SET @.SQL = 'SELECT admissiontype_id AS atype, admissiontype AS atype_desc, start_date, end_date
INTO ' + @.DB +'.dbo.tlkAdmitType
FROM <nvr_changing_server>.<nvr_changing_DB>.dbo.tlkAdmissionTypes'

EXEC(@.SQL)

The purpose of all this is...I need to pass a parameter for the DB that I will be inserting into...here we create a new db with a specific name based on quarterly data. We collect, crunch, validate data and ship it. Then when it's old we archive it then eventually delete it.

I have written a script that makes this quarterly build less painful. In fact I won't have to do it! :)...our Sr. Data Analysts will do it now. In order for this beautiful thing (*in my mind anyway*) to work they need to set parameters for which data to pull and where to put it. The DB is scripted into existance and the data is moved into it. So therefore they need to enter the Qtr,Yr and dbname. I have done DSQL before on smaller scripts and I am just curious if the expert pool here can shed some light on this approach. The script will most likely be run in a DTS SQL Task.

Thanks in advance...RI would go for the second option. It will not require you to open up xp_cmdshell to all users (bad security hole). Also, I think the error handling is better with the second option. I am not sure how to get an error back from option 1.

Dynamic SQL Cache

I am running SQL Server 2005 SP2 Standard Edition on Windows 2003 Server
Standard Edition (4 gb RAM, No AWE Enabled, default Max/Min Memory
settings). From PerfMon traces the overall server memory appears to be just
fine all the time. The SQL Server appears to run out of available SQL
memory resources during certain ETL jobs and log the following errors:
--Error: 701, Severity: 17, State: 123
--There is insufficient system memory to run this query.
I see significant spikes in the SQLServer:Memory Manager:SQL Cache Memory
counter during times when I experience "Out Of Memory" errors on my SQL
Server. The value is normally 0 until milliseconds before I get the 701
Errors. According to BOL this Counter represents the "Total amount of
dynamic memory the server is using for the dynamic SQL cache."
What exactly is the dynamic SQL cache? What should I look for in tracking
down this memory resource hog?Hi Ulysses,
I understand that you encountered the insufficient system memory error
during your ETL jobs execution.
If I have misunderstood, please let me know.
Dynamic SQL cache means that the dynamic memory that your SQL Server
instance caches. By design SQL Server will try to occupy memory as much as
possible to ensure its performance. SQL Server frees memory when it has
more than the min server memory setting, and Windows indicates that there
is a shortage of free memory. However in memory intensive usage situations,
this happens uncertainly.
You may refer to:
Dynamic Memory Management
http://msdn2.microsoft.com/en-us/library/ms178145.aspx
ETL jobs for extracting large volume of data may consume large memory for
SQL Server. Since AWE was not enabled, your SQL Server can only use 2GB
memory at most. I recommend that you use /3GB switch or enable AWE option
in your SQL Server 2005.
You may refer to the following articles:
Enabling AWE Memory for SQL Server
http://msdn2.microsoft.com/en-us/library/ms190673.aspx
How to configure SQL Server to use more than 2 GB of physical memory
http://support.microsoft.com/kb/274750/en-us
Please feel free to let me know if you have any other questions or
concerns. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
===========================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================|||Hi
>SQL Server frees memory when it has
> more than the min server memory setting,
BOL says
The database engine will not free any of the acquired memory until it
reaches the amount specified in min server memory
""Charles Wang [MSFT]"" <changliw@.online.microsoft.com> wrote in message
news:azN3LJrpIHA.1788@.TK2MSFTNGHUB02.phx.gbl...
> Hi Ulysses,
> I understand that you encountered the insufficient system memory error
> during your ETL jobs execution.
> If I have misunderstood, please let me know.
> Dynamic SQL cache means that the dynamic memory that your SQL Server
> instance caches. By design SQL Server will try to occupy memory as much as
> possible to ensure its performance. SQL Server frees memory when it has
> more than the min server memory setting, and Windows indicates that there
> is a shortage of free memory. However in memory intensive usage
> situations,
> this happens uncertainly.
> You may refer to:
> Dynamic Memory Management
> http://msdn2.microsoft.com/en-us/library/ms178145.aspx
> ETL jobs for extracting large volume of data may consume large memory for
> SQL Server. Since AWE was not enabled, your SQL Server can only use 2GB
> memory at most. I recommend that you use /3GB switch or enable AWE option
> in your SQL Server 2005.
> You may refer to the following articles:
> Enabling AWE Memory for SQL Server
> http://msdn2.microsoft.com/en-us/library/ms190673.aspx
> How to configure SQL Server to use more than 2 GB of physical memory
> http://support.microsoft.com/kb/274750/en-us
> Please feel free to let me know if you have any other questions or
> concerns. Have a nice day!
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ===========================================================> Delighting our customers is our #1 priority. We welcome your
> comments and suggestions about how we can improve the
> support we provide to you. Please feel free to let my manager
> know what you think of the level of service provided. You can
> send feedback directly to my manager at: msdnmg@.microsoft.com.
> ===========================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for
> non-urgent issues where an initial response from the community
> or a Microsoft Support Engineer within 1 business day is acceptable.
> Please note that each follow up response may take approximately
> 2 business days as the support professional working with you may
> need further investigation to reach the most efficient resolution.
> The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by
> contacting Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ============================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> =========================================================>
>|||Sorry for delaying this response since I took a sick leave these two days.
Hi Uri,
Could you please point out which paragraph that BOL describes this
information?
From the article "Dynamic Memory Management" in BOL, you can find the
following description:
" A SQL Server instance continues to acquire physical memory until it
either reaches its max server memory allocation target or Windows indicates
there is no longer an excess of free memory; it frees memory when it has
more than the min server memory setting, and Windows indicates that there
is a shortage of free memory."
Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
=========================================================This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================|||Thanks. I did enable the /3GB switch and enabled AWE in SQL Server 2005 and
then set the min / max memory using sp_configure and this allowed for the
ETL process to run without interupting the other processes (i.e., no more
Out-of-Memory errors). I do not know if it was simply enabling the "AWE
/3GB" or manually setting the "min/max" memory that fixed the problem.
Perhaps it was the combination of both changes. I have moved on to more
pressing issues now (i.e., Linked Server Connection Error: 18456)|||Hi Ulysses,
Thanks for your response for letting me know this issue has been fixed.
Just clarify one thing that actually you can just enable one of the two
options:/3GB and AWE. In other words, if you specify /3GB switch, you do
not need to enable AWE; otherwise, you can enable AWE together with /PAE
switch. You may refer to:
Large memory support is available in Windows Server 2003 and in Windows 2000
http://support.microsoft.com/kb/283037/en-us
Regarding the linked server connection error, I will continue working with
you for further research under your other post "Linked Server Connection
Error: 18459".
Please feel free to let me know if you have any other questions or concerns.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
=========================================================This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================|||Hi Charles
I hope now you are doing well.
I was mention BOL (SS2000) Min server memory option then Effects of min and
max server memory
""Charles Wang [MSFT]"" <changliw@.online.microsoft.com> wrote in message
news:3FUxTgpqIHA.1784@.TK2MSFTNGHUB02.phx.gbl...
> Sorry for delaying this response since I took a sick leave these two days.
> Hi Uri,
> Could you please point out which paragraph that BOL describes this
> information?
> From the article "Dynamic Memory Management" in BOL, you can find the
> following description:
> " A SQL Server instance continues to acquire physical memory until it
> either reaches its max server memory allocation target or Windows
> indicates
> there is no longer an excess of free memory; it frees memory when it has
> more than the min server memory setting, and Windows indicates that there
> is a shortage of free memory."
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =========================================================> Delighting our customers is our #1 priority. We welcome your
> comments and suggestions about how we can improve the
> support we provide to you. Please feel free to let my manager
> know what you think of the level of service provided. You can
> send feedback directly to my manager at: msdnmg@.microsoft.com.
> =========================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> =========================================================>