Antivirus install to db server: windows

Showing posts with label windows. Show all posts

Friday, March 30, 2012

Question about sp_addscriptexec

Hi,
I'm running SQL Server 2000 EE SP3 on Windows Server 2003.
I have multiple named pull subscribers to a Transactional replication model.
I have written a script that adds and drops fields from several tables using
sp_repladdcolumn and sp_repldropcolumn. I also have some commands in my
script that are removing tables from the replication model. I also have some
commands that are adding new tables to the replication model.
After the commands that are adding and removing the fields and tables, I am
issuing a sp_refreshsubscriptions.
I am then starting the snapshot agent so that the subscribers will get the
new tables that I have added.
I then issue an sp_addscriptexec to run a script on the subscriber.
My question is why does this script that I am executing via the
sp_addscriptexec run on the subscriber before the snapshot ever gets applied
to the subscriber? I need to have the script run after the snapshot has been
created and delivered to the subscriber.
Thanks in advance,
Stephen
Probably because the sp_addscriptexec is added to the distribution database
before the snapshot is generated and the sync commands make it there, You
should perhaps use the post snapshot command for this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Stephen Schissler" <StephenSchissler@.discussions.microsoft.com> wrote in
message news:9CD0B3BB-6EE6-46DF-8CDE-C8B860890EE1@.microsoft.com...
> Hi,
> I'm running SQL Server 2000 EE SP3 on Windows Server 2003.
> I have multiple named pull subscribers to a Transactional replication
model.
> I have written a script that adds and drops fields from several tables
using
> sp_repladdcolumn and sp_repldropcolumn. I also have some commands in my
> script that are removing tables from the replication model. I also have
some
> commands that are adding new tables to the replication model.
> After the commands that are adding and removing the fields and tables, I
am
> issuing a sp_refreshsubscriptions.
> I am then starting the snapshot agent so that the subscribers will get the
> new tables that I have added.
> I then issue an sp_addscriptexec to run a script on the subscriber.
> My question is why does this script that I am executing via the
> sp_addscriptexec run on the subscriber before the snapshot ever gets
applied
> to the subscriber? I need to have the script run after the snapshot has
been
> created and delivered to the subscriber.
> Thanks in advance,
> Stephen
|||I already have a post snapshot command tied to my replication publication.
Since I am already replicating to the subscriber and just adding new tables
and fields, it seems as though the post snapshot command that was orginally
tied to the publication does not get run. I'm saying this as I already have
a post snapshot script applied to my publication and it is not getting run
when I regen the snapshot to create the definitions of the new tables.
So, I do not think that adding the additional script as a post script will
get run either, but in any case, I do not want to tie this script to the
publication definition as I do not want this script to be run whenever a
snapshot has been applied.
Thanks again,
Stephen
"Hilary Cotter" wrote:

> Probably because the sp_addscriptexec is added to the distribution database
> before the snapshot is generated and the sync commands make it there, You
> should perhaps use the post snapshot command for this.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Stephen Schissler" <StephenSchissler@.discussions.microsoft.com> wrote in
> message news:9CD0B3BB-6EE6-46DF-8CDE-C8B860890EE1@.microsoft.com...
> model.
> using
> some
> am
> applied
> been
>
>
|||You might want to set your distribution job to run scheduled as opposed to
continuous, stop the log reader agent, and then have the sp_addscriptexec
command run as your final job step. Stop and start your distribution agent.
After the distribution agent has stopped, remove this last step, start up
your log reader agent.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Stephen Schissler" <StephenSchissler@.discussions.microsoft.com> wrote in
message news:E7C9D59C-D82B-412E-9B5B-55DA93574C79@.microsoft.com...
> I already have a post snapshot command tied to my replication publication.
> Since I am already replicating to the subscriber and just adding new
tables
> and fields, it seems as though the post snapshot command that was
orginally
> tied to the publication does not get run. I'm saying this as I already
have[vbcol=seagreen]
> a post snapshot script applied to my publication and it is not getting run
> when I regen the snapshot to create the definitions of the new tables.
> So, I do not think that adding the additional script as a post script will
> get run either, but in any case, I do not want to tie this script to the
> publication definition as I do not want this script to be run whenever a
> snapshot has been applied.
> Thanks again,
> Stephen
> "Hilary Cotter" wrote:
database[vbcol=seagreen]
You[vbcol=seagreen]
in[vbcol=seagreen]
my[vbcol=seagreen]
have[vbcol=seagreen]
I[vbcol=seagreen]
the[vbcol=seagreen]
has[vbcol=seagreen]

Wednesday, March 28, 2012

question about removing logins at server level

Hi,
I created a login 'Network service' at server level in Management Studio
express.
I use windows authentification.
Then i defined an user for my database which is associated to login 'Network
service', because the application asp.net uses that account (IIS 6.0). This
user received db_read and db_write roles.
This works.
Now i experimented a little bit and i removed from the logins at server
level the login 'Network service'.
Result: the application still works.
.
Then i removed the Builtin\users login from the login list at server level.
Result: i get the error: "login failed for Network service".
I recreated then the login 'Network Service' at server level but not the
Builtin\users login.
Result: it works again.
My conclusion is: one of the two logins must be in the list: Network Service
or Builtin\users
Is this right?
Why do i get that error when both logins are removed and not only when
Network Service is removed?
Thanks
JeanBecause you're using Windows Authentication. From what you described it
doesn't sound like the connection to the db server ever came in using the
Network Service login, but rather came in using Builtin\users.
"Jean" wrote:
> Hi,
> I created a login 'Network service' at server level in Management Studio
> express.
> I use windows authentification.
> Then i defined an user for my database which is associated to login 'Network
> service', because the application asp.net uses that account (IIS 6.0). This
> user received db_read and db_write roles.
> This works.
> Now i experimented a little bit and i removed from the logins at server
> level the login 'Network service'.
> Result: the application still works.
> ..
> Then i removed the Builtin\users login from the login list at server level.
> Result: i get the error: "login failed for Network service".
> I recreated then the login 'Network Service' at server level but not the
> Builtin\users login.
> Result: it works again.
> My conclusion is: one of the two logins must be in the list: Network Service
> or Builtin\users
> Is this right?
> Why do i get that error when both logins are removed and not only when
> Network Service is removed?
> Thanks
> Jean
>
>|||Thanks, but after deleting the login Network Service, the user still is
mapped to Network Service account, no? If i delete that user, then the
application fails.
"Hurme" <michael.geles@.thomson.com> schreef in bericht
news:1F40DC8A-A258-4900-BD8B-1B312F151ED4@.microsoft.com...
> Because you're using Windows Authentication. From what you described it
> doesn't sound like the connection to the db server ever came in using the
> Network Service login, but rather came in using Builtin\users.
>
> "Jean" wrote:
>> Hi,
>> I created a login 'Network service' at server level in Management Studio
>> express.
>> I use windows authentification.
>> Then i defined an user for my database which is associated to login
>> 'Network
>> service', because the application asp.net uses that account (IIS 6.0).
>> This
>> user received db_read and db_write roles.
>> This works.
>> Now i experimented a little bit and i removed from the logins at server
>> level the login 'Network service'.
>> Result: the application still works.
>> ..
>> Then i removed the Builtin\users login from the login list at server
>> level.
>> Result: i get the error: "login failed for Network service".
>> I recreated then the login 'Network Service' at server level but not the
>> Builtin\users login.
>> Result: it works again.
>> My conclusion is: one of the two logins must be in the list: Network
>> Service
>> or Builtin\users
>> Is this right?
>> Why do i get that error when both logins are removed and not only when
>> Network Service is removed?
>> Thanks
>> Jean
>>|||I'm not sure about whether or not the Network Service id would still be
mapped or not, but I just did a test on one of my sql servers. I previously
did not have the BUILTIN\Users login and tried to login with a domain account
login that did not exist on the sql server nor the windows 2003 server. When
I tried to login access was denied. Once I created the BUILTIN\Users login on
hte sql server I was able to get in.
"Jean" wrote:
> Thanks, but after deleting the login Network Service, the user still is
> mapped to Network Service account, no? If i delete that user, then the
> application fails.
> "Hurme" <michael.geles@.thomson.com> schreef in bericht
> news:1F40DC8A-A258-4900-BD8B-1B312F151ED4@.microsoft.com...
> > Because you're using Windows Authentication. From what you described it
> > doesn't sound like the connection to the db server ever came in using the
> > Network Service login, but rather came in using Builtin\users.
> >
> >
> >
> > "Jean" wrote:
> >
> >> Hi,
> >>
> >> I created a login 'Network service' at server level in Management Studio
> >> express.
> >> I use windows authentification.
> >> Then i defined an user for my database which is associated to login
> >> 'Network
> >> service', because the application asp.net uses that account (IIS 6.0).
> >> This
> >> user received db_read and db_write roles.
> >>
> >> This works.
> >>
> >> Now i experimented a little bit and i removed from the logins at server
> >> level the login 'Network service'.
> >> Result: the application still works.
> >> ..
> >> Then i removed the Builtin\users login from the login list at server
> >> level.
> >> Result: i get the error: "login failed for Network service".
> >>
> >> I recreated then the login 'Network Service' at server level but not the
> >> Builtin\users login.
> >> Result: it works again.
> >>
> >> My conclusion is: one of the two logins must be in the list: Network
> >> Service
> >> or Builtin\users
> >>
> >> Is this right?
> >> Why do i get that error when both logins are removed and not only when
> >> Network Service is removed?
> >>
> >> Thanks
> >> Jean
> >>
> >>
> >>
>
>|||Ok, thanks
"Hurme" <michael.geles@.thomson.com> schreef in bericht
news:6D07EA51-00F3-4787-91A8-95C6BF5C2B4C@.microsoft.com...
> I'm not sure about whether or not the Network Service id would still be
> mapped or not, but I just did a test on one of my sql servers. I
> previously
> did not have the BUILTIN\Users login and tried to login with a domain
> account
> login that did not exist on the sql server nor the windows 2003 server.
> When
> I tried to login access was denied. Once I created the BUILTIN\Users login
> on
> hte sql server I was able to get in.
>
> "Jean" wrote:
>> Thanks, but after deleting the login Network Service, the user still is
>> mapped to Network Service account, no? If i delete that user, then the
>> application fails.
>> "Hurme" <michael.geles@.thomson.com> schreef in bericht
>> news:1F40DC8A-A258-4900-BD8B-1B312F151ED4@.microsoft.com...
>> > Because you're using Windows Authentication. From what you described
>> > it
>> > doesn't sound like the connection to the db server ever came in using
>> > the
>> > Network Service login, but rather came in using Builtin\users.
>> >
>> >
>> >
>> > "Jean" wrote:
>> >
>> >> Hi,
>> >>
>> >> I created a login 'Network service' at server level in Management
>> >> Studio
>> >> express.
>> >> I use windows authentification.
>> >> Then i defined an user for my database which is associated to login
>> >> 'Network
>> >> service', because the application asp.net uses that account (IIS 6.0).
>> >> This
>> >> user received db_read and db_write roles.
>> >>
>> >> This works.
>> >>
>> >> Now i experimented a little bit and i removed from the logins at
>> >> server
>> >> level the login 'Network service'.
>> >> Result: the application still works.
>> >> ..
>> >> Then i removed the Builtin\users login from the login list at server
>> >> level.
>> >> Result: i get the error: "login failed for Network service".
>> >>
>> >> I recreated then the login 'Network Service' at server level but not
>> >> the
>> >> Builtin\users login.
>> >> Result: it works again.
>> >>
>> >> My conclusion is: one of the two logins must be in the list: Network
>> >> Service
>> >> or Builtin\users
>> >>
>> >> Is this right?
>> >> Why do i get that error when both logins are removed and not only when
>> >> Network Service is removed?
>> >>
>> >> Thanks
>> >> Jean
>> >>
>> >>
>> >>
>>

Wednesday, March 21, 2012

question about launching an application at sqlserver startup

Hello,

What are the possible ways to make a GUI application start
automatically and run in a usual fashion (i.e. with a capability to
create windows) when mssqlserver starts?Hi

If you had a GUI where would you want it to display if there is noone logged
onto the server? Without a GUI you could write a service that is dependent
on the SQL Server service.

John

"Alexander Korovyev" <korovyev@.rambler.ru> wrote in message
news:26c82787.0407012306.fc5ace0@.posting.google.co m...
> Hello,
> What are the possible ways to make a GUI application start
> automatically and run in a usual fashion (i.e. with a capability to
> create windows) when mssqlserver starts?|||Alexander Korovyev (korovyev@.rambler.ru) writes:
> What are the possible ways to make a GUI application start
> automatically and run in a usual fashion (i.e. with a capability to
> create windows) when mssqlserver starts?

You can use a sp_procoption to mark a procedure as a startup procedure,
so that it is run each time SQL Server starts. From that procedure you
can call xp_cmdshell to run a command, but here is a catch: that must be
a command-line command, it cannot be a GUI, because SQL Server does not
have a desktop. However, you implement your application as another
service, but one that is permitted to interact with the desktop. (Exactly
how you would do this, I don't know, it's more of a Windows issue.)

Another alternative is to use the sp_OAxxx routines from your startup
procedure to interact with a COM object. A local COM object that uses
a GUI is not a good idea, but you could start a COM object on a remote
machine.

But I have question is: what is the real problem you are trying to solve?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

question about instances (what is an instance)

I have 1 SQL 2000 Standard Edition (SP3) server running on Windows 2000 SP4.
I have to install a new application which will reside on this sql server. Th
e
installation tells me to "Stop" all SQl instances.
My question is this: Is the instance the "MSSQLServer" service running on
this server?
If I understand "instances" right, then it is the O/S and the SQL Server
itself which makes up the instance. Does this mean I currently have 1
instance of SQL running?
Thanks for any help and info on this matter.Hi
IF you check out the topic "Working with Named and Multiple Instances of SQL
Server 2000" you will see you can have a default or a named instance of SQL
Server running on the same machine.
This default instance is identified by the network name of the computer on
which it is running. A named instance is identified by the network name of
the computer plus an instance name, in the format
<computername>\<instancename>.
For the service names a default instance will have the name MSSQLServer and
a named instance wll have a service name of MSSQL$InstanceName.
John
"carl" wrote:

> I have 1 SQL 2000 Standard Edition (SP3) server running on Windows 2000 SP
4.
> I have to install a new application which will reside on this sql server.
The
> installation tells me to "Stop" all SQl instances.
> My question is this: Is the instance the "MSSQLServer" service running on
> this server?
> If I understand "instances" right, then it is the O/S and the SQL Server
> itself which makes up the instance. Does this mean I currently have 1
> instance of SQL running?
> Thanks for any help and info on this matter.
>|||An instance is one installation of the SQL Server program files onto an O/S.
You can have up to 16 instances of SQL 2000 per machine (1 default and 15
named instances, or 16 named instances).
Each has its own section of the registry and file system. There may be some
shared files but I can't swear to it.
Each will use up its own specified amount of memory as well, so multiple
instances per box may or may not be a good idea, deoending on your hardware.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"carl" <carl@.discussions.microsoft.com> wrote in message
news:6F1D3CE3-305B-4430-928E-327D8037C05D@.microsoft.com...
>I have 1 SQL 2000 Standard Edition (SP3) server running on Windows 2000
>SP4.
> I have to install a new application which will reside on this sql server.
> The
> installation tells me to "Stop" all SQl instances.
> My question is this: Is the instance the "MSSQLServer" service running on
> this server?
> If I understand "instances" right, then it is the O/S and the SQL Server
> itself which makes up the instance. Does this mean I currently have 1
> instance of SQL running?
> Thanks for any help and info on this matter.
>

question about instances (what is an instance)

I have 1 SQL 2000 Standard Edition (SP3) server running on Windows 2000 SP4.
I have to install a new application which will reside on this sql server. The
installation tells me to "Stop" all SQl instances.
My question is this: Is the instance the "MSSQLServer" service running on
this server?
If I understand "instances" right, then it is the O/S and the SQL Server
itself which makes up the instance. Does this mean I currently have 1
instance of SQL running?
Thanks for any help and info on this matter.Hi
IF you check out the topic "Working with Named and Multiple Instances of SQL
Server 2000" you will see you can have a default or a named instance of SQL
Server running on the same machine.
This default instance is identified by the network name of the computer on
which it is running. A named instance is identified by the network name of
the computer plus an instance name, in the format
<computername>\<instancename>.
For the service names a default instance will have the name MSSQLServer and
a named instance wll have a service name of MSSQL$InstanceName.
John
"carl" wrote:
> I have 1 SQL 2000 Standard Edition (SP3) server running on Windows 2000 SP4.
> I have to install a new application which will reside on this sql server. The
> installation tells me to "Stop" all SQl instances.
> My question is this: Is the instance the "MSSQLServer" service running on
> this server?
> If I understand "instances" right, then it is the O/S and the SQL Server
> itself which makes up the instance. Does this mean I currently have 1
> instance of SQL running?
> Thanks for any help and info on this matter.
>|||An instance is one installation of the SQL Server program files onto an O/S.
You can have up to 16 instances of SQL 2000 per machine (1 default and 15
named instances, or 16 named instances).
Each has its own section of the registry and file system. There may be some
shared files but I can't swear to it.
Each will use up its own specified amount of memory as well, so multiple
instances per box may or may not be a good idea, deoending on your hardware.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"carl" <carl@.discussions.microsoft.com> wrote in message
news:6F1D3CE3-305B-4430-928E-327D8037C05D@.microsoft.com...
>I have 1 SQL 2000 Standard Edition (SP3) server running on Windows 2000
>SP4.
> I have to install a new application which will reside on this sql server.
> The
> installation tells me to "Stop" all SQl instances.
> My question is this: Is the instance the "MSSQLServer" service running on
> this server?
> If I understand "instances" right, then it is the O/S and the SQL Server
> itself which makes up the instance. Does this mean I currently have 1
> instance of SQL running?
> Thanks for any help and info on this matter.
>sql

question about instances (what is an instance)

I have 1 SQL 2000 Standard Edition (SP3) server running on Windows 2000 SP4.
I have to install a new application which will reside on this sql server. The
installation tells me to "Stop" all SQl instances.
My question is this: Is the instance the "MSSQLServer" service running on
this server?
If I understand "instances" right, then it is the O/S and the SQL Server
itself which makes up the instance. Does this mean I currently have 1
instance of SQL running?
Thanks for any help and info on this matter.
Hi
IF you check out the topic "Working with Named and Multiple Instances of SQL
Server 2000" you will see you can have a default or a named instance of SQL
Server running on the same machine.
This default instance is identified by the network name of the computer on
which it is running. A named instance is identified by the network name of
the computer plus an instance name, in the format
<computername>\<instancename>.
For the service names a default instance will have the name MSSQLServer and
a named instance wll have a service name of MSSQL$InstanceName.
John
"carl" wrote:

> I have 1 SQL 2000 Standard Edition (SP3) server running on Windows 2000 SP4.
> I have to install a new application which will reside on this sql server. The
> installation tells me to "Stop" all SQl instances.
> My question is this: Is the instance the "MSSQLServer" service running on
> this server?
> If I understand "instances" right, then it is the O/S and the SQL Server
> itself which makes up the instance. Does this mean I currently have 1
> instance of SQL running?
> Thanks for any help and info on this matter.
>
|||An instance is one installation of the SQL Server program files onto an O/S.
You can have up to 16 instances of SQL 2000 per machine (1 default and 15
named instances, or 16 named instances).
Each has its own section of the registry and file system. There may be some
shared files but I can't swear to it.
Each will use up its own specified amount of memory as well, so multiple
instances per box may or may not be a good idea, deoending on your hardware.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"carl" <carl@.discussions.microsoft.com> wrote in message
news:6F1D3CE3-305B-4430-928E-327D8037C05D@.microsoft.com...
>I have 1 SQL 2000 Standard Edition (SP3) server running on Windows 2000
>SP4.
> I have to install a new application which will reside on this sql server.
> The
> installation tells me to "Stop" all SQl instances.
> My question is this: Is the instance the "MSSQLServer" service running on
> this server?
> If I understand "instances" right, then it is the O/S and the SQL Server
> itself which makes up the instance. Does this mean I currently have 1
> instance of SQL running?
> Thanks for any help and info on this matter.
>

Monday, March 12, 2012

Question about Database Mirroring and Possibly NLB

OK since my company really would not prefer to spend the money for Windows 2003 Enterprise and MS SQL 2005 Enterprise I was thinking about this idea...

Server1 - W2K3 Std - SQL2005 Std - Primary
Server2 - W2K3 Std - SQL2005 Std - Mirror
Server3 - W2K3 Std - SQL2005 Std - Witness
Server4 - W2K3 Std - SQL2005 Std - Processing

OK So we have Server1 & Server2 doing the Database Mirroring. Then I have Server3 as the Witness to say who is the Primary and so forth. Now Server4 I thinking to us to create jobs that will run on a Schedule to pull data from our Ticketing system and import it into Server1 & Server2 (Yes this is a Datawarehouse).. So what I am wondering... We really wish to not have to change our connections to the DB with the Failover connection string and just use a common name. Since we are not usng Enterprise I can not do the Failover Clustering... So what I was wondering is if we could use the MS NLB. So for example with Server1 being primary and Server2 being Mirror... Server1 dies or goes down for maintance or whatever... Then Server3 will notice that and say OK Server2 you are now primary but also have Server3 go into the MS NLB and say Server3 Online and take all connections. Then when Server1 comes back on-line it is set as the Mirror system and is Offline in the NLB... Is this possible? If yes can someone either A... Give me directions on how to do that... or B shot me some URL's where I can read up on this stuff?

Thanks for all your help anyone is able to provide. It is greatly appricated.

Billy S.

1. You can build a 2 node cluster with the Standard edition

2. You don't need to deploy Standard edition for the witness, it can be Express Edition if you choose

3. Your imports would go to Server1. You can not write to a mirror.

4. Yes, you can combine this with NLB if you really want to.

|||

1. How do you setup a Cluster in Windows 2003 Server Standard? To my understanding it wont allow you to create a cluster on the Standard version... Can you give me a URL for that?

2. Yeah I understand that... but was noticing you cant really schedule any jobs via Express edition... Unless I am confused about that also.

3. Wouldn't my imports go to the Virtual name being which ever SQL Server is the Primary? Cause say Server1 dies and Server2 becomes primary... Wouldn't I want the updates to then to go Server2?

4. Do you happen to know where some documentation is on this concept?

Thanks again for all the help.

Billy S.

|||

What I believe Michael was referring to is SQL Server Standard Edition will allow you to create a 2 node cluster. I believe that you will still need to purchase Windows 2003 Server Enterprise Edition to cluster the two servers together at the OS level.

There are 3rd party products that allow you to do failover clustering similar to Microsoft Clustering that allow Windows 2003 Server Standard edition as the OS.

Hope this helps clarify things.

Drew

|||

What about on the Witness when it goes to Set a server to be the primary... can it also execute another command that will send a command to either a HW NLB or the MS NLB to set which ever server Online and the other server offline?

I would try and do it now but I need to wait some time first and get our VM server in before I can test anything... But manager is wanting me to say yes or no on this method and exactly what versions we will need to purchase for both the OS & SQL parts... So... Is there some place I can read about what the Witness can do when it does do a failover and set say Server2 to be primary and Server1 as mirror... like say run server2prim.bat... Just trying to get this figured out the best I can while they still have the purse open :)

|||

Actually, if you're operating in full sync mode (full safety), mirroring should do everything you need.

DBM can handle the redirection when there is a principal transition. You use a little different connection string which names both principal and partner. On connection, the app will automatically try the partner if the principal does not respond.

|||Yes... We can use the Failover Partner in the ODBC type connections... However some of the apps I design/manage/run use Perl and I do a direct connection not looking at ODBC to a single server... So... How would I do that without using the Failover peice in the connection string and not use the Microsoft Clustering Service? That is why I was looking at NLB...You have 2 machines in a NLB setup.. 1 system is set "Online" and the Secondary (Mirror Server) is set "Offline" now is there anyways on the Witness server to tell it when Primary server dies and it not available after you tell the Mirror server to become the primary run this command or batch job which will send a request to the NLB device or app saying Server1 is "Offline" and Server2 is "Online"... Does this make any sense? Or is what I am wanting to do just not doable period?|||

1. Windows Server 2003 STANDARD edition will do a 2 node cluster. That is very clearly documented in the feature set and there are tens of thousands of clusters out there running under Windows Server 2003 Standard edition. SQL Server 2005 Standard edition will also support a 2 node cluster.

2. If you are putting a load balancer in front of this, there is NOTHING that you need to do. The load balancer gives you a virtual IP for everything you put behind the LB. You hit the virtual IP exposed by the load balancer. The load balancer takes care of routing the traffic to the proper machine on the back end. Your applications are oblivious to any of this since they connect to the VIP and NOT the actual machines behind the load balancer. You don't point anything at all at the witness. The sole purpose of the witness is to enable automatic failover. How does traffic get re-routed by the load balancer? Exactly the same way that a load balancer routes all traffic. It detects that a resource is unavailable and then it automatically routes traffic to the resources that are available. By putting a witness in here, YOU are not telling the mirroring session to failover, the software does this automatically. If you don't put a witness in there to have automatic failover, then putting all of this behind a load balancer would be completely pointless in the first place.

3. You can NOT combine Windows Load Balancing and Clustering. They are incompatible.

|||

1) Michael, I know you as an extremely knowledgeable person, so I'm finding a hard time pointing out, that you seems to be off track. You cannot cluster on Windows Standard edition: http://www.microsoft.com/technet/windowsserver/evaluate/features/compare.mspx#64Clustering

Or at least, can you point me to a place in which Microsoft states that you can cluster on Windows 2003 Standard Edition?

Yours

Kim

|||

Michael,

I like the Idea of using NLB with 2005 Database mirroring since our DB's guy's will not have to rewrite all of their existing code, however I'm concerned about what would happen in the event of the SQL service stopping. I understand the witness will auto-magically failover the SQL resources but how do you instruct NLB to look for that same condition and redirect traffic accordingly?

Rob

|||That is done in the NLB configuration. You setup NLB with health checks that handle the traffic direction. Simply put, when the code in the NLB configuration can not connect to the SQL Server, it will direct connections to the alternate IP address that it is configured with. It behaves no differently than if you had a web server farm or app server farm behind NLB.

Friday, March 9, 2012

Question about Database Mirroring and Possibly NLB

OK since my company really would not prefer to spend the money for Windows 2003 Enterprise and MS SQL 2005 Enterprise I was thinking about this idea...

Server1 - W2K3 Std - SQL2005 Std - Primary
Server2 - W2K3 Std - SQL2005 Std - Mirror
Server3 - W2K3 Std - SQL2005 Std - Witness
Server4 - W2K3 Std - SQL2005 Std - Processing

Thanks for all your help anyone is able to provide. It is greatly appricated.

Billy S.

1. You can build a 2 node cluster with the Standard edition

2. You don't need to deploy Standard edition for the witness, it can be Express Edition if you choose

3. Your imports would go to Server1. You can not write to a mirror.

4. Yes, you can combine this with NLB if you really want to.

|||

1. How do you setup a Cluster in Windows 2003 Server Standard? To my understanding it wont allow you to create a cluster on the Standard version... Can you give me a URL for that?

2. Yeah I understand that... but was noticing you cant really schedule any jobs via Express edition... Unless I am confused about that also.

3. Wouldn't my imports go to the Virtual name being which ever SQL Server is the Primary? Cause say Server1 dies and Server2 becomes primary... Wouldn't I want the updates to then to go Server2?

4. Do you happen to know where some documentation is on this concept?

Thanks again for all the help.

Billy S.

|||

There are 3rd party products that allow you to do failover clustering similar to Microsoft Clustering that allow Windows 2003 Server Standard edition as the OS.

Hope this helps clarify things.

Drew

|||

Actually, if you're operating in full sync mode (full safety), mirroring should do everything you need.

3. You can NOT combine Windows Load Balancing and Clustering. They are incompatible.

|||

Or at least, can you point me to a place in which Microsoft states that you can cluster on Windows 2003 Standard Edition?

Yours

Kim

|||

Michael,

Rob

Question about Database Mirroring and Possibly NLB

OK since my company really would not prefer to spend the money for Windows 2003 Enterprise and MS SQL 2005 Enterprise I was thinking about this idea...

Server1 - W2K3 Std - SQL2005 Std - Primary
Server2 - W2K3 Std - SQL2005 Std - Mirror
Server3 - W2K3 Std - SQL2005 Std - Witness
Server4 - W2K3 Std - SQL2005 Std - Processing

Thanks for all your help anyone is able to provide. It is greatly appricated.

Billy S.

1. You can build a 2 node cluster with the Standard edition

2. You don't need to deploy Standard edition for the witness, it can be Express Edition if you choose

3. Your imports would go to Server1. You can not write to a mirror.

4. Yes, you can combine this with NLB if you really want to.

|||

1. How do you setup a Cluster in Windows 2003 Server Standard? To my understanding it wont allow you to create a cluster on the Standard version... Can you give me a URL for that?

2. Yeah I understand that... but was noticing you cant really schedule any jobs via Express edition... Unless I am confused about that also.

3. Wouldn't my imports go to the Virtual name being which ever SQL Server is the Primary? Cause say Server1 dies and Server2 becomes primary... Wouldn't I want the updates to then to go Server2?

4. Do you happen to know where some documentation is on this concept?

Thanks again for all the help.

Billy S.

|||

There are 3rd party products that allow you to do failover clustering similar to Microsoft Clustering that allow Windows 2003 Server Standard edition as the OS.

Hope this helps clarify things.

Drew

|||

Actually, if you're operating in full sync mode (full safety), mirroring should do everything you need.

3. You can NOT combine Windows Load Balancing and Clustering. They are incompatible.

|||

Or at least, can you point me to a place in which Microsoft states that you can cluster on Windows 2003 Standard Edition?

Yours

Kim

|||

Michael,

Rob

Wednesday, March 7, 2012

question about AWE and /PAE

I have a question about the /3gb /pae switch that I was wondering if
someone could shed some light on.
My understanding is that when windows has a machine with 4GB of memory
2GB is reserved as usermode and 2gb is reserved for kernal mode. (This
is without using the /3gb switch)
My server has 5GB of memory. AWE is enabled and max server memory and
min server memory is set to 4125. I am using the /PAE switch but NOT
the /3GB
based on this the max memory that the SQL Server process shoudl have
access to is 3GB
2GB - Kernel Mode
2GB - User Mode
1GB - Access via AWE
But when I run perfmon and look at Total Server Memory this value
is 4125. How is this possible without using the /3GB switch?
Shouldn't this value be only 3GB?
BertWhat you see is correct. AWE does not require PAE or /3GB or any special OS
edition to do it's business.
PAE only helps it to see mem above 4 GB when available.
KB 274750 is the most correct source, I wouldn't trust anything else, there
are plenty of misleading publications about AWE :-)
Best practices would be another topic, but I am not certain that you have
the best config.
5GB gives you only 1 GB benefit vs 4GB + life without PAE-AWE, but you're
getting all the PAE-AWE overhead and bugs for this extra gig.
I'd pump memory up to 8GB to fully justify PAE-AWE, or sacrificed 1GB and
use /3GB only on a 4GB system if that doesn't hurt page life expectancy.
Regards,
AD
"bert" <bertcord@.gmail.com> wrote in message
news:1110229420.102441.138830@.z14g2000cwz.googlegroups.com...
> I have a question about the /3gb /pae switch that I was wondering if
> someone could shed some light on.
> My understanding is that when windows has a machine with 4GB of memory
> 2GB is reserved as usermode and 2gb is reserved for kernal mode. (This
> is without using the /3gb switch)
> My server has 5GB of memory. AWE is enabled and max server memory and
> min server memory is set to 4125. I am using the /PAE switch but NOT
> the /3GB
> based on this the max memory that the SQL Server process shoudl have
> access to is 3GB
> 2GB - Kernel Mode
> 2GB - User Mode
> 1GB - Access via AWE
> But when I run perfmon and look at Total Server Memory this value
> is 4125. How is this possible without using the /3GB switch?
> Shouldn't this value be only 3GB?
> Bert
>|||Thanks for your reply Andrew; I am familiar with the article. However
I still don't understand the interaction of the /3GB switch and the
/PAE switch
I started looking into this more after Brian Moran's article in SQL
magazine this month. Based on Brain's article the KB you mentioned
and several other resources on the net I have come up with the
following conclusions.
1. AWE is used to access memory over 4GB
2. To enabled AWE you must specify the /PAE switch in the boot.ini
3. on a 32 bit OS 4GB of memory can be directly accessed
4. a default windows install on a machine with 4GB will allocated 2GB
to Usermode memory and 2GB to kernel mode memory
5. you can change the kernel mode \user mode allocation by using the
3GB switch in the boot.ini, this will change it to 3GB Usermode 1GB
Kernel mode
The problem is based on these conclusions I don't understand how my
server is using 4125MB of memory as it is not using the /3GB switch.
Can anyone help see anything wrong with my points above or can
explanation of how my server is using 4125MB of memory?
Bert|||/PAE is an OS level switch that allows it to access more than 4GB of memory
by fooling it into thinking it is a 36 bit vs. 32 bit address space. The
/3GB is an app level (any app not just sql server) switch that lets the app
use up to 3GB (instead of the default of 2GB) of directly addressable ram.
Meaning the first 4GB. The reason SQL Server is using about 4GB is due to
the AWE setting which basically fools the app into thinking it is 36 bit
addressing as well. But in your case you are only using 2GB of directly
addressable memory and the other 2GB is thru AWE. Certain parts (most
actually) in sql servers memory allocation can only use the first 2GB (or
3GB if /3GB is turned on). The other 2GB is only used for the data buffer
cache. The buffer cache can also use memory below 2GB but the other parts
can not use above it. Hope that clears it up.
--
Andrew J. Kelly SQL MVP
"bert" <bertcord@.gmail.com> wrote in message
news:1110295182.235072.44080@.l41g2000cwc.googlegroups.com...
> Thanks for your reply Andrew; I am familiar with the article. However
> I still don't understand the interaction of the /3GB switch and the
> /PAE switch
> I started looking into this more after Brian Moran's article in SQL
> magazine this month. Based on Brain's article the KB you mentioned
> and several other resources on the net I have come up with the
> following conclusions.
> 1. AWE is used to access memory over 4GB
> 2. To enabled AWE you must specify the /PAE switch in the boot.ini
> 3. on a 32 bit OS 4GB of memory can be directly accessed
> 4. a default windows install on a machine with 4GB will allocated 2GB
> to Usermode memory and 2GB to kernel mode memory
> 5. you can change the kernel mode \user mode allocation by using the
> 3GB switch in the boot.ini, this will change it to 3GB Usermode 1GB
> Kernel mode
> The problem is based on these conclusions I don't understand how my
> server is using 4125MB of memory as it is not using the /3GB switch.
> Can anyone help see anything wrong with my points above or can
> explanation of how my server is using 4125MB of memory?
> Bert
>|||This article might help.
Inside SQL Server 2000's Memory Management Facilities
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqldev/html/sqldev_01262004.asp
"bert" <bertcord@.gmail.com> wrote in message
news:1110229420.102441.138830@.z14g2000cwz.googlegroups.com...
>I have a question about the /3gb /pae switch that I was wondering if
> someone could shed some light on.
> My understanding is that when windows has a machine with 4GB of memory
> 2GB is reserved as usermode and 2gb is reserved for kernal mode. (This
> is without using the /3gb switch)
> My server has 5GB of memory. AWE is enabled and max server memory and
> min server memory is set to 4125. I am using the /PAE switch but NOT
> the /3GB
> based on this the max memory that the SQL Server process shoudl have
> access to is 3GB
> 2GB - Kernel Mode
> 2GB - User Mode
> 1GB - Access via AWE
> But when I run perfmon and look at Total Server Memory this value
> is 4125. How is this possible without using the /3GB switch?
> Shouldn't this value be only 3GB?
> Bert
>|||I did some more reading on AWE this evening and I am wondering what
the advantage of using /3GB when also using /PAE.
According to books-on-line
"The memory pool of an instance of SQL Server 2000 using AWE cannot
be swapped out to the page file"
So in my server example
2GB - Locked in memory, accessed using AWE.
2GB - Is directly addressed. This memory is also locked and can not
be paged to disk.
Now if I enabled /3GB in the boot.ini it would change to as follows
1GB - Locked in memory, accessed using AWE.
3GB - Is directly addressed. Memory is also locked and can not be
paged to disk.
The one thing I am not 100% on is if the user mode section of memory is
also locked. IS this true due to the fact that the SQL Server is using
AWE?
Thanks
Bert|||> 1. AWE is used to access memory over 4GB
It is rather over 2GB for an application.
> 2. To enabled AWE you must specify the /PAE switch in the boot.ini
Not true.
You probably have many links telling opposite :-)
> 3. on a 32 bit OS 4GB of memory can be directly accessed
true
> 4. a default windows install on a machine with 4GB will allocated 2GB
> to Usermode memory and 2GB to kernel mode memory
true
> 5. you can change the kernel mode \user mode allocation by using the
> 3GB switch in the boot.ini, this will change it to 3GB Usermode 1GB
> Kernel mode
true. That's for direct access.
AWE can be used instead for a "cheat" method if app supports it.
> The problem is based on these conclusions I don't understand how my
> server is using 4125MB of memory as it is not using the /3GB switch.
It is using AWE instead|||You never want any memory that sql server uses to be paged to disk. That is
the whole point of sql server having it's own caching mechanism. If you
turn on AWE you need to ensure that you set your max memory setting to an
amount less than the total amount of memory so the OS always has access to
some.
--
Andrew J. Kelly SQL MVP
"bert" <bertcord@.gmail.com> wrote in message
news:1110342099.812640.6560@.f14g2000cwb.googlegroups.com...
>I did some more reading on AWE this evening and I am wondering what
> the advantage of using /3GB when also using /PAE.
> According to books-on-line
> "The memory pool of an instance of SQL Server 2000 using AWE cannot
> be swapped out to the page file"
> So in my server example
> 2GB - Locked in memory, accessed using AWE.
> 2GB - Is directly addressed. This memory is also locked and can not
> be paged to disk.
> Now if I enabled /3GB in the boot.ini it would change to as follows
> 1GB - Locked in memory, accessed using AWE.
> 3GB - Is directly addressed. Memory is also locked and can not be
> paged to disk.
> The one thing I am not 100% on is if the user mode section of memory is
> also locked. IS this true due to the fact that the SQL Server is using
> AWE?
> Thanks
> Bert
>|||I appreciate everyone trying to help me...but... my question is still
not answered. Maybe If I ask a different question it will help.
Ok lets take my example
1. SQL Server Enterprise on windows 2000 Advanced server
2. Server has 5GB of memory
3. /PAE is enabled
4. NO /3GB is used
5. AWE is enabled in SQL server
6. max server memory is not set
According to books online
"If max server memory has not been set and there is at least 3 GB of
free memory available on the computer, then the instance acquires
almost all of the available memory and may leave only up to 128 MB of
memory free."
Also from books-on-line
"Windows 2000 may require at least 1 GB of available memory to manage
AWE"
ok so in this example the server takes all memory but 128MB...... So
what about the second statement above? Where is the kernel mode memory
that windows need to use to manage the AWE memory? The server only has
128MB left...so I am assuming that it is paged out to disk.
I want to make sure that enough memory is left for the OS to manage the
AWE memory. The kernel mode section of memory manages this so you
should make sure SQL leaves enough physical memory so that the kernel
memory is not paged out to disk.
Thanks again
Bert|||The statement that it may take up to 1GB to manage AWE is hypothetical. If
you have upwards of 16GB and are using AWE the OS may actually need all 2GB
to operate properly. You always have to set a MAX memory when using AWE
otherwise SQL Server will allocate and lock all but 128MB and leave very
little for the OS. I never recommend leaving less than 1GB to start for the
OS.
--
Andrew J. Kelly SQL MVP
"bert" <bertcord@.gmail.com> wrote in message
news:1110381394.812121.45160@.z14g2000cwz.googlegroups.com...
>I appreciate everyone trying to help me...but... my question is still
> not answered. Maybe If I ask a different question it will help.
> Ok lets take my example
> 1. SQL Server Enterprise on windows 2000 Advanced server
> 2. Server has 5GB of memory
> 3. /PAE is enabled
> 4. NO /3GB is used
> 5. AWE is enabled in SQL server
> 6. max server memory is not set
> According to books online
> "If max server memory has not been set and there is at least 3 GB of
> free memory available on the computer, then the instance acquires
> almost all of the available memory and may leave only up to 128 MB of
> memory free."
> Also from books-on-line
> "Windows 2000 may require at least 1 GB of available memory to manage
> AWE"
> ok so in this example the server takes all memory but 128MB...... So
> what about the second statement above? Where is the kernel mode memory
> that windows need to use to manage the AWE memory? The server only has
> 128MB left...so I am assuming that it is paged out to disk.
> I want to make sure that enough memory is left for the OS to manage the
> AWE memory. The kernel mode section of memory manages this so you
> should make sure SQL leaves enough physical memory so that the kernel
> memory is not paged out to disk.
> Thanks again
> Bert
>|||Correct...
So with my server that I have with the max memory setting of 4125 and
not using the /3GB switch. There is a good chance that kernel mode
memory is being paged in and out.
This is due to the fact that SQL has 4GB locked, I only have 5GB and
the kernel mode address range is 2GB.
Has anyone performed and types of performance tests analyzing the use
of the /3GB boot.ini switch?
Bert|||Just because it can use up to 2GB does not mean it needs to. It will only
page if it needs more than the ~1GB you have left over for the OS. If your
OS is using more than that you have to find out why.
--
Andrew J. Kelly SQL MVP
"bert" <bertcord@.gmail.com> wrote in message
news:1110384098.888228.33880@.g14g2000cwa.googlegroups.com...
> Correct...
> So with my server that I have with the max memory setting of 4125 and
> not using the /3GB switch. There is a good chance that kernel mode
> memory is being paged in and out.
> This is due to the fact that SQL has 4GB locked, I only have 5GB and
> the kernel mode address range is 2GB.
> Has anyone performed and types of performance tests analyzing the use
> of the /3GB boot.ini switch?
> Bert
>|||Yes I agree...but the point I bring up is one that I have not seen
mentioned before.
When using AWE all memory that SQL server is using is locked in memory.
This includes the Bpool that resides in UserMode memory and the AWE
memory above 2GB or 3GB.
This is important because you need to make sure the OS has enough
physical memory to use for Kernalmode.
According to this link http://linktrim.com/1od
"On a computer running a member of the Windows Server 2003 family,
the Page Table area, which contains the Page Table Entries (PTEs), is
64 megabytes (MB) by default when 4GT is enabled. The Paged Pool space
is approximately 256 MB."
So in this case you would want to have at least 320MB of free physical
memory available so these sections are not paged out to disk. IT gets
more complicated when PAE is enabled. More PTEs are required to mange
this memory, so you're settling for MAX memory in SQL server should
be reduced.
I haven't tested this...not sure how to... but based on the above
facts I Would think it would be best to do as follows
1. If you are using /3GB leave 1GB free of available physical memory
for kernel mode
2. If you are not using /3GB leave 2GB free of available physical
memory for kernel mode.
Bert|||Yes that is true but you stated you had 5GB and the max memory set to about
4GB. That should leave plenty for the OS.
--
Andrew J. Kelly SQL MVP
"bert" <bertcord@.gmail.com> wrote in message
news:1110558787.002507.326490@.l41g2000cwc.googlegroups.com...
> Yes I agree...but the point I bring up is one that I have not seen
> mentioned before.
> When using AWE all memory that SQL server is using is locked in memory.
> This includes the Bpool that resides in UserMode memory and the AWE
> memory above 2GB or 3GB.
> This is important because you need to make sure the OS has enough
> physical memory to use for Kernalmode.
> According to this link http://linktrim.com/1od
> "On a computer running a member of the Windows Server 2003 family,
> the Page Table area, which contains the Page Table Entries (PTEs), is
> 64 megabytes (MB) by default when 4GT is enabled. The Paged Pool space
> is approximately 256 MB."
> So in this case you would want to have at least 320MB of free physical
> memory available so these sections are not paged out to disk. IT gets
> more complicated when PAE is enabled. More PTEs are required to mange
> this memory, so you're settling for MAX memory in SQL server should
> be reduced.
> I haven't tested this...not sure how to... but based on the above
> facts I Would think it would be best to do as follows
> 1. If you are using /3GB leave 1GB free of available physical memory
> for kernel mode
> 2. If you are not using /3GB leave 2GB free of available physical
> memory for kernel mode.
> Bert
>|||Andrew,
Do you know how much more meory the OS needs for PTE's based on a given
size of AWE memory. For example if my server is using say 3GB of AWE
memory how many additioanl MB of PTEs are required to manage this AWE
memory. IF this was know it migt be easier to determine how much
memory to leave free for the OS.
For example
64MB - Default PTE
256MB- Defaul Psged Pool space.
XXXMB - Additional PTE sapce to manange AWE
XXXMB - minimum amount of meory to leave
Thanks
Bert|||To the best of my knowledge it is not a straight calculation. It seems to
depend on how you are actually using the memory and how aggressive the AWE
re-mappings are. I have seen systems running up to 16GB with /3GB with
absolutely no problems. And I have also seen ones that have troubles at
12GB if the /3GB is set. 3GB of AWE should not be an issue at all. Now
there may be some nutty case out there where this is not true but it will be
the exception not the rule. The OS should operate properly on 1GB and
manage 3GB of AWE pretty easily. If you are running other apps on that
server they may play a part in overall memory management though.
Andrew J. Kelly SQL MVP
"bert" <bertcord@.gmail.com> wrote in message
news:1111160197.266230.224330@.f14g2000cwb.googlegroups.com...
> Andrew,
> Do you know how much more meory the OS needs for PTE's based on a given
> size of AWE memory. For example if my server is using say 3GB of AWE
> memory how many additioanl MB of PTEs are required to manage this AWE
> memory. IF this was know it migt be easier to determine how much
> memory to leave free for the OS.
> For example
> 64MB - Default PTE
> 256MB- Defaul Psged Pool space.
> XXXMB - Additional PTE sapce to manange AWE
> XXXMB - minimum amount of meory to leave
> Thanks
> Bert
>

Question about authentiocation

Hi!

I have SQL Express runing and I'm using basic Authentication (Win 2k3 Web edi.).

But not all my Windows accounts show up in the list. Is this a bug or a setting? I want to add some users but I can't select them.

I'm newbies in microsoft SQL so if someone could help me.

SQL server requires you to add the users that will get permissions to use it. If you use basic authentication it will not use windows authentication but a proprietary way to create and authenticate users.

If you change it to use intergrated security you will need to add and define the permissions for windows users.

If you use intergrated security it usually is easier to define groups and give the group permission.

|||

First of all, thanks for replying.

I used windows authentication. Because I think that's more easy to manage.

But my colleague says they had problems with dbase connection. I don't know what they exactly did, so I have to figure that out, but they are using the SQL auth. at the moment.

I've made a new usergroup called 'SQL'. I added the user 'infostrada' to that usergroup.

By default, everbody is allowed to connect to a win 2003 machine? (policy: access the computer true the network).

So; when using a new user in a new usergroup, do I have to make certain changes inside Windows to let them connect?

í'm first going to check what they changed and try soms things out.

Thanks again.

Friday, March 30, 2012

Question about sp_addscriptexec

Wednesday, March 28, 2012

question about removing logins at server level

Wednesday, March 21, 2012

question about launching an application at sqlserver startup

question about instances (what is an instance)

question about instances (what is an instance)

question about instances (what is an instance)

Monday, March 12, 2012

Question about Database Mirroring and Possibly NLB

Friday, March 9, 2012

Question about Database Mirroring and Possibly NLB

Question about Database Mirroring and Possibly NLB

Wednesday, March 7, 2012

question about AWE and /PAE

question about AWE and /PAE

question about AWE and /PAE

Question about authentiocation

Antivirus install to db server

Blog Archive

About Me