Consolidate SQL servers to single server

We have 3 SQL servers we would like to consolidate onto a single
server. While proccessor usage is not of concern (per perfmon
monitoring and stats on the new hardware), memory usage is.
The Plan:
Migrate three SQL Server 2000 Standard servers to a Dell 6650 (4 way),
each in their own instance. The Server is running Windows 2003
Advanced server.
The Question/Problem:
While I've read about the PAE, AWE and 3/4/gb tunings, I'm still
confused on just what options we have. Each of these DB's use up
around 1 - 1.75GB of memory on a normal basis. They never exceed
1.75GB of usage so we haven't seen the need to go to SQL 2000
Enterprise. Obviously, though, 1.75GB + 1.75GB + 1.75GB !< 4GB of RAM
typically installed on a server. I've used the /3GB tuning on Windows
2000 Advanced, but that doesn't allow for enough memory in this
instance. What are my options to get each of these SQL instances 2GB
of RAM working within the confines of OS the SQL memory management?
Can I simply run Windows in PAE mode and install each copy of SQL in
their own instance to have full run of the 8GB to be installed in this
server? Does the /4gb tuning work with PAE?
KevinSo processor is ruled out. That's good.
I suggest you look at the chapter we put in the SQL 2K HA
book which explains how memory works in depth. To access
8 GB of memory under 32-bit, you need to put /PAE in
boot.ini. There is no other way. With /3GB, you can get
up to 3 GB of usable memory (different space tho), and 1
GB is always reserved for the OS. Up to 16 GB, both can
technically play together, and some applications need it.
beyond 16 GB on 32-bit machines, you cannot combine /3GB
and /PAE.
So if you have 8 GB and use /PAE and /3GB, you have 7 GB
of usable memory, of which only 3 GB would be dynamic (so
one instance could potentially be set to dynamic). When
using PAE, it is best to set the AWE settings in SQL as
well as set max mem for the instance. You seem to know
what that is.
So you can run all three instances with separate memory
using /PAE only, and if you can, I'd recommend that
approach.|||Thank you for your reply, though I am still a bit confused.
I understand that using /3GB and /PAE will give me 7GB of usable RAM.
However, you say "When using PAE, it is best to set the AWE settings
in SQL as well as set max mem for the instance. You seem to know what
that is.", and while this is what I have read elsewhere, I was under
the impression that AWE settings were not available on SQL 2000
Standard?
In the next paragraph you say "So you can run all three instances with
separate memory using /PAE only, and if you can, I'd recommend that
approach." I don't think I understand this sentence. Are you saying
I should run /PAE and not /3GB /PAE along with AWE settings in SQL?
Basically, I think I'm just looking to see if I can make SQL Standard
address memory above 4GB. I know you can with Enterprise using AWE, I
just thought AWE was not usable on Standard.
Thanks,
Kevin
"Allan Hirt" <anonymous@.discussions.microsoft.com> wrote in message news:<532201c47407$87fa08c0$a501280a@.phx.gbl>...
> So processor is ruled out. That's good.
> I suggest you look at the chapter we put in the SQL 2K HA
> book which explains how memory works in depth. To access
> 8 GB of memory under 32-bit, you need to put /PAE in
> boot.ini. There is no other way. With /3GB, you can get
> up to 3 GB of usable memory (different space tho), and 1
> GB is always reserved for the OS. Up to 16 GB, both can
> technically play together, and some applications need it.
> beyond 16 GB on 32-bit machines, you cannot combine /3GB
> and /PAE.
> So if you have 8 GB and use /PAE and /3GB, you have 7 GB
> of usable memory, of which only 3 GB would be dynamic (so
> one instance could potentially be set to dynamic). When
> using PAE, it is best to set the AWE settings in SQL as
> well as set max mem for the instance. You seem to know
> what that is.
> So you can run all three instances with separate memory
> using /PAE only, and if you can, I'd recommend that
> approach.|||PMJI, but I'm 99.9% sure that SQL Standard will only ever use 2GB RAM
maximum. Doesn't matter what OS you use or what switches you have set.
Mike Kruchten
"Kevin" <kjarrard@.gmail.com> wrote in message
news:4f5b3722.0407281043.471e0365@.posting.google.com...
> Thank you for your reply, though I am still a bit confused.
> I understand that using /3GB and /PAE will give me 7GB of usable RAM.
> However, you say "When using PAE, it is best to set the AWE settings
> in SQL as well as set max mem for the instance. You seem to know what
> that is.", and while this is what I have read elsewhere, I was under
> the impression that AWE settings were not available on SQL 2000
> Standard?
> In the next paragraph you say "So you can run all three instances with
> separate memory using /PAE only, and if you can, I'd recommend that
> approach." I don't think I understand this sentence. Are you saying
> I should run /PAE and not /3GB /PAE along with AWE settings in SQL?
> Basically, I think I'm just looking to see if I can make SQL Standard
> address memory above 4GB. I know you can with Enterprise using AWE, I
> just thought AWE was not usable on Standard.
> Thanks,
> Kevin
> "Allan Hirt" <anonymous@.discussions.microsoft.com> wrote in message
news:<532201c47407$87fa08c0$a501280a@.phx.gbl>...
> > So processor is ruled out. That's good.
> >
> > I suggest you look at the chapter we put in the SQL 2K HA
> > book which explains how memory works in depth. To access
> > 8 GB of memory under 32-bit, you need to put /PAE in
> > boot.ini. There is no other way. With /3GB, you can get
> > up to 3 GB of usable memory (different space tho), and 1
> > GB is always reserved for the OS. Up to 16 GB, both can
> > technically play together, and some applications need it.
> > beyond 16 GB on 32-bit machines, you cannot combine /3GB
> > and /PAE.
> >
> > So if you have 8 GB and use /PAE and /3GB, you have 7 GB
> > of usable memory, of which only 3 GB would be dynamic (so
> > one instance could potentially be set to dynamic). When
> > using PAE, it is best to set the AWE settings in SQL as
> > well as set max mem for the instance. You seem to know
> > what that is.
> >
> > So you can run all three instances with separate memory
> > using /PAE only, and if you can, I'd recommend that
> > approach.

Considerations when delete records from table.

Hello, I'm developing application which monitors network packets. The monitoring data are saved into table. Monitoring table maintains the data for fixed quantum time,for example during one 1 hour. So, every minute before or after insert new data, I delete the time-expired data. I doubt that the endless delete operation would results in some problems(increasing index,etc..).

Is this mechanism safe to the dbms?

Aren't there round-robin(?) style table?

thats wat OLTP is for.... just keep the update statistics to auto (its default) , if ur using indexes.....also there may be fragmentation issues ...but thats a DBA activity/Db maintainance...

Connection problems from VB

When monitoring errors concerning connection and updating data on my MS SQL
Server2000 from a VB application I get these errors.
-2147467259 [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite
(send()).
-2147467259 [Microsoft][ODBC SQL Server Driver]Connection error
-2147467259 [Microsoft][ODBC SQL Server Driver][SQL
Server]SqlDumpExceptionHandler: Process 79 generated fatal exception
c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
-2147467259 [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite
(send()).
The application has been communicating with an MS SQL Server2000 for 5-6
years with no problems of this kind. The problem started when I installed my
application to run against a new MS SQL Server2000 at a new company, and I
am wondering if it could have something to do with how the server is
configured.
I am not sure why the err.description ref. to ODBC SQL Server Driver because
as shown below I use OLEDB connection object.
Any Ideas on how to manage this problem?
Below you can see example of my functions in VB:
Private Sub cmdSelect_Click()
funConnect
set rstMyRecordset = rstExecData(mySelectSQL)
funDisconnect
End Sub
this procedure works just fine
this next code works fine as well, but if I try to run a select as above
after the update if fails with the error in function rstExecData
Private Sub cmdUpdate_Click()
funConnect
rstExecData(myUpdateSQL)
funDisconnect
End Sub
Functions used:
Function funConnect() As Boolean
On Error GoTo errHandler
strCnn =
" driver={SQLServer};server=DC01;uid=sa;pw
d=mysapassword;database=mydatabase"
Set cnn = New ADODB.Connection
cnn.CursorLocation = adUseClient
cnn.Open strCnn
Exit Function
errHandler:
funConnect = False
End Function
Function funDisconnect() As Boolean
On Error GoTo errHandler
If cnn.State = 1 Then
Set cnn = Nothing
Else
Exit Function
End If
errHandler:
funDisconnect = False
End Function
Public Function rstExecData(strSQL As String) As ADODB.Recordset
Set rstExecData = New ADODB.Recordset
rstExecData.CursorType = adOpenDynamic
rstExecData.LockType = adLockOptimistic
rstExecData.Open Trim(strSQL), cnn
End Function
TIRislaaWhat about following line:
strCnn =
" driver={SQLServer};server=DC01;uid=sa;pw
d=mysapassword;database=mydatabase"
This is call to OLEDB for ODBC Provider and ODBC Driver.
Why not OLEDB Driver for SQL Server?
strCnn = "Provider=SQLOLEDB.1;Data Source=DC01;Password=mysapassword;User
ID=sa;Initial Catalog=mydatabase;"
Regards
Jacek
"Tor Inge Rislaa" wrote:

> When monitoring errors concerning connection and updating data on my MS SQ
L
> Server2000 from a VB application I get these errors.
>
> -2147467259 [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite
> (send()).
> -2147467259 [Microsoft][ODBC SQL Server Driver]Connection error
> -2147467259 [Microsoft][ODBC SQL Server Driver][SQL
> Server]SqlDumpExceptionHandler: Process 79 generated fatal exception
> c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this proces
s.
>
> -2147467259 [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite
> (send()).
>
> The application has been communicating with an MS SQL Server2000 for 5-6
> years with no problems of this kind. The problem started when I installed
my
> application to run against a new MS SQL Server2000 at a new company, and I
> am wondering if it could have something to do with how the server is
> configured.
>
> I am not sure why the err.description ref. to ODBC SQL Server Driver becau
se
> as shown below I use OLEDB connection object.
>
> Any Ideas on how to manage this problem?
>
>
> Below you can see example of my functions in VB:
>
> Private Sub cmdSelect_Click()
> funConnect
> set rstMyRecordset = rstExecData(mySelectSQL)
> funDisconnect
> End Sub
> this procedure works just fine
> this next code works fine as well, but if I try to run a select as above
> after the update if fails with the error in function rstExecData
> Private Sub cmdUpdate_Click()
> funConnect
> rstExecData(myUpdateSQL)
> funDisconnect
> End Sub
> Functions used:
>
> Function funConnect() As Boolean
> On Error GoTo errHandler
> strCnn =
> " driver={SQLServer};server=DC01;uid=sa;pw
d=mysapassword;database=mydatabas
e"
> Set cnn = New ADODB.Connection
> cnn.CursorLocation = adUseClient
> cnn.Open strCnn
> Exit Function
> errHandler:
> funConnect = False
> End Function
>
> Function funDisconnect() As Boolean
> On Error GoTo errHandler
> If cnn.State = 1 Then
> Set cnn = Nothing
> Else
> Exit Function
> End If
>
> errHandler:
> funDisconnect = False
> End Function
> Public Function rstExecData(strSQL As String) As ADODB.Recordset
> Set rstExecData = New ADODB.Recordset
> rstExecData.CursorType = adOpenDynamic
> rstExecData.LockType = adLockOptimistic
> rstExecData.Open Trim(strSQL), cnn
> End Function
> TIRislaa
>
>
>|||This connectionstring works OK for select statements, but if I try to update
data, the application hangs totally (don't answer)
Is it possible that there are settings on the SQL server that blocks update
and insert statements?
TIRislaa
"JacekZ" <JacekZ@.discussions.microsoft.com> skrev i melding
news:0B026699-EDBA-4B7E-8B1F-BFCED79AA73E@.microsoft.com...
> What about following line:
> strCnn =
> " driver={SQLServer};server=DC01;uid=sa;pw
d=mysapassword;database=mydatabas
e"
> This is call to OLEDB for ODBC Provider and ODBC Driver.
> Why not OLEDB Driver for SQL Server?
> strCnn = "Provider=SQLOLEDB.1;Data Source=DC01;Password=mysapassword;User
> ID=sa;Initial Catalog=mydatabase;"
>
> Regards
> Jacek
> "Tor Inge Rislaa" wrote:
>