Best Technology for Reporting.

Current Environment:
SQL 2000 SP4 EE on Windows 2003 SP1
I need some suggestion on some of the currently available options for
reporting.
We have a transactional database where lots of transactions come throughout
the day. Some of the tables can have over 200 k records added. We need to
aggregate data on another server for reporting purposes and currently all
this is done via DTS jobs which do lots of locking and time consuming.
Since we are looking into re-architecting this solution and go away from
DTS, I am looking for various options that we can set up in test environment
and see which one supercedes over another one. Also, should I look into SQL
2005 or stay with SQL 2000 only.
Please advice.
Thanks in Advance."Mark" <Mark@.discussions.microsoft.com> wrote in message
news:8F1E6BD8-A36D-4D3E-8799-C67DF4F19D9D@.microsoft.com...
> Current Environment:
> SQL 2000 SP4 EE on Windows 2003 SP1
> I need some suggestion on some of the currently available options for
> reporting.
> We have a transactional database where lots of transactions come
> throughout
> the day. Some of the tables can have over 200 k records added. We need to
> aggregate data on another server for reporting purposes and currently all
> this is done via DTS jobs which do lots of locking and time consuming.
> Since we are looking into re-architecting this solution and go away from
> DTS, I am looking for various options that we can set up in test
> environment
> and see which one supercedes over another one. Also, should I look into
> SQL
> 2005 or stay with SQL 2000 only.
> Please advice.
> Thanks in Advance.
If the jobs are doing that much locking, then I would probably look at
rearchitecting the jobs themselves. Take advantage of the WITH (NOLOCK)
hints where appropriate etc.
Having your reporting done on the live data with the same aggregations that
your DTS jobs are doing doesn't seem like a particularly good idea to me.
Your performance will be better on the aggregated data already stored in the
reporting database server.
Just my .02
Rick Sawtell
MCT, MCSD, MCDBAsql

Best Practices Question

Environment is SQL Server 2005 x64 Enterprise running under Windows Server 2003 x64 Enterprise with four processors and 16GB of ram.

I have 28 data copy routines I would like to add to a SSIS package. They use the Data Reader Source to an ODBC database (InterSystems Cache) and copy the table contents to a SQL2005 database for reporting needs. The data rows in these 28 routines range from only 100 rows to over 6 million rows depending on the table. I have tested these individually and they work fine. My question is, is it a good practice to have all of these routines in a single package or can I expect performance degragation?

A single package is fine, but multiple packages are more supportable.|||

Thanks Phil. I should note that in this package, all 28 copy routines are in the same data flow object. Would it be better to have them in separate data flow objects? Also, when you say more supportable, are you referring to making it easier to troubleshoot?

|||

windthorstking wrote:

Thanks Phil. I should note that in this package, all 28 copy routines are in the same data flow object. Would it be better to have them in separate data flow objects? Also, when you say more supportable, are you referring to making it easier to troubleshoot?

I'd probably put the distinct "copy routines" into their own data flows, at the very least.

And yes, I mean it will be easier to troubleshoot.sql

Best practices for remotely connecting

I have a Windows 2003 standard edition server with MSDE 2000 installed
at a client. I would like to be able to connect my SQL Enterprise
manager in case I need to do something in their database and I need to
do it remotely. I have the MSDE set to mixed mode authentication and
opened the ports (1433 and tried 2433 as well because client network
utility said it used that port) but I cannot connect for some reason.
Any clues what I still need to do?
The other question is: is it safe to connect a SQL server directly to
the net? The SQL Server is fully up to date with the latest patches.
Are there any guidelines that I need to follow when I want to do this
(in a safe way)?
Thanks in advance,
Stijn Verrept.You should consider establishing a VPN connection from your remote client.
Then use Terminal Server or Remote Desktop to administer the server.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Kevin McDonnell [MSFT] wrote:

quote:

---

> You should consider establishing a VPN connection from your remote
> client. Then use Terminal Server or Remote Desktop to administer the
> server.

---

Thanks for your answer Kevin. I can login with TS but MSDE does not
include enterprise manager so I cannot install this on the clients pc!
Kind regards,
Stijn Verrept.|||So, unless the client as a Terminal Server that you can install the SQL
Client tools, your options are limited.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Kevin McDonnell [MSFT] wrote:

quote:

---

> So, unless the client as a Terminal Server that you can install the
> SQL Client tools, your options are limited.

---

What do you mean? The client is running a terminal server. And I can
log in on that machine. The problem is that it does not have
Enterprise manager. This is why I need to connect with my enterprise
manager to the terminal server through the net. Isn't this possible?
Why doesn't it want to work?
Kind regards,
Stijn Verrept.|||Here's what I'm recommending:
1. Client -->VPN -->Terminal Server (with Enterprise Manager Installed)
-->MSDE
or
2. Client (with Enterprise Manager) -->VPN -->MSDE
Option 1 would be preferred.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Im having exactly the same problem.
However, we need access to both our SQL Server and their Server as we
do database design work as part of our web development and need to
upload databases we have created.
Weve tried the VPN route, but our policy is to keep our SQL Server
well behind the firewall, and there are no exceptions (Clients company
information kept on them so security is a big deal) so they cant see
our DB.
They have opened all ports for our IP address so all traffic is
allowed, but we can still not connect to their DB.
A simple yes or no is required, Can you connect to a remote MSDE
Server across the internet and register it in SQL Server Enterprise
manager?
TCPIP and Named Pipes are Installed and enabled on MSDE
kevmc@.online.microsoft.com (Kevin McDonnell [MSFT]) wrote in message news:<v40lyXs2DHA.3564@.cp
msftngxa07.phx.gbl>...
> Here's what I'm recommending:
> 1. Client -->VPN -->Terminal Server (with Enterprise Manager Installed)
> -->MSDE
> or
> 2. Client (with Enterprise Manager) -->VPN -->MSDE
> Option 1 would be preferred.
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.|||Can you connect to a remote MSDE
Server across the internet and register it in SQL Server Enterprise
manager?
Simple Answer:
Yes. Over TCP. As long as the ports are open on the firewall.
287932 INF: TCP Ports Needed for Communication to SQL Server Through a
Firewall
http://support.microsoft.com/?id=287932
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thanks Kevin, Thats what I thought, but not what I wanted to hear!
This means we have a different problem which is going to be a nightmare
to sort out.
If SQL Server (MSDE) has the TCPIP netlib enabled and the default port
is being used (1433), do we need to have 1433 enabled on both firewalls
(theirs and ours) to enable enterprise manager to register this server?
Also, Do we need to open port 1434 (Ive heard that this is needed for 2
way communication)
Any other ideas why I can connect to our ISP hosted SQL Servers, but not
our clients (who hosts their own website) even though they have tried
with the server outside the firewall? Even If I make a vpn connection to
the SQL Server box, and can ping it by name, I still cant connect. I get
the error:
SQL Server does not exist or access is denied
ConnectionOpen(Connect())
Desperate to get this sorted now
Thanx again
remove 'REMOVESPAM.' for email
Karl Rhodes
Internet Solutions Architect
The Learning Business & Keywave
www.thelearningbiz.com
www.keywave.com
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||from previous post:
"This means we have a different problem which is going to be a nightmare
to sort out.
If SQL Server (MSDE) has the TCPIP netlib enabled and the default port
is being used (1433), do we need to have 1433 enabled on both firewalls
(theirs and ours) to enable enterprise manager to register this server?
Also, Do we need to open port 1434 (Ive heard that this is needed for 2
way communication)"
You would need 1433 open on the remote firewall. You client would open up
a tcp session to 1433 and communicate back to your
client on a random tcp port.
Ex. client opens port 2000 and sends traffic to the remote MSDE on 1433
The communication comes back from 1433 to your client port 2000.
"Any other ideas why I can connect to our ISP hosted SQL Servers, but not
our clients (who hosts their own website) even though they have tried
with the server outside the firewall? Even If I make a vpn connection to
the SQL Server box, and can ping it by name, I still cant connect. I get
the error:
SQL Server does not exist or access is denied
ConnectionOpen(Connect())
"
This error is meaningless without the OS error number.
Force a TCP sockets connection with ISQL.exe
Ex. ISQL -Stcp:ServerName -Uuserid -Ppassword
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.sql

Best practices for accessing a sql 2005 db on the SAME box as IIS 6.0 serving asp.net 2.0

I am re-posting this from the security Forum where it remains un-answered.OK, Here's the set up.I have a Windows 2003 box, soon to have SSL installedOn it is IIS 6.0, SQL 2005 Standard Edition (5Cal user lic) SOON I'll have a prod enviornemnt where a web app being served by IIS is accessing SQL. I can go into SQL and set up a user account, call it MyAppSQLAcess, and code that into the connectionn string and lock it down to the tables/db it has access to. Or I can do it w/windows authentication, or I can do it a number of other ways, the question is this:What is the best way for an asp.net app being server by IIS 6.0 to access data from SQL 2005 server when they are all on the same BOX? WRT Speed and security? Thanks Dan

You really should take some time to read the information in the link below which is also in SQL 2005 Books Online. It'll help you in making informed decisions.

http://msdn2.microsoft.com/en-us/library/bb283235.aspx

Best of Luck!

|||

Thanks!
Nice link. Here is also another short concise article I found helpful

http://vyaskn.tripod.com/sql_server_security_best_practices.htm

Best practice using EM and Windows Authentification

Hi,
We have a team of IT people with mixed profiles.
If we use windows authentification we should be able to see all our
SQL Servers but with limited rights (readonly).
If we use a special admin windows account we should be able to have
full rights.
How can you accomplish this?
Allways logged in with my own user account and using EM and Query
Analyser for readonly stuff and use EM and Query Analyser from time to
time to change something BUT without logging off on your local machine
and logging on with your admin account.
Creating different MMC's, runas,...?
Can anybody point me in the right direction?
Thanks!!
Fred"Freddy" <fromheretoeternity@.hotmail.com> wrote in message
news:b9e50d08.0411230826.576b6cf7@.posting.google.com...

> We have a team of IT people with mixed profiles.
> If we use windows authentification we should be able to see all our
> SQL Servers but with limited rights (readonly).
> If we use a special admin windows account we should be able to have
> full rights.
> How can you accomplish this?
Define a new group that includes all of the accounts that should have full
rights and defined that group on SQL Server and grant system administrator
permissions to that group.

> Allways logged in with my own user account and using EM and Query
> Analyser for readonly stuff and use EM and Query Analyser from time to
> time to change something BUT without logging off on your local machine
> and logging on with your admin account.
> Creating different MMC's, runas,...?
> Can anybody point me in the right direction?
Create a short cut to the EM and QA MMC's on the desktops. Train your
admins, when performing administrative activity, on the appropriate MMC use
shift/right click, choose Run As... and enter the administrative credentials
to get full rights.
Steve

Best Practice Reboot Schedule

Is there a best practice reboot schedule for Windows 2000
with SQL Server 2000?
And if they are clustered is the schedule any different?
Previously I used a once a week reboot schedule for
Windows NT / SQL Server 7.You should only have to reboot if you install, say, a
patch that needs to have your server rebooted such as a
SQL Server service pack. And a cluster is the same.
Don't reboot unless you see a need ot.
>--Original Message--
>Is there a best practice reboot schedule for Windows 2000
>with SQL Server 2000?
>And if they are clustered is the schedule any different?
>Previously I used a once a week reboot schedule for
>Windows NT / SQL Server 7.
>.
>|||Darrell Deck wrote:
> Is there a best practice reboot schedule for Windows 2000
> with SQL Server 2000?
> And if they are clustered is the schedule any different?
> Previously I used a once a week reboot schedule for
> Windows NT / SQL Server 7.
You should not need to reboot to schedule on Windows 2000
(unlike NT). But in a clustered situation it is a good idea
to swap over now and then to check the other node is "valid".
We do this monthly at one site. Against weekly for the NT4 boxes.
Regards
AJ

Best Practice for Windows Authentication?

Hi,
We are changing our classic ASP web application to use Windows
Authentication instead of SQL Server Authentication.
I would like to know the best practice for:
1. IIS and SQL Server are on the same machine and
2.When they are on different machines in the same domain.
I *think* the solution to 1. is to add the IUSR_MACHINENAME user to SQL
Server (this works but is it the best practice?)
For 2. I have read different opinions. Some say create a IUSR_IISMACHINENAME
account on the SQL Server and make sure they have the same password. Other
say create a user on the domain and use that in IIS as the anonymous user
(and give that user the relevant rights on SQL Server)
I would like to know what is considered the best practice for this sort of
authentication.
Thanks in advancewhat version of IIS you running ?, using ASP.NET ?
http://msdn2.microsoft.com/en-us/library/bsz5788z
"Hugh Mungo" <hugh_mungo@.hotmail.com> wrote in message
news:%23igmiopuFHA.3500@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We are changing our classic ASP web application to use Windows
> Authentication instead of SQL Server Authentication.
> I would like to know the best practice for:
> 1. IIS and SQL Server are on the same machine and
> 2.When they are on different machines in the same domain.
> I *think* the solution to 1. is to add the IUSR_MACHINENAME user to SQL
> Server (this works but is it the best practice?)
> For 2. I have read different opinions. Some say create a
> IUSR_IISMACHINENAME
> account on the SQL Server and make sure they have the same password. Other
> say create a user on the domain and use that in IIS as the anonymous user
> (and give that user the relevant rights on SQL Server)
> I would like to know what is considered the best practice for this sort of
> authentication.
> Thanks in advance
>|||The solution should work with IIS5 and above.
We are not using ASP.NET this is a classic ASP application.
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:OcQFYgruFHA.2076@.TK2MSFTNGP14.phx.gbl...
> what version of IIS you running ?, using ASP.NET ?
> http://msdn2.microsoft.com/en-us/library/bsz5788z
> "Hugh Mungo" <hugh_mungo@.hotmail.com> wrote in message
> news:%23igmiopuFHA.3500@.TK2MSFTNGP09.phx.gbl...
Other[vbcol=seagreen]
user[vbcol=seagreen]
of[vbcol=seagreen]
>|||What a coincidence. Same here. I would definitely be interested to know
how to do this best practice also as this is the exact same thing that I'm
currently working on. One slightly different thing here is that we require
individual accounts (so we can track user activity with our sql profiler)
and would believe we would create a windows account on our domain controller
which resides on a different machine than our web (.asp files) and sql
server (also on separate machine) and was wondering if this would be
possible and how to go about doing this. Would it be as straight forward in
changing the connection string in our .asp files specifying windows
authentication? I am not too familiar in how to do this but was thinking of
maybe removing the anonymous account in IIS so that it would force the user
to login with the windows authentication pop up (in the possible situation
if users share a public machine and/or if the machine's operating system is
not windows with a valid corresponding domain windows account on our domain
controller?...which makes me wonder how this would be incorporated into our
connection string in our .asp files? Thanks in advance
"Hugh Mungo" <hugh_mungo@.hotmail.com> wrote in message
news:eJLVIyruFHA.2312@.TK2MSFTNGP14.phx.gbl...
> The solution should work with IIS5 and above.
> We are not using ASP.NET this is a classic ASP application.
> "David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
> news:OcQFYgruFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Other
> user
> of
>|||_-=?/today_-=?/354
"Hugh Mungo" wrote:

> Hi,
> We are changing our classic ASP web application to use Windows
> Authentication instead of SQL Server Authentication.
> I would like to know the best practice for:
> 1. IIS and SQL Server are on the same machine and
> 2.When they are on different machines in the same domain.
> I *think* the solution to 1. is to add the IUSR_MACHINENAME user to SQL
> Server (this works but is it the best practice?)
> For 2. I have read different opinions. Some say create a IUSR_IISMACHINENA
ME
> account on the SQL Server and make sure they have the same password. Other
> say create a user on the domain and use that in IIS as the anonymous user
> (and give that user the relevant rights on SQL Server)
> I would like to know what is considered the best practice for this sort of
> authentication.
> Thanks in advance
>
>

best practice for disk paritioning

Can anyone please tell me what the best practice is for SQL 2000 on a
WIndows 2003 server with regards to disk paritioning? My standard setup is
c:/d: on mirrored disk array (c is for OS, d for data) and all other data on
a SAN. What is the best way to configure sql?What other data is there except data<g>. A logical partition does nothing
for performance if it is on the same drive array as something you are trying
to keep it away from. While C: is great for the OS and SQL Binaries you are
best to place the data and logs on the SAN.
--
Andrew J. Kelly SQL MVP
"Bad Beagle" <maxwelli@.shaw.ca> wrote in message
news:%23IbisWnzEHA.260@.TK2MSFTNGP11.phx.gbl...
> Can anyone please tell me what the best practice is for SQL 2000 on a
> WIndows 2003 server with regards to disk paritioning? My standard setup
> is c:/d: on mirrored disk array (c is for OS, d for data) and all other
> data on a SAN. What is the best way to configure sql?
>

Best practice analyser tool installation

I installed a sql server best practice analyser tool on
our new windows 2003 server.
I log in as my own username which is a system
admininstrator.
After installation, I found the program is only available
by logging using my own username, if login using other
admin username or other username, I cannot see the program.
Even when I move the shortcut to all users, it gives
message:
This action is only available to currently installed
program
And during installation, it didn't ask to intall for all
users or current users.
How can I make the program available to all users?
Thanks
I think this should work. Try the following from the other (non-setup)
accounts.
regasm.exe $(O)\bpacommon.dll /tlb
regasm.exe $(O)\bpaclient.dll /tlb
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Best practise analyser" <anonymous@.discussions.microsoft.com> wrote in
message news:0a8b01c4903a$462f62b0$a401280a@.phx.gbl...
> I installed a sql server best practice analyser tool on
> our new windows 2003 server.
> I log in as my own username which is a system
> admininstrator.
> After installation, I found the program is only available
> by logging using my own username, if login using other
> admin username or other username, I cannot see the program.
> Even when I move the shortcut to all users, it gives
> message:
> This action is only available to currently installed
> program
> And during installation, it didn't ask to intall for all
> users or current users.
> How can I make the program available to all users?
> Thanks

Best method of doing Connection Strings

I am using SQL 2000 sp3a on Windows 2000 sp3. I have developed an Intranet application using asp.net/vb.net. Currently my connection string is:

data source=intraweb1;initial catalog=ASGWEB;password=blahblah;persist security info=True;user id=justauser;packet size=4096

So all my users are coming in with one SQL database id. Is this the best method for a combination of security and performance?

I do not allow anonymous to the website so I was thinking of setting up an application role and putting the domain users account in it. But from some other threads I was reading this does not work well with connection pooling.> Is this the best method for a combination of security and performance?

yeah, that's fine. I hardly ever do it otherwise - it's not fine-grained security-wise, but do you need it to be?

as for the connection pooling thing, yup - connection polling makes a pollfor the user id, so with multiple users you'd probably lose the beneficial effects, besides needing more CALs|||::besides needing more CALs

Using onedb server is does NOT save you CAL's. Read the licensing condition. You still need one CAL for every user. They say user - NOT user id. This is actually extremely clear, especially in the descriptions and comments.|||I had a discussion about this recently, and the concensus seemed to be one Device Access license for IIS to grab data if you're using one user ID. licencing is a nightmare though, and don't claim to be an expert on it by any means. I usually just ask MS whet the deal is and get multiple answers (!)

Best Damn Clustering Class...Period!

I had the opportunity to attend a Windows 2003 Clustering Class offered by
Rod (and Russ) in NYC. In my eleven years of IT, I have attended 8 or so
classes. This was the best class I have ever attended - period! The way
that Rod and Russ have designed this course, I wish MSL (Microsoft Learning)
would adopt that format. It just flowed perfectly. It also gave you the
choice of which order you wanted to do the labs in, without affecting the
desired outcomes. They have put a lot of work into making the one of the
best classes ever.
My only regret is that I could never deliver this courseware to students to
allow them to have the same experience I had since it is not for resale. You
can only take it with Rod or Russ, but that is so worth it. The only other
courseware to come close to this level of completeness was the 827 and 828
kits. I love those classes, too!
Anyway, if you need a vacation and want to take the best damn class you will
ever take, you must take Rod and Russ' class.
(Now let me tell you how much I really liked it!)
Jim Thyne - MCT
Thanks Jim,
It was a pleasure to have you in class. ClusterHelp.com thanks you!
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://www.msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Jim Thyne" <jthyne@.thlee.com> wrote in message
news:OD7kQBi6GHA.940@.TK2MSFTNGP03.phx.gbl...
>I had the opportunity to attend a Windows 2003 Clustering Class offered by
>Rod (and Russ) in NYC. In my eleven years of IT, I have attended 8 or so
>classes. This was the best class I have ever attended - period! The way
>that Rod and Russ have designed this course, I wish MSL (Microsoft
>Learning) would adopt that format. It just flowed perfectly. It also gave
>you the choice of which order you wanted to do the labs in, without
>affecting the desired outcomes. They have put a lot of work into making
>the one of the best classes ever.
> My only regret is that I could never deliver this courseware to students
> to allow them to have the same experience I had since it is not for
> resale. You can only take it with Rod or Russ, but that is so worth it.
> The only other courseware to come close to this level of completeness was
> the 827 and 828 kits. I love those classes, too!
> Anyway, if you need a vacation and want to take the best damn class you
> will ever take, you must take Rod and Russ' class.
> (Now let me tell you how much I really liked it!)
>
> Jim Thyne - MCT
>
|||Can't wait till you come to London :-)
Mark Fugatt
Premier Field Engineering
Microsoft
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:e8ypAzj6GHA.2384@.TK2MSFTNGP04.phx.gbl...
> Thanks Jim,
> It was a pleasure to have you in class. ClusterHelp.com thanks you!
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://www.msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Jim Thyne" <jthyne@.thlee.com> wrote in message
> news:OD7kQBi6GHA.940@.TK2MSFTNGP03.phx.gbl...
>
|||"Mark Fugatt [MSFT]" <markfu@.online.microsoft.com> wrote in message
news:eTjC5Qm6GHA.4404@.TK2MSFTNGP04.phx.gbl...
> Can't wait till you come to London :-)
> --
> Mark Fugatt
> Premier Field Engineering
> Microsoft
Tentative date is set of March 6-9th. Details to follow.
|||how about in Asia-Pacific Region?
"Russ Kaufmann (MVP)" <russ@.clusterhelp.com> wrote in message
news:OqWuxaq6GHA.1560@.TK2MSFTNGP04.phx.gbl...
> "Mark Fugatt [MSFT]" <markfu@.online.microsoft.com> wrote in message
> news:eTjC5Qm6GHA.4404@.TK2MSFTNGP04.phx.gbl...
> Tentative date is set of March 6-9th. Details to follow.
>
|||"Louie Lubangco" <llubangco@.iti-consulting.com> wrote in message
news:ex%23VgB36GHA.3760@.TK2MSFTNGP02.phx.gbl...
> how about in Asia-Pacific Region?
>
Sorry, but we have no plans at this time for the year 2006 or 2007.
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp
The next ClusterHelp classes are:
Denver starting Nov 14th
NYC starting Dec 4th.
|||Find at least 9 more people, and I will come to Japan or Singapore in a
heartbeat.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://www.msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Louie Lubangco" <llubangco@.iti-consulting.com> wrote in message
news:ex%23VgB36GHA.3760@.TK2MSFTNGP02.phx.gbl...
> how about in Asia-Pacific Region?
> "Russ Kaufmann (MVP)" <russ@.clusterhelp.com> wrote in message
> news:OqWuxaq6GHA.1560@.TK2MSFTNGP04.phx.gbl...
>
|||how about PF?
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:%23OeoZy86GHA.4996@.TK2MSFTNGP04.phx.gbl...
> Find at least 9 more people, and I will come to Japan or Singapore in a
> heartbeat.
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://www.msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Louie Lubangco" <llubangco@.iti-consulting.com> wrote in message
> news:ex%23VgB36GHA.3760@.TK2MSFTNGP02.phx.gbl...
>
|||MABUHAY Louie
Congratulations to ClusterHelp.com. - Rod and Russ. Maybe you can come up
with a business plan to have partners to deliver the training for you in Asia
Pacific ;) ... or franchise, maybe
MCP MCDBA MCAD MCSD MCT MCTS:SQL Server 2005
"Helping people grow and develop their full potential as God has plan for
them"
"Louie Lubangco" wrote:

> how about PF?
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:%23OeoZy86GHA.4996@.TK2MSFTNGP04.phx.gbl...
>
>
|||"bass_player" <bassplayer@.discussions.microsoft.com> wrote in message
news:B9DEB1FE-0E7D-4A76-A616-589C4BFB707D@.microsoft.com...
> MABUHAY Louie
> Congratulations to ClusterHelp.com. - Rod and Russ. Maybe you can come
> up
> with a business plan to have partners to deliver the training for you in
> Asia
> Pacific ;) ... or franchise, maybe
Actually, we are talking about that. Our UK class will be a Train the
Trainer class for anyone that wants to train in Europe using our materials
and meets our standards.
We would love to do the same thing in Sinapore for Asia-Pacific market.
However, we don't have any contacts there to arrange a class and market in
the area.
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp
The next ClusterHelp classes are:
Denver starting Nov 14th
NYC starting Dec 4th.

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

Best Config

I have a windows 2K server using SQL 2K. I am trying to
indentify what RAID configuation I should use for the
best performance. The application is an OLTP variety. I
have 5 physical disks. What would be the best set-up
(performance is more important than redundancy). If you
need any more info please ask. TIA.
MarcusAlthough the number of concurrent users is a potential issue.
Mirror the log, raid 10 the data ( but you don't have enough disk for that)
so
Mirror the log, raid 0 the data (stripe with stripe size of 64K)
opinions will vary... but avoiding raid 5, will give you a write improvement
( R5 has at least a 75% write penalty.)
But this will not give you data redundancy, but you can get up to the minute
recovery without loss ofdata...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and its
community of SQL Server professionals.
www.sqlpass.org
"Marcus" <marcus12@.hotmail.com> wrote in message
news:0d4a01c36268$9feebd10$a501280a@.phx.gbl...
> I have a windows 2K server using SQL 2K. I am trying to
> indentify what RAID configuation I should use for the
> best performance. The application is an OLTP variety. I
> have 5 physical disks. What would be the best set-up
> (performance is more important than redundancy). If you
> need any more info please ask. TIA.
> Marcus
>

benfit of 64bit windows os for sql 2000

I been reading quite a bit of documentation and am somewhat confused as to
whether or not running SQL 2000 on 64bit Win2k3 is truly beneficial. We
currently run SQL 2000 on Win2k with 8 Gb mem, PAE enabled and configured SQ
L
to use 6 GB via AWE. Performance needs to be better. We've tweaked some SQL
specific settings but have found that SQL needs more memory. We cannot run
64bit SQL due to our application not supporting it. I understand that SQL
2000 will run under WOW on Win2K3 but will this truely offer us any better
performance?The answer is, 'it depends.' Whether or not running SQL2000 SP4 on Windows
Server 2003 x64 edition will give you any performance benefit truly depends
on the characteristics of your workloads. You have to test your app to tell;
I don't know a better way than actually testing.
It's very hard to predict how your specific app will behave in this
configuration from whatever you may read in general whitepapers.
Linchi
"Brian" wrote:

> I been reading quite a bit of documentation and am somewhat confused as to
> whether or not running SQL 2000 on 64bit Win2k3 is truly beneficial. We
> currently run SQL 2000 on Win2k with 8 Gb mem, PAE enabled and configured
SQL
> to use 6 GB via AWE. Performance needs to be better. We've tweaked some SQ
L
> specific settings but have found that SQL needs more memory. We cannot run
> 64bit SQL due to our application not supporting it. I understand that SQL
> 2000 will run under WOW on Win2K3 but will this truely offer us any better
> performance?|||Thanks. I guess its time for some real world testing.
"Linchi Shea" wrote:
[vbcol=seagreen]
> The answer is, 'it depends.' Whether or not running SQL2000 SP4 on Windows
> Server 2003 x64 edition will give you any performance benefit truly depend
s
> on the characteristics of your workloads. You have to test your app to tel
l;
> I don't know a better way than actually testing.
> It's very hard to predict how your specific app will behave in this
> configuration from whatever you may read in general whitepapers.
> Linchi
> "Brian" wrote:
>

benfit of 64bit windows os for sql 2000

I been reading quite a bit of documentation and am somewhat confused as to
whether or not running SQL 2000 on 64bit Win2k3 is truly beneficial. We
currently run SQL 2000 on Win2k with 8 Gb mem, PAE enabled and configured SQL
to use 6 GB via AWE. Performance needs to be better. We've tweaked some SQL
specific settings but have found that SQL needs more memory. We cannot run
64bit SQL due to our application not supporting it. I understand that SQL
2000 will run under WOW on Win2K3 but will this truely offer us any better
performance?The answer is, 'it depends.' Whether or not running SQL2000 SP4 on Windows
Server 2003 x64 edition will give you any performance benefit truly depends
on the characteristics of your workloads. You have to test your app to tell;
I don't know a better way than actually testing.
It's very hard to predict how your specific app will behave in this
configuration from whatever you may read in general whitepapers.
Linchi
"Brian" wrote:
> I been reading quite a bit of documentation and am somewhat confused as to
> whether or not running SQL 2000 on 64bit Win2k3 is truly beneficial. We
> currently run SQL 2000 on Win2k with 8 Gb mem, PAE enabled and configured SQL
> to use 6 GB via AWE. Performance needs to be better. We've tweaked some SQL
> specific settings but have found that SQL needs more memory. We cannot run
> 64bit SQL due to our application not supporting it. I understand that SQL
> 2000 will run under WOW on Win2K3 but will this truely offer us any better
> performance?|||Thanks. I guess its time for some real world testing.
"Linchi Shea" wrote:
> The answer is, 'it depends.' Whether or not running SQL2000 SP4 on Windows
> Server 2003 x64 edition will give you any performance benefit truly depends
> on the characteristics of your workloads. You have to test your app to tell;
> I don't know a better way than actually testing.
> It's very hard to predict how your specific app will behave in this
> configuration from whatever you may read in general whitepapers.
> Linchi
> "Brian" wrote:
> > I been reading quite a bit of documentation and am somewhat confused as to
> > whether or not running SQL 2000 on 64bit Win2k3 is truly beneficial. We
> > currently run SQL 2000 on Win2k with 8 Gb mem, PAE enabled and configured SQL
> > to use 6 GB via AWE. Performance needs to be better. We've tweaked some SQL
> > specific settings but have found that SQL needs more memory. We cannot run
> > 64bit SQL due to our application not supporting it. I understand that SQL
> > 2000 will run under WOW on Win2K3 but will this truely offer us any better
> > performance?

benefits to use collations BIN vs windows

Hi
which are the real benefits to use collations BIN vs for example
Latin1_General_Ci_AS.
Or only it is for the order of the data, this always utilizing types of data
unicode.
ThankOn Tue, 9 Jan 2007 09:28:01 -0800, sqlextreme wrote:

>Hi
>which are the real benefits to use collations BIN vs for example
>Latin1_General_Ci_AS.
>Or only it is for the order of the data, this always utilizing types of dat
a
>unicode.
Hi sqlextreme,
The most important consideration should be how you generally want to
compare data. What do the users expect? Do they expect to find
"Washington" when entering "washington" as search string? Should
"Dsseldorf" be returned when searching "Dusseldorf"? Would they be
surprised to find "aaron" after "Zelazny" if the data is supposed to be
ordered?
The only advantage of using binary collation that I know of is that it's
(slightly) faster.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Behind the scenes SQL commands

Hello, I am running SQL 2000 SP4 on a Windows 2003 standard edition server.
The SQL database gets changed/updated through a web interface. I was
wondering if there was any way for me to see which SQL commands were being
run on the database when changed like deleting/inserting users are being
applied from the Web frontend?
Any help is appreciated, thanks.
RobertSure. Create a trace via Profiler. The text of stored procedures can be
viewed in Query Analyzer via sp_helptext if needed.
HTH
Jerry
"supersonic_oasis" <supersonicoasis@.discussions.microsoft.com> wrote in
message news:536DC58F-2216-4166-8D18-503CADB90B70@.microsoft.com...
> Hello, I am running SQL 2000 SP4 on a Windows 2003 standard edition
> server.
> The SQL database gets changed/updated through a web interface. I was
> wondering if there was any way for me to see which SQL commands were being
> run on the database when changed like deleting/inserting users are being
> applied from the Web frontend?
> Any help is appreciated, thanks.
> Robert
>

Behind the Scenes SQL commands

Hello, I am running SQL 2000 SP4 on a Windows 2003 standard edition server.

The SQL database gets changed/updated through a web interface. I was wondering if there was any way for me to see which SQL commands were being run on the database when changed like deleting/inserting users are being applied from the Web frontend?

Any help is appreciated, thanks.

Robert

Take a look in Books On Line at using Profiler, this is not available in MSDE, so if you are using MSDE then take a look for articles on uing SQL Trace.

Behind the scenes SQL commands

Hello, I am running SQL 2000 SP4 on a Windows 2003 standard edition server.
The SQL database gets changed/updated through a web interface. I was
wondering if there was any way for me to see which SQL commands were being
run on the database when changed like deleting/inserting users are being
applied from the Web frontend?
Any help is appreciated, thanks.
RobertSure. Create a trace via Profiler. The text of stored procedures can be
viewed in Query Analyzer via sp_helptext if needed.
HTH
Jerry
"supersonic_oasis" <supersonicoasis@.discussions.microsoft.com> wrote in
message news:536DC58F-2216-4166-8D18-503CADB90B70@.microsoft.com...
> Hello, I am running SQL 2000 SP4 on a Windows 2003 standard edition
> server.
> The SQL database gets changed/updated through a web interface. I was
> wondering if there was any way for me to see which SQL commands were being
> run on the database when changed like deleting/inserting users are being
> applied from the Web frontend?
> Any help is appreciated, thanks.
> Robert
>

before startup SQL Enterprise Manager

Hi,
Since some time, when I start SQL Enterprise Manager for the first time a
day, I get a message from Windows (not from a virus protection tool) that
says, 'an application is trying to access a protected item'.
In fact, I don't care for this, because the EM is allowed to do so. I can I
fix this?
Regards,
Egbert
I don't think that message is coming from SQL Server, but from some other
application running on your machine.
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
"Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.invalid> wrote in
message news:O18rllITEHA.2128@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Since some time, when I start SQL Enterprise Manager for the first time a
> day, I get a message from Windows (not from a virus protection tool) that
> says, 'an application is trying to access a protected item'.
> In fact, I don't care for this, because the EM is allowed to do so. I can
I
> fix this?
> Regards,
> Egbert
>
|||It's from Windows the protected storage service.
E.N.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:Oh%23HRtITEHA.644@.tk2msftngp13.phx.gbl...
> I don't think that message is coming from SQL Server, but from some other
> application running on your machine.
> --
> 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