PAE & AWE on x64 Windows & SQL

We've just setup some new Microsoft SQL 2005 servers running windows 2003 R2
x64 with 16GB of RAM. Since we are running a 64 bit version of windows and a
64 bit version of SQL, is it any longer neccesary to add the /3GB /PAE
option in the boot.ini and enable AWE in SQL?
Thanks!
Brad"Brad Baker" <brad@.nospam.nospam> wrote in message
news:OF%23WZo3xGHA.2384@.TK2MSFTNGP05.phx.gbl...
> We've just setup some new Microsoft SQL 2005 servers running windows 2003
> R2 x64 with 16GB of RAM. Since we are running a 64 bit version of windows
> and a 64 bit version of SQL, is it any longer neccesary to add the /3GB
> /PAE option in the boot.ini and enable AWE in SQL?
>
Slava says no /3GB, no /PAE, no AWE option. Just grant the SQL account the
"Lock Pages in Memory" privilege.
Q and A: Using Lock Pages In memory on 64 bit platform
Q: Hello Slava, I would like to confirm my understanding that on SQL 2005
64 bit edition it is recommended to grant Lock Pages in Memory right to the
SQL account and then turn on the AWE setting. Thanks
A: Yes, we do recommend to turn on Lock pages in memory so that OS doesn't
page SQL Server out. However on 64 bit you only need to grant the right
"Lock Pages in Memory" to the SQL account for SQL Server to utilize this
feature. You do need to to change any of AWE settings through sp_configure.
http://blogs.msdn.com/slavao/archive/2005/08/31/458545.aspx
Slava explains:
http://blogs.msdn.com/slavao/archive/2005/04/29/413425.aspx
David

PAE & AWE on x64 Windows & SQL

We've just setup some new Microsoft SQL 2005 servers running windows 2003 R2
x64 with 16GB of RAM. Since we are running a 64 bit version of windows and a
64 bit version of SQL, is it any longer neccesary to add the /3GB /PAE
option in the boot.ini and enable AWE in SQL?
Thanks!
Brad"Brad Baker" <brad@.nospam.nospam> wrote in message
news:OF%23WZo3xGHA.2384@.TK2MSFTNGP05.phx.gbl...
> We've just setup some new Microsoft SQL 2005 servers running windows 2003
> R2 x64 with 16GB of RAM. Since we are running a 64 bit version of windows
> and a 64 bit version of SQL, is it any longer neccesary to add the /3GB
> /PAE option in the boot.ini and enable AWE in SQL?
>
Slava says no /3GB, no /PAE, no AWE option. Just grant the SQL account the
"Lock Pages in Memory" privilege.
Q and A: Using Lock Pages In memory on 64 bit platform
Q: Hello Slava, I would like to confirm my understanding that on SQL 2005
64 bit edition it is recommended to grant Lock Pages in Memory right to the
SQL account and then turn on the AWE setting. Thanks
A: Yes, we do recommend to turn on Lock pages in memory so that OS doesn't
page SQL Server out. However on 64 bit you only need to grant the right
"Lock Pages in Memory" to the SQL account for SQL Server to utilize this
feature. You do need to to change any of AWE settings through sp_configure.
http://blogs.msdn.com/slavao/archiv.../31/458545.aspx
Slava explains:
http://blogs.msdn.com/slavao/archiv.../29/413425.aspx
David

packages and data source views do not work?

Hi,

doing my first steps in SSIS I wanted to copy data from 2 different SQL 2000 database servers to a SQL 2005 Data warehouse. For not having to deploy additional views and procedures to the individual systems I chose to create a Data Source View to create an abstract view on the different data sources. I found out that I can have named queries pointing to the two different data sources in the same view.

1 Project, 2 Data Sources, 1 Data Source View with 3 Named Queries

When I now add a Data Flow Task to the Control Flow how can I specify my DSV as Source for Transformations? I even added both OLEDB Connections to the Connection Manager but the Named Queries from my DSV do not appear at all. I even tried "SELECT from [myNamedQueryFromDSV]" but without success.

The description available at http://msdn2.microsoft.com/en-us/ms403395.aspx is bullshit. There is nothing to expand about the "Connection Manager" in the Data Flow Window. I can add a OLEDB Source as described in the above HOWTO and double click it. But the dropdown field for Connection Manager does offer only the two OLEDB Connections and nothing more. Among the items of the access mode "Tables and Views" the named queries not appear. It does not even work with a homogeneous Data Source View.

How can I make it work? Ain't there a better (working) HOWTO out there on how to enable DSV als Data Flow Task data sources? Do I have to wait for SP2 to solve the problem or is it not possible by intention?

Cheers,
Frank

You say you "added both OLEDB Connections to the Connection Manager." I am not sure exactly what you mean, but I wonder if you right-clicked in the Connection Managers tray and added an OLEDB connection. That would not help you use a Data Source View.

As the splendidly written and accurate article at http://msdn2.microsoft.com/en-us/ms403395.aspx describes through its links, you should in fact add a Connection from a Data Source: http://msdn2.microsoft.com/en-us/ms403395.aspx.

You can tell if you have a Connection Manager that enables access to Data Source Views by looking at the icon. If it looks like a Data Source icon, you're in business. If it looks like an OLEDB Connection Manager icon, then you will not be able to see any Data Source Views.

Donald

|||

Hi Donald,

thanks for replying. Your hint about the Icon that should be different led me to the right actions.

But regarding the quality and accurateness of the article I have to disagree.

Please look add the following quote taken directly from the article:
"After a data source and its data sources views are in the Integration Services project, you add must add a connection manager that references the data source to the package. When you add the connection manager based on a data source to a package, Integration Services adds an OLE DB type connection manager. Only sources, transformations, and destinations that can use an OLE DB connection manager can use data source views."

It tells that an OLE DB Type connection manager is added and that DSVs only work with OLE DB connection managers. There's no real usable hint, that I need to create some-kind-of-reference connection. From reading the article I thought I need to add the OLE DB connection that I used already for the DSV. "New Connection from Datasource" as an option is not quite clear in its meaning. Where's the difference to the normal OLEDB Connection? It looks to be linking to that OLE DB connection underneath anyway.

And one problem remains: As my DSV does have Named Queries pointing to both OLE DB datasource connections (2x datasource 1, 1x datasource 2) I only see the 2 first named queries using the connection manager but I never see the 3rd from the other datasource regardless if I also create a "New Connection from Datasource" from the second one and specify that one for the OLE DB Source. So heterogeneous DSVs are not supported?

Best regards, Frank

|||

The doc is indeed accurate, although maybe I should not overpraise its style. :-) The following extracts do seem very clear to me and even include a link to detailed info on how to add the data source.

... a connection manager that references the data source

... add the connection manager based on a data source

Before you can use a data source view in a package, you must add the data source, on which the data source view is built, to the package. For more information, see How to: Add a Data Source Reference to a Package.

However, the reader is always right, and if you did not find the docs helpful, then that is fair enough. The information you needed was there, but you could not find it. It would be great if you could provide some detail on how you think they could have been better worded, or more clearly structured, to help us help others.

Meanwhile, you are indeed correct that you can only use one (the default) data source in a data source view with SSIS.

Donald

|||

Hello once again,

Donald Farmer wrote:

Before you can use a data source view in a package, you must add the data source, on which the data source view is built, to the package. For more information, see How to: Add a Data Source Reference to a Package.

This is exactly one of the misleading statements. I shall add the data source on which the DSV was built. And for the DSV I have added two OLE DB Data Sources to the project. So my conclusion would be to reuse them. When I right-click the connection manager of the package I can choose between several types of connections to be added. And as the article also states that DSVs only work with OLE DB connections my first choice would be to select "New OLE DB Connection ...". Doing so pops up a dialog with the OLE DB Data Sources I have already defined in the project and used for the DSV.

Maybe this would be a better explanation:

Before you can use a data source view in a package, you must add a data source reference ("New Connection from Datasource") to the project data source object, on which the data source view is built, to the package. Your project data source object should be an OLE DB data source. For more information, ...

And for the future it would be nice if the SQL 2005 Development team could allow something like "New Connection to Data Source View" which would be (imho) very user friendly.

As for the restriction that only the Named Queries / Table Queries basing on the default data source in a DSV can be used in packages - a hint in the documentation would be very appreciated - and surely not only by me. Is that a restriction by intention or is it a bug going to solved in the future?

I highly appreciate your dealing with this topic

Kind regards, Frank

|||I agree that the documentation is not clear. I'm still struggling trying to get this setup.

P2P reindexing

I was looking at setting up multiple SQL Server 2005 Standard edition
servers using Peer-to-Peer Replication with the goal being able to
reindex one server at a time so as not to have any downtime (via locked
tables). Would this work?
Also, I know log-shipping to a standby server only requires 1 CPU
license which is attractive as CPU licenses are pricey. Still, if I
use P2P only for this reindex purpose (including three nodes for
voting) with only one server being used by customers/users, is a 1 CPU
license good enough?
Thanks!
MichaelI can't seem to find an answer anywhere for these questions:
Can I run ALTER INDEX REBUILD seperately between a live SQL 2005 server
and the hot-standby server? This way, if the live table is locked
during this operation, the hot-standby server would take over the CRUD
statements.
Is it allowed to have a single CPU license for two SQL 2005 server if
one is a hot-standby-only server and the standby-only server is only
used for failover purposes. Note that when tables are locked during
ALTER INDEX REBUILD, I assume the failover would kick in. Is that
possible?
The goal is to have no downtime for executing ALTER INDEX REBUILD.
Thank you,
Michael

p2p replication error too many arguments specified

Currently, we have peer-to-peer replication on two servers (server1 and
server2).
I added a few columns using sp_repladdcolumn stored procedure. Rows inserted
at Server1
are replicated to Server2 without problems, and rows inserted Server2 can
not be replicated to Server1
because of the error below. Any help is appreciated. thanks, D
Command attempted:
if @.@.trancount > 0 rollback tran
(Transaction sequence number: 0x0000008600000EE7000400000000, Command ID: 1)
Error messages:
Procedure or function sp_MSins_dboCustomers0823818373 has too many arguments
specified. (Source: MSSQLServer, Error number: 8144)
Get help: http://help/8144
Procedure or function sp_MSins_dboCustomers0823818373 has too many arguments
specified. (Source: MSSQLServer, Error number: 8144)
Get help: http://help/8144
Paul,
First time at this. Not sure how to quiesce the system. Does
this mean change db to single user?
I've used sp_repladdcolumn to add two columns to the publisher's article.
Both tables at subscriber and publisher do reflect the new columns.
However, stored procedures at the publisher do not receive the changes
while stored procedures at subscriber do.
When a new record is inserted into the table at publisher, the
new record does get distributed. When a new record is inserted
at the subscriber, the record got an error when being distributed.
Thanks, D
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OwyqlhJwHHA.4612@.TK2MSFTNGP04.phx.gbl...
> 1.. Quiesce the system.
> 2.. Execute the data definition language (DDL) statements to modify the
> schema of published tables. For more information about supported schema
> changes, see Making Schema Changes on Publication Databases.
> 3.. Before resuming activity on published tables, quiesce the system
> again. This ensures that schema changes have been received by all nodes
> before any new data changes are replicated
> The general advice is to do things as above - is that how you made the
> schema change?
> I'm also interested in what has actually happened - do the tables have an
> equal number of columns now? Can you also compare the text of the 2
> sp_MSins_dboCustomers procs (pub and subs) and see if they are different.
> If so, can you tell us which one is correct.
> Cheers,
> Paul Ibison
>

P2P reindexing

I was looking at setting up multiple SQL Server 2005 Standard edition
servers using Peer-to-Peer Replication with the goal being able to
reindex one server at a time so as not to have any downtime (via locked
tables). Would this work?
Also, I know log-shipping to a standby server only requires 1 CPU
license which is attractive as CPU licenses are pricey. Still, if I
use P2P only for this reindex purpose (including three nodes for
voting) with only one server being used by customers/users, is a 1 CPU
license good enough?
Thanks!
MichaelI can't seem to find an answer anywhere for these questions:
Can I run ALTER INDEX REBUILD seperately between a live SQL 2005 server
and the hot-standby server? This way, if the live table is locked
during this operation, the hot-standby server would take over the CRUD
statements.
Is it allowed to have a single CPU license for two SQL 2005 server if
one is a hot-standby-only server and the standby-only server is only
used for failover purposes. Note that when tables are locked during
ALTER INDEX REBUILD, I assume the failover would kick in. Is that
possible?
The goal is to have no downtime for executing ALTER INDEX REBUILD.
Thank you,
Michael

P&T

Can anyone recommend a good book or web-site to provide
me with more indepth information on performance & tuning
my sql servers as well as performance & tuning the
databases within my sql servers?
I have the SQL Server 2000 Performance Tuning book from
Microsoft Press but it does not give me near enough
detailed info on statistics, index information,
optimizations, etc.There's some good stuff at http://www.sql-server-performance.com
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Z" <anonymous@.discussions.microsoft.com> wrote in message
news:017001c3af8f$1591b2c0$a301280a@.phx.gbl...
Can anyone recommend a good book or web-site to provide
me with more indepth information on performance & tuning
my sql servers as well as performance & tuning the
databases within my sql servers?
I have the SQL Server 2000 Performance Tuning book from
Microsoft Press but it does not give me near enough
detailed info on statistics, index information,
optimizations, etc.

Ownership Chains and Linked Servers

I have 2 servers which are linked. All objects on server1 are owned by sa an
d
all objects on server2 are owned by sa. I have created a mapping between sa
on server1 and sa on server2. However the ownership chain seems to be broken
when I try and run a stored procedure from server1 which accesses objects on
server2 as a user other than sa.
Any ideas?Ownership chaining does not apply to queries across linked servers because
these are essentially different database connections.
Hope this helps.
Dan Guzman
SQL Server MVP
"Stevo" <Stevo@.discussions.microsoft.com> wrote in message
news:02DAAFDA-584E-4148-8B26-A752D6D08A7F@.microsoft.com...
>I have 2 servers which are linked. All objects on server1 are owned by sa
>and
> all objects on server2 are owned by sa. I have created a mapping between
> sa
> on server1 and sa on server2. However the ownership chain seems to be
> broken
> when I try and run a stored procedure from server1 which accesses objects
> on
> server2 as a user other than sa.
> Any ideas?|||Hmm. That's what I feared.
Does this mean that the ownership chain will always be broken across linked
servers and so I have to give a user server2 select permisssions so that a
stored procedure on server1 is able to select from server2?
"Dan Guzman" wrote:

> Ownership chaining does not apply to queries across linked servers because
> these are essentially different database connections.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Stevo" <Stevo@.discussions.microsoft.com> wrote in message
> news:02DAAFDA-584E-4148-8B26-A752D6D08A7F@.microsoft.com...
>
>|||> Does this mean that the ownership chain will always be broken across
> linked
> servers and so I have to give a user server2 select permisssions so that a
> stored procedure on server1 is able to select from server2?
Your understanding is correct that linked servers effectively break the
ownership chain. You'll need to grant permissions needed on server2 to the
account(s) used for the linked server connection. See sp_addlinkedsrvlogin
in the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Stevo" <Stevo@.discussions.microsoft.com> wrote in message
news:AAFC2AE4-08BF-486A-A712-1BE6072B8DB0@.microsoft.com...[vbcol=seagreen]
> Hmm. That's what I feared.
> Does this mean that the ownership chain will always be broken across
> linked
> servers and so I have to give a user server2 select permisssions so that a
> stored procedure on server1 is able to select from server2?
> "Dan Guzman" wrote:
>