Best Practices for Moving SQL Server to Another domain?

I've been looking all over the KB trying to find documentation on Best
Practices for moving a SQL Server to another domain. What reACLing needs to
be done to the databases (if any) and what is the best way to do it?
Thanks for any links or help...
Here are a couple of Links...
http://support.microsoft.com/default...b;en-us;224071
http://support.microsoft.com/default...b;en-us;304692
Personally I would go for the first one, as it will transfer everything over.
Once completion do a sp_createstats & sp_updatestats
After than I would advise you run a performance trace for a about a week and
implement any changes, then run the sp_createstats & sp_updatestats again
Peter
"Denial ain't just a river in Egypt."
Mark Twain
"Losferwords" wrote:

> I've been looking all over the KB trying to find documentation on Best
> Practices for moving a SQL Server to another domain. What reACLing needs to
> be done to the databases (if any) and what is the best way to do it?
> Thanks for any links or help...
|||Let me be a little more specific... I'm moving a SQL Server from DOMAIN A to
DOMAIN B. Any Best Practices Guides on that?
"Losferwords" wrote:

> I've been looking all over the KB trying to find documentation on Best
> Practices for moving a SQL Server to another domain. What reACLing needs to
> be done to the databases (if any) and what is the best way to do it?
> Thanks for any links or help...
sql

Best Practices Database Owner, Database Connection Method (asp)

Hi-

I have a sql server database, and am wring web apps to access it.

I've created databases different ways, and ended up with different owners (eg dbo, nt authority\network services...)

I also have connection strings using windows authentication, and some using a user name and password.

I have read that using windows authentication is the best way to go, as far as security goes, but I have noticed some connectivity issues when I upload the site to the server, and test it remotely.

What is the safest 'owner' of the database, and what's the safest way to connect?

Thanks

Dan

You may get somewhat different details from different people but I think most will agree with what I'm about to say (I may live to regret those words!). Remember that the goal is give your users a little privileges as possible

owner of the database should be dbo

|||

Create a login which has an entry in your Active Directory (AD)*, and give it the needed permissions.

Map that login to a database user (name it MyAppUser), this user has only needed permisions on the database (e.g. execute stored procedures and maybe SELECTing some fields from some tables).

Use Windows Authentication if it is possible.

Encrypt your ConnectionString in your Web.Config file.

*: you can enforce some policies like password has to be strong and changed every two weeks or months. Old password can not be used and some policies that can increase the security.

Remember: Too much security doesn't always good.

Good luck.

|||

One more thing I would like to mention is try to use stored procedures ONLY as much as you can.

This will increase the performance (usually) and make your App secure (e.g. SQL Injuction).

Try to not thatMyAppUserother thatEXECstored procedures.

Insred of sending a lot of T-SQL statments over the network, you will just send the stored procedure name.. and once it is executed it will be cached (better performance for later execution).

Make you logic in the stored procedure, allow you to change the logic later -if needed- without redeploying the application or compiling it.

Good luck.

|||

OK, so stored procedures seems to be a common theme.

hodw do I best use them(SP), and use the GUI advantage of visual studio.net?

Do I write, say a SP called "SP_Update_Client()" Then have the asp.net page call

"SP_Update_Client("Param1","Param2")

and how do I get a hold of the stored procedure IN Visual studio?

thanks

dan

(Im getting lazzy in this GUI world)

|||

You don't "get hold" of a proc like you would, say, a dll. You create a sql command and attach parameters to it as in this example http://www.codeproject.com/useritems/simplecodeasp.asp

Note esp their use of output parameters to return data

|||

hummm-

I think Im starting to get it.

If I am writing a small app (500 users, connecting 10 - 25 x a week) will I notice a benifit of procs? in speed? Or is it more of a security issue at this size?

Thanks so much for the discussion an the artilce

|||

Harperator:

If I am writing a small app (500 users, connecting 10 - 25 x a week) will I notice a benifit of procs? in speed? Or is it more of a security issue at this size?

Stored Procedure = Both security + performance, but the main thing here is the security especially SQL Injuction.

Good luck.

|||

Agree with CS4Ever's statement

Best Practice for Structuring XML?

I've created a large XML document from a relational database (using AUTO,
EXPLICIT, etc.) with many elements, attributes, and subelements but now
wonder if there is a "best practice" for designing the structure for going
the other way, XML -> relational. Since I have not yet worked on the data
extraction side, maybe what I've put together makes data extraction awkward
(requiring many lines of T-SQL vs. one or two). But I definitely cannot
stomach the 'all attribute' or 'all element' practices. Between the two
examples below, which is better/easier/more efficient/flexible for
retrieving information (e.g. with OPENXML). I like the first example
theoretically, but the second is REAL easy to generate (with FOR XML AUTO,
ELEMENTS). Thanks for any tips or insights.
<entities>
<entity>
<entityAttribute>nameOfThisEntity</entityAttribute>
<entityValue>valueOfThisEntity</entityValue>
</entity>
<entity>
<entityAttribute>nameOfNextEntity</entityAttribute>
<entityValue>valueOfNextEntity</entityValue>
</entity>
<entity>
...
</entity>
</entities>
vs.
<entities>
<nameOfThisEntity>valueOfThisEntity</nameOfThisEntity>
<nameOfNextEntity>valueOfNextEntity</nameOfNextEntity>
...
</entities>
Hi Don,
I preferred to the first one, although I do not think there will be much
performance difference between the following two XML structures. The fist
XML structure will be more readable and efficient for search. The following
article will tell you how to optimize SQLXML performance for databases,
including SQL Server 2000.
SQLXML best practice paper on MSDN
http://msdn.microsoft.com/library/de...us/dnsql2k/htm
l/sqlxml_optimperformance.asp?frame=true
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
|||If you plan on using OpenXML, then size and ability to query structure
instead of values will most likely make your second format perform better.
Best regards
Michael
"Don Miller" <nospam@.nospam.com> wrote in message
news:es8k9YfLEHA.2396@.TK2MSFTNGP12.phx.gbl...
> I've created a large XML document from a relational database (using AUTO,
> EXPLICIT, etc.) with many elements, attributes, and subelements but now
> wonder if there is a "best practice" for designing the structure for going
> the other way, XML -> relational. Since I have not yet worked on the data
> extraction side, maybe what I've put together makes data extraction
> awkward
> (requiring many lines of T-SQL vs. one or two). But I definitely cannot
> stomach the 'all attribute' or 'all element' practices. Between the two
> examples below, which is better/easier/more efficient/flexible for
> retrieving information (e.g. with OPENXML). I like the first example
> theoretically, but the second is REAL easy to generate (with FOR XML AUTO,
> ELEMENTS). Thanks for any tips or insights.
> <entities>
> <entity>
> <entityAttribute>nameOfThisEntity</entityAttribute>
> <entityValue>valueOfThisEntity</entityValue>
> </entity>
> <entity>
> <entityAttribute>nameOfNextEntity</entityAttribute>
> <entityValue>valueOfNextEntity</entityValue>
> </entity>
> <entity>
> ...
> </entity>
> </entities>
> vs.
> <entities>
> <nameOfThisEntity>valueOfThisEntity</nameOfThisEntity>
> <nameOfNextEntity>valueOfNextEntity</nameOfNextEntity>
> ...
> </entities>
>

Best Practice for SQL Server Null Values / Empty Strings

I'm fairly new to SQL Server. Coming from Acces, I see that Null values are handled differently. I've read many of the posts on querying Null values, but I want to know what is the best practice for designing a new system (SQL Server 2000) that could co
ntain empty fields.
For example, suppose I have a 'Phone' field that is often, but not always, filled in. If the user blanks out a phone number, the .NET DataAdapter .Update method will save the field as an empty string instead of a NULL. This of course makes every query m
ore complex having to check for both nulls and empty strings.
Is there any practical way to prevent, at the database level, the empty strings from getting into the database? (Perhaps triggers or some global setting?) Or should the string fields be empty strings and never nulls...? I could re-write the data adapte
r, but I don't know if I can trust that every program that touches the database will have handled the issue correctly.
Any opionions?
Thanks,
Denise
using VB.Net and ADO.Net code and if the user blanks out a field, ADO.Net by default it sometimes saves them as an empty string
Denise,
I'll give you my opinion, for what it's worth.
A NULL means an unknown state. Therefore if you do not know the person's
phone number then it is unknown, therefore NULL.
An empty string is a positive entry into the database. It could be
interpreted as, "I KNOW that this value is empty" and could therefore be
interpreted as "this person doesn't have a phone".
This is a subtle difference to NULL. NULL just means "I don't know", or
unknown state. I doubt there is any performance difference between the
two, however I haven't tested it.
I'm not sure if I've actually answered your question because you need to
ensure you are passing NULLs to the database and not empty strings in
your data access layer code. If you want to prevent empty strings from
entering the database, then you could use a constraint like this:
create table a (i int not null, c varchar(30) null check (c <> ''))
insert a (i,c) values (1,null) -- succeeds
insert a (i,c) values (2,'') -- fails
select * from a
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Denise wrote:
> I'm fairly new to SQL Server. Coming from Acces, I see that Null
values are handled differently. I've read many of the posts on querying
Null values, but I want to know what is the best practice for designing
a new system (SQL Server 2000) that could contain empty fields.
> For example, suppose I have a 'Phone' field that is often, but not
always, filled in. If the user blanks out a phone number, the .NET
DataAdapter .Update method will save the field as an empty string
instead of a NULL. This of course makes every query more complex having
to check for both nulls and empty strings.
> Is there any practical way to prevent, at the database level, the
empty strings from getting into the database? (Perhaps triggers or some
global setting?) Or should the string fields be empty strings and never
nulls...? I could re-write the data adapter, but I don't know if I can
trust that every program that touches the database will have handled the
issue correctly.
> Any opionions?
> Thanks, Denise
>
> using VB.Net and ADO.Net code and if the user blanks out a field, ADO.Net by default it sometimes saves them as an empty string
|||There are strong debates regarding whether or not nulls should ever be
allowed in data columns. I agree with Mark, if you do not know the value
allow null ( even though it may require programming on the front end.)
Others ( Kalen Delaney for instance) make strong arguments for never
allowing nulls in the database.
This is an area where reasonable people differ in their opinions, so do
whatever works for you, with a clear conscience..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:O07qtuHSEHA.2000@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Denise,
> I'll give you my opinion, for what it's worth.
> A NULL means an unknown state. Therefore if you do not know the person's
> phone number then it is unknown, therefore NULL.
> An empty string is a positive entry into the database. It could be
> interpreted as, "I KNOW that this value is empty" and could therefore be
> interpreted as "this person doesn't have a phone".
> This is a subtle difference to NULL. NULL just means "I don't know", or
> unknown state. I doubt there is any performance difference between the
> two, however I haven't tested it.
> I'm not sure if I've actually answered your question because you need to
> ensure you are passing NULLs to the database and not empty strings in
> your data access layer code. If you want to prevent empty strings from
> entering the database, then you could use a constraint like this:
> --
> create table a (i int not null, c varchar(30) null check (c <> ''))
> insert a (i,c) values (1,null) -- succeeds
> insert a (i,c) values (2,'') -- fails
> select * from a
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Denise wrote:
> values are handled differently. I've read many of the posts on querying
> Null values, but I want to know what is the best practice for designing
> a new system (SQL Server 2000) that could contain empty fields.
> always, filled in. If the user blanks out a phone number, the .NET
> DataAdapter .Update method will save the field as an empty string
> instead of a NULL. This of course makes every query more complex having
> to check for both nulls and empty strings.
> empty strings from getting into the database? (Perhaps triggers or some
> global setting?) Or should the string fields be empty strings and never
> nulls...? I could re-write the data adapter, but I don't know if I can
> trust that every program that touches the database will have handled the
> issue correctly.
ADO.Net by default it sometimes saves them as an empty string
|||you've definately opened a can of worms. (Religious topic)
Many argue that nulls suggest a normalization problem.
Nulls will cause performance issues
Nulls will force you to add handling into your sprocs etc.
it's your call of course whether or not you wish to use them.
I lean towards not using them except in rare situations, but that's just me.
Cheers,
Greg Jackson
PDX, Oregon
|||Thanks for all your insight.

best practice analyzer feedback

Hi I've been running the BPA for a few weeks now and have followed all the
recommendations to correct my databases, but I consistently get the following
2 Non Compliance errors.
1. One or more of the databases failed the scan. It is recommended that data
and log files be kept on separate drives.
I've tried to move the log files but it won't let me even when nobody is
using the databases.
2. One or more triggers is either setting NOCOUNT to OFF or missing NOCOUNT
setting! It is generally recommended to explicitly set NOCOUNT option to ON
at the beginning of a trigger.
Here is the trigger reffered to:-
CREATE TRIGGER trEnterRelatedEntitlement ON [dbo].[tblEmployee]
FOR INSERT
AS
IF @.@.ROWCOUNT=0
RETURN
SET NOCOUNT ON
INSERT INTO dbo.tblEntitlement (strLogonName, intYear)
SELECT strlogonname, YEAR(CURRENT_TIMESTAMP)
FROM Inserted
As can be seen the SET NOCOUNT ON is included and it won't work if it's at
the beginning of the trigger.
The rest of the tool seems to work OK.
1 - Try to detach database first then move log file, then attach database
"jez123456" <jez123456@.discussions.microsoft.com> wrote in message
news:2A879E25-DA67-4A0E-9CC8-A06A3D396B43@.microsoft.com...
> Hi I've been running the BPA for a few weeks now and have followed all the
> recommendations to correct my databases, but I consistently get the
following
> 2 Non Compliance errors.
> 1. One or more of the databases failed the scan. It is recommended that
data
> and log files be kept on separate drives.
> I've tried to move the log files but it won't let me even when nobody is
> using the databases.
>
> 2. One or more triggers is either setting NOCOUNT to OFF or missing
NOCOUNT
> setting! It is generally recommended to explicitly set NOCOUNT option to
ON
> at the beginning of a trigger.
> Here is the trigger reffered to:-
> CREATE TRIGGER trEnterRelatedEntitlement ON [dbo].[tblEmployee]
> FOR INSERT
> AS
> IF @.@.ROWCOUNT=0
> RETURN
> SET NOCOUNT ON
> INSERT INTO dbo.tblEntitlement (strLogonName, intYear)
> SELECT strlogonname, YEAR(CURRENT_TIMESTAMP)
> FROM Inserted
> As can be seen the SET NOCOUNT ON is included and it won't work if it's at
> the beginning of the trigger.
> The rest of the tool seems to work OK.
>
>
|||Thanks t, detach and attach worked great. Do you have any idea about the
NOCOUNT problem?
jez
"t" wrote:

> 1 - Try to detach database first then move log file, then attach database
> "jez123456" <jez123456@.discussions.microsoft.com> wrote in message
> news:2A879E25-DA67-4A0E-9CC8-A06A3D396B43@.microsoft.com...
> following
> data
> NOCOUNT
> ON
>
>
|||Hi
It is an interesting pattern... I'll open a tracking item to see if we can
relax the check for the next release.
Thanks for the feedback.
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"jez123456" <jez123456@.discussions.microsoft.com> wrote in message
news:D67E5CBA-C13E-4272-A100-B613BD7F4E82@.microsoft.com...[vbcol=seagreen]
> Thanks t, detach and attach worked great. Do you have any idea about the
> NOCOUNT problem?
> jez
> "t" wrote:
database[vbcol=seagreen]
the[vbcol=seagreen]
that[vbcol=seagreen]
is[vbcol=seagreen]
to[vbcol=seagreen]
it's at[vbcol=seagreen]

Best Practice Analyzer

First of all, let me say I think the Best Practice Analyzer is great - it's
a valuable tool. I've already identified a few things in my database that I
might not have ever seen without this tool.
I'd like to know if a couple of things are possible in the SQL Server Best
Practice Analyzer. If they are not, then please consider them as
suggestions:
a.. When I scan a server, is it possible to choose the databases I'd liked
scanned? I don't really care about scanning Northwind and Pubs.
b.. Is it possible to scan objects that begin with a certain prefix? For
example, the objects that I create all begin with certain prefix; IQWP_ for
example. Another developer's objects begin with IQP_. The objects are for
different apps, thus the difference in the naming convention. I'd prefer in
most cases to scan only my objects. Along this same line, it would be nice
to be able to exclude items using a wildcard - similar to how we do this
same thing in Profiler. For instance, exclude %test%.
c.. Is there a way to sort by sproc/view name when I click on "Scan
Details" in a report? It's pretty sporadic now.
Thanks, Andre
I think (a) may be in the works, the other two sound like great suggestions.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Andre" <AndreGetsEnoughSPAM@.nospam.com> wrote in message
news:%23%23IbKt7NEHA.3936@.TK2MSFTNGP12.phx.gbl...
> First of all, let me say I think the Best Practice Analyzer is great -
it's
> a valuable tool. I've already identified a few things in my database that
I
> might not have ever seen without this tool.
>
> I'd like to know if a couple of things are possible in the SQL Server Best
> Practice Analyzer. If they are not, then please consider them as
> suggestions:
>
> a.. When I scan a server, is it possible to choose the databases I'd
liked
> scanned? I don't really care about scanning Northwind and Pubs.
> b.. Is it possible to scan objects that begin with a certain prefix?
For
> example, the objects that I create all begin with certain prefix; IQWP_
for
> example. Another developer's objects begin with IQP_. The objects are
for
> different apps, thus the difference in the naming convention. I'd prefer
in
> most cases to scan only my objects. Along this same line, it would be
nice
> to be able to exclude items using a wildcard - similar to how we do this
> same thing in Profiler. For instance, exclude %test%.
> c.. Is there a way to sort by sproc/view name when I click on "Scan
> Details" in a report? It's pretty sporadic now.
> Thanks, Andre
>
|||Thanks Jasper.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:Opvdb%23AOEHA.1272@.tk2msftngp13.phx.gbl...
> I think (a) may be in the works, the other two sound like great
suggestions.[vbcol=seagreen]
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Andre" <AndreGetsEnoughSPAM@.nospam.com> wrote in message
> news:%23%23IbKt7NEHA.3936@.TK2MSFTNGP12.phx.gbl...
> it's
that[vbcol=seagreen]
> I
Best[vbcol=seagreen]
> liked
> For
> for
> for
prefer
> in
> nice
>
|||Hi Andre
As usual Jasper's right :-) I've noted b & c for future enhancements.
Thanks for the feedback.
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andre" <AndreGetsEnoughSPAM@.nospam.com> wrote in message
news:ev0yczEOEHA.904@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Thanks Jasper.
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:Opvdb%23AOEHA.1272@.tk2msftngp13.phx.gbl...
> suggestions.
> that
> Best
IQWP_[vbcol=seagreen]
are[vbcol=seagreen]
> prefer
this
>
|||Would like to be able to schedule and mail reports automatically. When there are dozens of servers to review, this would be nice.
Also, from an announcement elsewhere: quote
Key features include the ability to generate reports on the databases you scan. The reports are generated in a Reporting Services–compatible format, so you can also send out automated nastygrams to people who oversee databases that aren't compliant with
your company's best practices.
endquote
So far, I haven't quite been able to understand how nastygrams or reports can be generated for viewing outside of the MS-SQLBPA user interface box.
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
|||I'm assuming you're using the v1 bits posted on the web two weeks back.
To schedule and mail reports automatically you'll need to use SQL Server
Reporting Services. http://www.microsoft.com/sql/reporting/default.asp
BPA user's guide has a quick description of the steps needed to get BPA
reports.
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:%23I1UNYnSEHA.3448@.TK2MSFTNGP09.phx.gbl...
> Would like to be able to schedule and mail reports automatically. When
there are dozens of servers to review, this would be nice.
> Also, from an announcement elsewhere: quote
> Key features include the ability to generate reports on the databases you
scan. The reports are generated in a Reporting Services?"compatible format,
so you can also send out automated nastygrams to people who oversee
databases that aren't compliant with your company's best practices.
> endquote
> So far, I haven't quite been able to understand how nastygrams or reports
can be generated for viewing outside of the MS-SQLBPA user interface box.
>
>
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

Best database IDE around

Hi!
For some time I've been looking at alternative tools for query construction
that could replace the old good Query Analyzer.
I think QA is a great tool for its purpose but it has become outdated and
it's missing some of the features we have come to expect in modern IDEs, like
Intellisense support for database objects, a Visual Studio-like Server
Explorer, some kind of project management (meaning the possibility to
organize my SQL files in projects) and a good debugger for stored procedures,
among others.
I've used Visual Studio .NET for a while for debugging stored procedures and
I think it was good but what I'm looking for is a tool especially dedicated
to the purpose of building queries and (possibly) managing database objects.
I've looked at the new Management Studio for SQL Server 2005 and I think it's
the closest thing to that kind of tools but I'm also interested in free/open
source alternatives.
Does anyone know of such a tool? I'm interested in hearing about what other
database administrators/developers' favourite tools are.
Thanks in advance
/Enrico
You can have a look at foxySQL
http://www.casestudio.com/enu/databa...l_foxysql.aspx
It's free and has some features like intellisense, but to be honest
I've gone back to QA.
M
|||Enrico Campidoglio wrote:

> the closest thing to that kind of tools but I'm also interested in free/open
> source alternatives.
Visit http://www.sqlmanager.net - they offer both free and commercial
utilities for many popular RDBMS (MSSQL, IB/FB, MySQL,...)
Christmas Carol
|||If you are looking to use QA or SQL Server 2005 Management Studio, and
want to add SQL Intellisense to these tools then you might want to
check out PromptSQL: http://www.promptsql.com/
Its not free, but at US$25/user its not too expensive either. One
month evaluation download.

Best Configuration for a 3 Node SQL 2000 Cluster on Windows 2003?

Ok, I've got the cluster setup and running, but having never done this,
I'm not sure if I'm setting up SQL right... We're trying to migrate
our multitude of SQL Server running on older hardware to the new
cluster, but I want to make sure we don't shoot ourselves in the foot.
Here is what we've got:
Specs:
3 HP BL20P Blade Server (Twin 3.6GHz Xeon, 4GB Ram)
1 HP MSA1000 w/ Twin Fibre Switches (Dual Path Redundancy)
Current Setup:
Windows 2003 Enterprise, 20GB C:, 10GB D: (Pagefile), 37GB E: Data
MSA1000 is currently configured with 4 36GB Arrays (Quorum, 2 for Trans
Logs, and 1 for Backups) and 2 120GB Arrays (Database Data), but there
is about 1.2TB left on the controller for additional space.
Followed all the instructions, Public IPs, Private IPs, etc...
Installed SQL on the first two nodes (SQLCL01 & SQLCL02) and have two
Virtual Servers (Same name, actual server name is longer and unique),
and then two instances, INST1 on SQLCL01, and INST2 on SQLCL02.
SQLCL03 is the failover server which of course is identical to the
first two. We're never expecting to have 2 fail, but we may add a 4th
Server/Node in the future (we have 5 additional slots in our two Blade
Chassis).

>From what I've read you "can" install up to 16 instances into a single
cluster, but obviously I don't know if #1 I should try and install more
than 2 instances, or #2 if I even can. I've tried rerunning the SQL
Setup just to see, and all it lets me do it "modify" the current
install or remove it, it won't let me add another instance.
So... What am I looking at here? Is this the optimum configuration
for now, or can I do more? What about memory? Should I limit each SQL
Server instance to a certain level of RAM, say 3GB? Or possibly 2GB,
incase both the two main servers ever fail and everything gets forced
to the 3rd? These servers will pretty much only be used for SQL,
nothing else, so there isnt' too much worry about applications battling
for memory.
Thanks in advance. I know some of these questions may seem rather
newbish, but I've installed and administered SQL2k before, but never a
cluster... so this is new ground for me and the documentation out
there is not very helpful, most of it refers to SQL2k on Windows 2000,
not Windows Server 2003.
Jon Casimir
Lotsa comments inline.
<kazsmir@.gmail.com> wrote in message
news:1125515891.775160.31810@.z14g2000cwz.googlegro ups.com...
> Ok, I've got the cluster setup and running, but having never done this,
> I'm not sure if I'm setting up SQL right... We're trying to migrate
> our multitude of SQL Server running on older hardware to the new
> cluster, but I want to make sure we don't shoot ourselves in the foot.
> Here is what we've got:
> Specs:
> 3 HP BL20P Blade Server (Twin 3.6GHz Xeon, 4GB Ram)
> 1 HP MSA1000 w/ Twin Fibre Switches (Dual Path Redundancy)
> Current Setup:
> Windows 2003 Enterprise, 20GB C:, 10GB D: (Pagefile), 37GB E: Data
> MSA1000 is currently configured with 4 36GB Arrays (Quorum, 2 for Trans
> Logs, and 1 for Backups) and 2 120GB Arrays (Database Data), but there
> is about 1.2TB left on the controller for additional space.
>
I am not a big fan of blade servers as cluster nodes. Too many single
failure points for what is intended to be a highly available system.
A well-tuned, dedicated SQL server should have minimal need for a paging
file. If you are paging heavily, you have something tuned wrong.
Backups should never be stored on the same host computer or storage array as
the primary data store, even if they are on separate physical disks. Backup
across the net to a file share for immediate use and archive those files to
tape for longer retention periods.

> Followed all the instructions, Public IPs, Private IPs, etc...
> Installed SQL on the first two nodes (SQLCL01 & SQLCL02) and have two
> Virtual Servers (Same name, actual server name is longer and unique),
> and then two instances, INST1 on SQLCL01, and INST2 on SQLCL02.
> SQLCL03 is the failover server which of course is identical to the
> first two. We're never expecting to have 2 fail, but we may add a 4th
> Server/Node in the future (we have 5 additional slots in our two Blade
> Chassis).
This doesn't sound right. You should have each Virtual Server\Instance
combination installed on all nodes on the cluster so you can fail over as
needed. During install time you can select which cluster nodes to install
SQL to. You can set the preferred node order of each Virtual Server later
independently so they start and fail where you choose.
On a multi-node, multi-instance cluster, I usually only worry about
first-order failures. If I have more than one instance go south on me, it
is usually the entire cluster that bombs. If I have one instance with a
problem, somebody competent better be standing in front of it fixing the
problem within 30 minutes. You can adjust memory settings and failover
order at that time.

> cluster, but obviously I don't know if #1 I should try and install more
> than 2 instances, or #2 if I even can. I've tried rerunning the SQL
> Setup just to see, and all it lets me do it "modify" the current
> install or remove it, it won't let me add another instance.
>
SQL won't let you add a new virtual server unless there is at least one
unassigned cluster disk resource to anchor the instance. Whether you
"should" install more instances is another matter. Each instance looks and
acts like a separate server on the network. Generally, multiple instances
in a cluster are used to manage security and performance. In my history, I
find that one instance per node + one spare is an optimal configuration, but
your needs with this server consolidation project may vary.

> So... What am I looking at here? Is this the optimum configuration
> for now, or can I do more? What about memory? Should I limit each SQL
> Server instance to a certain level of RAM, say 3GB? Or possibly 2GB,
> incase both the two main servers ever fail and everything gets forced
> to the 3rd? These servers will pretty much only be used for SQL,
> nothing else, so there isnt' too much worry about applications battling
> for memory.
Since you have paid for Enterprise Edition anyway you should max out the
memory, although your choice of blade servers as hosts may limit that
expansion capability. You will need to consider what happens during a
failover so that you can tolerate "stacking" multiple instances on the same
nost node.

> Thanks in advance. I know some of these questions may seem rather
> newbish, but I've installed and administered SQL2k before, but never a
> cluster... so this is new ground for me and the documentation out
> there is not very helpful, most of it refers to SQL2k on Windows 2000,
> not Windows Server 2003.
Most of the considerations for Windows 2000 clustering apply to Windows
2003, except for some installation gotchas. Unless you are sure something
from Windows 2000 doesn't apply, assume it does.
Now is the best time to ask "dumb" questions. Later, when your "highly
available" database solution that you bet your job on is down is the worst
time.

> Jon Casimir
>
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

Being able to watch different processes from a web server.

I've got a web server that has many connections going to it. When I'm doing
debugging (sometimes I have to debug on the live server - get over it!) I'll
start up profiler to be able to see the exact SQL statement that is being
sent. Problem is, since there are a lot of users on the system and profiler
tracks them all, it just flies by and it's almost impossible to find
anything.
Is there an easy way to track the specific task for me? Remembering that
every time I connect/disconnect I'll get a different connection ID. About
the only thing I can think of is in my web site do something different when
it's me debugging (like log in as a different user) except this would be
heinous to have to go through and change all my code.
Anybody have any suggestions?
TIA - Jeff.
Hello,
Best way is to load the profiler log data in to a table and do the analysis.
As well as take a look into the server side trace.
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
Thanks
Hari
"UJ" <fred@.nowhere.com> wrote in message
news:OZ1vCpyMHHA.4712@.TK2MSFTNGP04.phx.gbl...
> I've got a web server that has many connections going to it. When I'm
> doing debugging (sometimes I have to debug on the live server - get over
> it!) I'll start up profiler to be able to see the exact SQL statement that
> is being sent. Problem is, since there are a lot of users on the system
> and profiler tracks them all, it just flies by and it's almost impossible
> to find anything.
> Is there an easy way to track the specific task for me? Remembering that
> every time I connect/disconnect I'll get a different connection ID. About
> the only thing I can think of is in my web site do something different
> when it's me debugging (like log in as a different user) except this would
> be heinous to have to go through and change all my code.
> Anybody have any suggestions?
> TIA - Jeff.
>
|||I guess I wasn't clear. The problem is that if all of my webservices log in
as the same account, how can I pare it down to just the commands I did?
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ePZRJuyMHHA.4720@.TK2MSFTNGP03.phx.gbl...
> Hello,
> Best way is to load the profiler log data in to a table and do the
> analysis. As well as take a look into the server side trace.
> http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
>
> Thanks
> Hari
> "UJ" <fred@.nowhere.com> wrote in message
> news:OZ1vCpyMHHA.4712@.TK2MSFTNGP04.phx.gbl...
>

Beginner at Backups

I've spent the afternoon reading as much as possible on how to backup and
restore databases except there are a couple of small areas I can't quite get
my head round, so apologies for the beginners question but here goes:
I can set up a disk backup device and then every night run the 'Backup
Database' command to back up our database to a local folder, on top of this
I can run the 'backup log with truncate_only' every 20 minutes to provide
even better restore capability. I have tested these functions and
successfully restored the DB, but in this scenario the backup device is one
file and will simlpy keep growing every day. How can I overcome this? Should
I be creating a new file for every day and then deleting old files? Also,
should each transaction log be backed up to a new file - if not then how do
you know when to start a new backup file for the transaction logs without
the possibility of loosing data?
Cheers, Tom
Hi,
Use the INIT option along with BACKUP database command to reinitialize the
file every time.
Backup database dbname to device_name with INIT
See books online for more detail
Thanks
Hari
MCDBA
"Tom Clark" <tom_clark100@.hotmail.com> wrote in message
news:eTN0zsweEHA.140@.TK2MSFTNGP12.phx.gbl...
> I've spent the afternoon reading as much as possible on how to backup and
> restore databases except there are a couple of small areas I can't quite
get
> my head round, so apologies for the beginners question but here goes:
> I can set up a disk backup device and then every night run the 'Backup
> Database' command to back up our database to a local folder, on top of
this
> I can run the 'backup log with truncate_only' every 20 minutes to provide
> even better restore capability. I have tested these functions and
> successfully restored the DB, but in this scenario the backup device is
one
> file and will simlpy keep growing every day. How can I overcome this?
Should
> I be creating a new file for every day and then deleting old files? Also,
> should each transaction log be backed up to a new file - if not then how
do
> you know when to start a new backup file for the transaction logs without
> the possibility of loosing data?
> Cheers, Tom
>
|||1. With TRUNCATE Only will ONLY truncate the logs (Basically smokes Em)
2. Instead of overwriting the backup file each night, it is fairly typical
to move the OLD Backup to an archive location and save a few previous
backups. OR you can just delete it and replace it with the new backup file.
We typically name our Backup Files with a date time element
...\Backups\DatabaseName\Full\DatabaseNameXX_FULL _MMDDYYYYMMSS.Bak
Where XX is the File Number (We create 4 seperate files for each full backup
to improve backup performance as SQL Server can write to multiple files at
once using multiple threads)
3. We write each transaction backup to a seperate file following the same
naming convention.
...\Backups\DatabaseName\TRXN\DatabaseName_Trnxn_ MMDDYYYYMMSS.Bak
we backup our transaction logs every 15 minutes.
AFTER each successful FULL Backup, we move the Old Transaction Logs to an
Archive Location. in the archive folder we keep 48 hours of logs. We delete
files older than 48 hours.
this may not be perfect, but it works for us and is a good start for you.
Cheers,
Greg Jackson
PDX, Oregon
|||Cheers Greg, this was exactly the kind of answer I was after!
Thinking on though, if you had to do a restore and then apply all the logs
up to the last log backup, would you do this by script (or apply each
transaction log by hand) and if so how? The testing I've done has only been
with a small number of log files and so I have applied them one by one.
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:eb68X3weEHA.4068@.TK2MSFTNGP11.phx.gbl...
> 1. With TRUNCATE Only will ONLY truncate the logs (Basically smokes Em)
> 2. Instead of overwriting the backup file each night, it is fairly
typical
> to move the OLD Backup to an archive location and save a few previous
> backups. OR you can just delete it and replace it with the new backup
file.
> We typically name our Backup Files with a date time element
> ...\Backups\DatabaseName\Full\DatabaseNameXX_FULL_ MMDDYYYYMMSS.Bak
> Where XX is the File Number (We create 4 seperate files for each full
backup
> to improve backup performance as SQL Server can write to multiple files at
> once using multiple threads)
> 3. We write each transaction backup to a seperate file following the same
> naming convention.
> ...\Backups\DatabaseName\TRXN\DatabaseName_Trnxn_M MDDYYYYMMSS.Bak
> we backup our transaction logs every 15 minutes.
> AFTER each successful FULL Backup, we move the Old Transaction Logs to an
> Archive Location. in the archive folder we keep 48 hours of logs. We
delete
> files older than 48 hours.
>
> this may not be perfect, but it works for us and is a good start for you.
>
> Cheers,
>
> Greg Jackson
> PDX, Oregon
>
|||you can do it by hand or by script if you wanted.
I've actually done it by hand most often in past.
GAJ

Begin some web programming

I know there are lots of books out there.. Ive been a DBA(not an all rounder
like most of you'll are that can program as well) for a while and have now
decided to do some Web programming,,i,e, basically using SQL Server as data
source and maybe generating some reports,etc.. Can one guide me into
achieving this through some online content thats out there ?Some step by
step stuff...Using SQL 2000You'll want to pick a software product to program in first. For
ASP.NET resources, see http://msdn.microsoft.com/asp.net/
-- Mary
MCW Technologies
http://www.mcwtech.com
On Sun, 1 Feb 2004 11:43:38 -0800, "Hassan" <fatima_ja@.hotmail.com>
wrote:

quote:

---

>I know there are lots of books out there.. Ive been a DBA(not an all rounde
r
>like most of you'll are that can program as well) for a while and have now
>decided to do some Web programming,,i,e, basically using SQL Server as data
>source and maybe generating some reports,etc.. Can one guide me into
>achieving this through some online content thats out there ?Some step by
>step stuff...Using SQL 2000
>

---

|||Hi Hassan.
I found the msdn VB.Net 101's a very useful resource, so if you choose to
code in VB.Net, drop by here:
http://msdn.microsoft.com/vbasic/do...101samples.aspx
HTH
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OaETxuP6DHA.2300@.TK2MSFTNGP10.phx.gbl...

quote:

---

> I know there are lots of books out there.. Ive been a DBA(not an all

---

rounder

quote:

---

> like most of you'll are that can program as well) for a while and have

---

now

quote:

---

> decided to do some Web programming,,i,e, basically using SQL Server as

---

data

quote:

---

> source and maybe generating some reports,etc.. Can one guide me into
> achieving this through some online content thats out there ?Some step by
> step stuff...Using SQL 2000
>

---

|||When would i use ASP.net or VB.net ? Do I need to use both ? Whats the
difference ?
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:%23%23J01eQ6DHA.1596@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Hi Hassan.
> I found the msdn VB.Net 101's a very useful resource, so if you choose to
> code in VB.Net, drop by here:
> http://msdn.microsoft.com/vbasic/do...101samples.aspx
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OaETxuP6DHA.2300@.TK2MSFTNGP10.phx.gbl...
> rounder
> now
> data
>

---

|||When you develop within the ASP.Net framework, you choose a .Net language to
program asp pages with.
You can program ASP.Net pages with any of the .Net languages - C#, VB.Net,
C++.
I generally use C# or VB.
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ee8Dr$T6DHA.2168@.TK2MSFTNGP12.phx.gbl...

quote:

---

> When would i use ASP.net or VB.net ? Do I need to use both ? Whats the
> difference ?
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:%23%23J01eQ6DHA.1596@.TK2MSFTNGP10.phx.gbl...
to[QUOTE]
have[QUOTE]
by[QUOTE]
>

---

Begin some web programming

I know there are lots of books out there.. Ive been a DBA(not an all rounder
like most of you'll are that can program as well) for a while and have now
decided to do some Web programming,,i,e, basically using SQL Server as data
source and maybe generating some reports,etc.. Can one guide me into
achieving this through some online content thats out there ?Some step by
step stuff...Using SQL 2000You'll want to pick a software product to program in first. For
ASP.NET resources, see http://msdn.microsoft.com/asp.net/
-- Mary
MCW Technologies
http://www.mcwtech.com
On Sun, 1 Feb 2004 11:43:38 -0800, "Hassan" <fatima_ja@.hotmail.com>
wrote:
>I know there are lots of books out there.. Ive been a DBA(not an all rounder
>like most of you'll are that can program as well) for a while and have now
>decided to do some Web programming,,i,e, basically using SQL Server as data
>source and maybe generating some reports,etc.. Can one guide me into
>achieving this through some online content thats out there ?Some step by
>step stuff...Using SQL 2000
>|||Hi Hassan.
I found the msdn VB.Net 101's a very useful resource, so if you choose to
code in VB.Net, drop by here:
http://msdn.microsoft.com/vbasic/downloads/samples/101samples.aspx
HTH
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OaETxuP6DHA.2300@.TK2MSFTNGP10.phx.gbl...
> I know there are lots of books out there.. Ive been a DBA(not an all
rounder
> like most of you'll are that can program as well) for a while and have
now
> decided to do some Web programming,,i,e, basically using SQL Server as
data
> source and maybe generating some reports,etc.. Can one guide me into
> achieving this through some online content thats out there ?Some step by
> step stuff...Using SQL 2000
>|||When would i use ASP.net or VB.net ? Do I need to use both ? Whats the
difference ?
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:%23%23J01eQ6DHA.1596@.TK2MSFTNGP10.phx.gbl...
> Hi Hassan.
> I found the msdn VB.Net 101's a very useful resource, so if you choose to
> code in VB.Net, drop by here:
> http://msdn.microsoft.com/vbasic/downloads/samples/101samples.aspx
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OaETxuP6DHA.2300@.TK2MSFTNGP10.phx.gbl...
> > I know there are lots of books out there.. Ive been a DBA(not an all
> rounder
> > like most of you'll are that can program as well) for a while and have
> now
> > decided to do some Web programming,,i,e, basically using SQL Server as
> data
> > source and maybe generating some reports,etc.. Can one guide me into
> > achieving this through some online content thats out there ?Some step by
> > step stuff...Using SQL 2000
> >
> >
>|||When you develop within the ASP.Net framework, you choose a .Net language to
program asp pages with.
You can program ASP.Net pages with any of the .Net languages - C#, VB.Net,
C++.
I generally use C# or VB.
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ee8Dr$T6DHA.2168@.TK2MSFTNGP12.phx.gbl...
> When would i use ASP.net or VB.net ? Do I need to use both ? Whats the
> difference ?
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:%23%23J01eQ6DHA.1596@.TK2MSFTNGP10.phx.gbl...
> > Hi Hassan.
> >
> > I found the msdn VB.Net 101's a very useful resource, so if you choose
to
> > code in VB.Net, drop by here:
> >
> > http://msdn.microsoft.com/vbasic/downloads/samples/101samples.aspx
> >
> > HTH
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:OaETxuP6DHA.2300@.TK2MSFTNGP10.phx.gbl...
> > > I know there are lots of books out there.. Ive been a DBA(not an all
> > rounder
> > > like most of you'll are that can program as well) for a while and
have
> > now
> > > decided to do some Web programming,,i,e, basically using SQL Server as
> > data
> > > source and maybe generating some reports,etc.. Can one guide me into
> > > achieving this through some online content thats out there ?Some step
by
> > > step stuff...Using SQL 2000
> > >
> > >
> >
> >
>