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

Package setup, configurations and testing

Hi,

I'm looing for advice on how to organize my SSIS packages. I'll quickly describe my requiremenst, and then outline how I see the solution.

1. Should work for development in BI Studio. Design time experience should be good, so static schema information should be available in all steps. Packages should run in debug mode inside BI Studio.

2. Should be able to run automated tests (which I gather is difficult. See proposed solution below). Tests should run both on a test machine, in batch mode, and from BI Studio.

3. Should work for deployment.

The packages should be fine-grained. Automated tests will be implemented as SSIS packages that call the packages to be tested via an Execute Package Task. There will be one test package per testcase. A test will look like: [Create Schema] -> [Load test data]->[Execute Package to be tested]->[Verify expected results]->[Teardown].

There will be one master test package that executes all the tests.

For this I need a) some way to vary the connections for the packages depending on whether they are run from a testcase or from the normal control flow. I'm considering making the connection strings configurations based on a parent package variable. Other configuration types strike me as not suitable since developers will want to run packages both as part of normal flow and as testcases on their machines. Is this a viable strategy? Any drawbacks?

b) need to organize testcases some way. I tried putting tests in a subfolder of the project to minimize the clutter, but BI studio shows them at the root node. I tried putting a new project (for containing the tests) in the same folder, and adding the packages (via add existing item), but BI Studio insists of adding copies (!) of the package files, rather than reusing the existing files. Any suggestions?

I welcome any feedback, suggestions of alternate strategies, etc.

/Anders

AndersI wrote:

For this I need a) some way to vary the connections for the packages depending on whether they are run from a testcase or from the normal control flow. I'm considering making the connection strings configurations based on a parent package variable. Other configuration types strike me as not suitable since developers will want to run packages both as part of normal flow and as testcases on their machines. Is this a viable strategy? Any drawbacks?

One possible drawback is that when the developers run in BIDS (not the test case), the packages will use the connection strings coded in the packages. I like using configurations for my packages even in development.

Another approach might be to use another type of configuration for the packages in dev, but when you run them from the test pacakges, use Execute Process instead of Execute Package. You can call DTEXEC from Execute Process, and override the connection strings by using the /CONN switch, which should take precedence over the configurations.

AndersI wrote:

b) need to organize testcases some way. I tried putting tests in a subfolder of the project to minimize the clutter, but BI studio shows them at the root node. I tried putting a new project (for containing the tests) in the same folder, and adding the packages (via add existing item), but BI Studio insists of adding copies (!) of the package files, rather than reusing the existing files. Any suggestions?

Can't you just put the test case packages in one project, and leave the real ones in another project?

|||

Thanks!

I'm using configurations for the master packages to set the connection variables, and then passing them down to child packages via parent package variable configurations.

The reason I want the packages-to-be-tested in the same project as the testcase packages is so that you can run them in the BI studio debugger. If the package called via Execute Package is not in the same project, then the debugger won't display it. [Edit] Scratch that, it works. Must have been a user error on my part... [/Edit]

The Execute Package task uses a connection with an absolute path to refer to the called package, right? What is the recommended practice for handling this in a development team where developers may have the files checked out to different locations on their machines? I'm considering adding a "PackagePath" variable (also stored in the configuration file) to the master packages, and using expressions on the form @.[User:ackagePath] + "ChildPackage.dtsx" for the connectionstring on the file connection manager. Has anyone else tried this?

/Anders

|||

AndersI wrote:

The Execute Package task uses a connection with an absolute path to refer to the called package, right? What is the recommended practice for handling this in a development team where developers may have the files checked out to different locations on their machines? I'm considering adding a "PackagePath" variable (also stored in the configuration file) to the master packages, and using expressions on the form @.[User:ackagePath] + "ChildPackage.dtsx" for the connectionstring on the file connection manager. Has anyone else tried this?

Yes, I've done exactly that. It works well.

P

Hi Experts,

After performing some tests on my setup I recorded very large performance degradation between simple setup and mirrored setup (High Availability �C with witness). In some inserts tests (100K in a loop) it took up to 400% longer to complete for mirror setup compared to the simple one (40 seconds compared to 170 seconds).Is this performance hit normal? Is there something that can be done to make it perform better?

The PCs are connected using a 100MB switch and I'm using SQL Server Developer Edition.

Thanks,

Avi

Hi Avi,

The tests that you are running are bad scenario for high-availability mirroring. Each insert statement in the loop practically commits a single-statement transaction. This causes log to be sent to disk and mirror and waits for the mirror to harden that log. That way you are directly adding to the per-statement latency.

Here are a few things you can try:

- Don't run all inserts sequentially on a single connection. Rather, distribute them among multiple connections. That way you are increasing the chance that while one transaction is committing and thus waiting for the mirror to harden the log, others may run in parallel, thus amortising the wait time.

- If at all possible, make your transactions bigger (do several inserts in a single transaction). That way each log flush will carry several transactions.

- If at all possible, use high-performance mirroring mode (here you have the risk of having your log growing, though).

Hope this helps.

Thanks,

Kaloian.

|||

For better estimation of performance impact of database mirroring, your tests should be as close to the actual application as possible. For discussion on database mirroring performance, take a look at the paper: http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx

|||

PCs?

Would you please give more details on the hardware of the mirrored servers?

|||

Thank you all for the replys.

I use two dual Xeon PCs with 2GB memory. The disks are fast too. The performance without the mirroring is great - over 2000 inserts per second. However with mirroring it goes down sharply.

When I run the inserts in a single transaction it flies. However I cannot always do that. My workaround for now is to suspend the mirroring for a few seconds do my inserts and run the resume command. This brings me back to the performance I used to have, but I hope to find a more "clean solution". It is not so good to suspend and resume the mirroring so many times��

Any suggestions (hardware, configuration or application implementation)

Thanks,

Avi

|||

Avi,

What you are doing (suspend/insert/resume) is essentially similar to high-performance mirroring mode with possibly more overhead, because of the resynchronisation.

If you can tolerate possible data loss on failure, then SAFETY = OFF is the better choice. Does that work for you? If not can you give some more details on what you want to accomplish.

Thanks,

Kaloian.

owner problem...

i have transactional repl setup with an updatable subscriber. when my
initial snapshot is synced to the subscriber, the tables are dropped fine
but recreated under a non-dbo user (in my case one that is setup named
'dev'). what controls how the publisher/distributor drops/creates the tables
at the subscriber end? this must be where the 'dev' owner is coming from.
thanks.
Terry,
in terms of TSQL this is the @.destination_owner value from sp_addarticle. I
don't have the GUI in front of me right now, but take a look at the article
properties option and there should be an entry to specify this in the list
of properties there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

OWC and AS 2005 Cube Drillthrough action

Hi There,

I setup drillthrough action on AdventureWorks sample cube, and am able
to view drillthrough through cube browser. However when I use OWC
(office web component) 11 (office 2003) to connect to the cube, I can
still open up drillthrough in the IE with all the dimension name, but
without any data.

Does OWC 11 support AS 2005 Drillthrough, we use OWC 11 on AS 2000 cube
drillthrough and work fine?

Does anyone get drillthrough working on AS 2005 cube in OWC 11?

Thanks
Dong

Hi Dong,

I haven't use plane OWC for a while and can't try this scenario right now. But if you still have a problem, please run SQL Profiler when issuing a drillthrough command and action (Discover request MDSCHEMA_ACTIONS) from OWC and Browser and see for any noticable differentces between the two. You can also post those commands in this post, may be we can find the problem together.

Overcoming MSDE Setup Problems

Here is my experience with installing MSDE.

I'm leaning C# and ASP.NET at the same time using the book "Microsoft ASP.NET Programming with Microsoft Visual C# .NET Step by Step". In Appendix C, P 577, it says to locate setup.exe under ��Microsoft Visual Studio .NET\Setup\MSDE and "run setup.exe to install MSDE."

This was my first mistake, because there are more recent versions available with security patches (for the slammer virus). I have been unable to successfully update the old version. The install program for MSDE has got to be a hackers delight because only a hacker can make it work. I decided to uninstall the old version of MSDE 2000 and install the latest download from the MSDN website. The new version would get most of the files installed and then rollback the install, removing all the files. When I tried to reinstall the old version, it did the same thing. At least the new version requires a password parameter for the sa user. The bad news is that the new password is ignored. Since I already installed the first version with a blank sa password by running setup.exe with no parameters the password will remain blank until I change it using the user friendly osql command line utility. More about osql later��

Here is how I solved the rollback problem:

I first (after many hours trying to modify the registry to clean up the mess Windows uninstaller left there) used the command line switch /L*V <your logfile name> to get a verbose log file. I noticed a property listed called "Disablerollback" this was set to 0. So, I ran setup on the command line as below (all one line):

>setup DisableRollback=1 /L*V "C:\Program Files\Microsoft Visual Studio .NET\Setup\MSDE\setupbat.log" SAPWD="ignored password"

The setup still failed, but its tracks were preserved for XP to complain about on system restart.
I then restarted the system and logged in. XP threw an error dialog box with the message "Your server installation is either corrupt or has been tampered with (unknown package ID)��" I clicked OK and waited a few extra minutes for XP to finish logging me in.

After logging in, I closed the Service Manger from the taskbar notification area. I then deleted the folders 80, and MSSQL (name of default instance) from the target install directory. Finally, I ran setup.exe again as shown:

>setup /L*V "C:\Program Files\Microsoft Visual Studio .NET\Setup\MSDE\setupgood.log" SAPWD="ignored password"

This time, after restarting, my old MSDE worked. This approach seems to only work with the first old version of MSDE that was shipped with Visual Studio .NET. I still have yet to figure out how to upgrade and get MSDE to work again.

Once you get MSDE to work, here are some tips on how to administer it using the osql command tool.
To change the sa password do the following (use null for a blank password):
>osql -U sa
Password:
1> sp-password @.old=null, @.new='newpassword', @.loginame='sa'
2> go
Password has been changed
3> quit

On page 113 of the authors' first attempt with the book "Microsoft ASP.NET Programming��", the hacks wrote:
Set up the SQL Server session state database by running the InstallSQLState.sql batch (located in ��) against the SQL Server you plan to use. (For more information about running batch statements, check with your database administrator or the SQL Server Books Online.)

Here is how to do it with osql:

> osql -S MSSQLSERVER -U sa -i InstallSQLState.sql

For less characters than it took to write the last parenthetical sentence, they could have just given the same example I supplied here.

If you want a graphical interface to administer you MSDE server, try downloading the small package for "Microsoft SQL Web Data Administrator" from Microsoft's website. This even comes with a modern installer. Here are some tips on how to connect to your locally installed MSDE using this utility:

The main dialog just requires that you click the start button. A login page comes up. To log in as sa, do the following:

? Click the "SQL Login" radio button.
? Under "Please enter a SQL Server name:" fill in the following text boxes:
? Username: sa
? Password: [your password | leave blank if no password is set]
? Server: (local)

This should get you to the server tools page. Click "Security", then Logins. Now, on the Logins page, you can add new users. If you install MSDE for mixed mode, you can add "Windows Integrated" user accounts. This best since you won't have to put passwords in your code to allow database access to these users. To add a user, click on "Create New Login". Set Authentication Method to "Windows Integrated" in the combo box. For Login Name you need to use the form <computername>\<username> . For example, "MSSQLServer\Dan".

If you did like I did, and installed MSDE in SQL login mode only, you can do the following registry hack to change it to mixed authentication mode:

1. Locate either of the following subkeys (depending on whether you installed MSDE as the default MSDE instance or as a named instance:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer

-or-

HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\<Instance Name>\MSSQLServer\

2. In the right-pane, double-click the LoginMode subkey.

3. In the DWORD Editor dialog box, set the value of this subkey to 1. Make sure that the Hex option is selected, and then click OK.

4. Restart the MSSQLSERVER and the SQLSERVERAgent services for this change to take effect.

Try this link for more details:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q322336Correction to this step:
3. In the DWORD Editor dialog box, set the value of this subkey to 1.

For Mixed mode, the subkey should be 0 or 2.
For Windows Integrated only, the subkey should be 1.|||First a syntax typo correction:
change sp-password to sp_password. So the section on changing the sa password should read:

To change the sa password do the following (use null for a blank password):
>osql -U sa
Password:
1> sp_password @.old=null, @.new='newpassword', @.loginame='sa'
2> go
Password has been changed
3> quit

Notes on upgrading to SP3a.

Prerequisites:
1) You have to know the sa password for this solution to work.
2) Parameters in example assume you are in mixed authentication mode.
3) You should stop SNMP services.

Command line setup:
At the command line, type the following or paste this in a *.bat file and run the bat file:

>setup /upgradesp sqlrun DISABLENETWORKPROTOCOLS=1 /L*V C:\sql2ksp3\MSDE\setupbat.log SAPWD=yourSApassword

The "DISABLENETWORKPROTOCOLS=1" is optional. The default when installing a new instance of SP3a is to disable network protocol support. I don't know what the option does when you are simply upgrading. You can run svrnetcn.exe from the Tools\Binn directory to enable network protocols later. I found this tip on another forum: http://www.mcse.ms/message290056.html

You need to check the log file (setupbat.log in this case) to make sure the upgrade finished successfully. It should say, "Configuration completed successfully."

I tried running this without the SAPWD parameter since this was left off the example under section 3.7.4 of the sp3readme.htm file. The upgrade failed. If the sa password is still blank it says to use the BLANKSAPWD=1 parameter. Requiring the password makes sense because the installer needs to access your database files in order to upgrade. The "Northwind" sample database was deleted during the upgrade.|||Ok I've got as far as this line:

>setup /L*V "C:\Program Files\Microsoft Visual Studio .NET\Setup\MSDE\setupgood.log" SAPWD="ignored password"

When I restarted my computer I got the "Your server installation is either corrupt or has been tampered with..." message and the MSDE icon was in my System Tray (with a red square in a white circle). So I closed this program and entered the above code in cmd line. However a dialog box appeared and said it could not locate the above file. I tried looking in the directory specified above and I couldn't find the file or the folders "80" and "MSSQL" either.|||Hello!

I got the same problem when installing MSDE at the end the performance counters install fails, beauce it can not update the reg key (as mentioned in my therad).

To uninstall MSDE see 320873 (german). Tipp you need only the msizap.exe prog. Evt. you dont must donload the whole SDK.
But uninstalling and new installation doesnt help either.

There is still the problem with the performance counters, and there is no solution yet (I really looked the www for the last days and did not found any solution.

lg ifoko|||You need to open a cmd window and run the command from the path where "setup" for MSDE lives. "MSSQL" may be named something else if you used INSTANCE= <name> in your first install. The default install target path is "C:\Program Files\Microsoft SQL Server\". This is where you should find the "80" and "MSSQL" folder to delete. Use a search to locate the "80" folder if you have to.

The other path (C:\Program Files\Microsoft Visual Studio .NET\Setup\MSDE\) is where my Microsoft Visual Studio .NET 2002 is installed. The buggy version of MSDE came with my .NET software.|||To VilleValo:

On second thought, I don't see how you could get "setup DisableRollback=1 /L*V "C:\Program Files\Microsoft Visual Studio .NET\Setup\MSDE\setupbat.log" SAPWD=IgnoredPassword" to work, and you can't get "setup /L*V "C:\Program Files\Microsoft Visual Studio .NET\Setup\MSDE\setupgood.log" SAPWD=IgnoredPassword" to work. I created these commands in .bat files and ran the .bat files. This way, if I needed to make any further modifications, I could edit the .bat files. It also leaves a record of what I did.|||thx dran001 for help.

But my problem is that I got no previous "working" installation of MSDE.

The problem is that I cant update the Perflib\009 key. Either the registry is damaged, or some service lock the key.
And I still no found any help with issue.

lg|||I think my problem was caused by the setup.ini file that came shipped with .NET. This file contains:
[Options]
INSTANCENAME=VSdotNET

So when I did the first install, my instance of MSDE was named VSdotNET. When I tried to install SP3a version after the uninstall, I didn't use the ini file. I ran this install from a different folder. The Windows uninstall must have left references to VSdotNET in the registry which left me unable to install another instance since I had uninstalled the first instance. The default instance is just MSSQL. If you supply an INSTANCENAME option, the instance is named MSSQL$<instancename> or MSSQL$VSdotNET for example.

I think the PerfMon is loaded after the error so that the MSDE installer can draw a nice reverse progress bar as the install is being rolled back. Here is a clip of my log file were I think the real error is:

MSI (s) (7C:DC) [15:59:17:020]: QueryPathOfRegTypeLib returned -2147319779 in local context.Path is ''
MSI (s) (7C:DC) [15:59:17:020]: CMsiServices::ProcessTypeLibrary runs in local context, not impersonated.
MSI (s) (7C:DC) [15:59:17:120]: ProcessTypeLibraryCore returns: 0. (0 means OK)
MSI (s) (7C:DC) [15:59:17:120]: Executing op: TypeLibraryRegister(,,FilePath=C:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtspkg.DLL,LibID={10010001-EB1C-11CF-AE6E-00AA004A34D5},Version=-2147483648,,Language=0,,BinaryType=0,IgnoreRegistrationFailure=0)
MSI (s) (7C:DC) [15:59:17:120]: QueryPathOfRegTypeLib returned -2147319779 in local context.Path is ''
MSI (s) (7C:DC) [15:59:17:120]: CMsiServices::ProcessTypeLibrary runs in local context, not impersonated.
MSI (s) (7C:DC) [15:59:17:181]: ProcessTypeLibraryCore returns: 0. (0 means OK)
MSI (s) (7C:DC) [15:59:17:181]: Executing op: TypeLibraryRegister(,,FilePath=C:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtspump.DLL,LibID={10010200-740B-11D0-AE7B-00AA004A34D5},Version=-2147483648,,Language=0,,BinaryType=0,IgnoreRegistrationFailure=0)
MSI (s) (7C:DC) [15:59:17:181]: QueryPathOfRegTypeLib returned -2147319779 in local context.Path is ''
MSI (s) (7C:DC) [15:59:17:181]: CMsiServices::ProcessTypeLibrary runs in local context, not impersonated.
MSI (s) (7C:DC) [15:59:17:211]: ProcessTypeLibraryCore returns: 0. (0 means OK)
MSI (s) (7C:DC) [15:59:17:211]: Executing op: TypeLibraryRegister(,,FilePath=C:\WINDOWS\system32\atl.dll,LibID={44EC0535-400F-11D0-9DCD-00A0C90391D3},Version=-2147483648,,Language=0,,BinaryType=0,IgnoreRegistrationFailure=1)
MSI (s) (7C:DC) [15:59:17:221]: QueryPathOfRegTypeLib returned -2147319779 in local context.Path is ''
MSI (s) (7C:DC) [15:59:17:221]: CMsiServices::ProcessTypeLibrary runs in local context, not impersonated.
MSI (s) (7C:DC) [15:59:17:261]: ProcessTypeLibraryCore returns: 0. (0 means OK)
MSI (s) (7C:DC) [15:59:17:261]: Executing op: ActionStart(Name=InstallServices,Description=Installing new services,Template=Service: [2])
MSI (s) (7C:DC) [15:59:17:261]: Executing op: ProgressTotal(Total=2,Type=1,ByteEquivalent=1300000)
MSI (s) (7C:DC) [15:59:17:261]: Executing op: ServiceInstall(Name=SQLAgent$VSdotNET,DisplayName=SQLAgent$VSdotNET,ImagePath=C:\Program Files\Microsoft SQL Server\MSSQL$VSdotNET\Binn\sqlagent.EXE -i VSdotNET,ServiceType=16,StartType=3,ErrorControl=1,,Dependencies=MSSQL$VSdotNET
MSI (s) (7C:DC) [15:59:17:871]: Executing op: ServiceInstall(Name=MSSQL$VSdotNET,DisplayName=MSSQL$VSdotNET,ImagePath=C:\Program Files\Microsoft SQL Server\MSSQL$VSdotNET\Binn\sqlservr.exe -sVSdotNET,ServiceType=16,StartType=2,ErrorControl=1,,,,,,)
MSI (s) (7C:DC) [15:59:18:072]: Executing op: ActionStart(Name=InstallPerfMon.2D02443E_7002_4C0B_ABC9_EAB2C064397B,,)
MSI (s) (7C:DC) [15:59:18:082]: Executing op: CustomActionSchedule(Action=InstallPerfMon.2D02443E_7002_4C0B_ABC9_EAB2C064397B,ActionType=1025,Source=BinaryData,Target=InstallPerfMon,)
MSI (s) (7C:48) [15:59:18:152]: Invoking remote custom action. DLL: C:\WINDOWS\Installer\MSI8F.tmp, Entrypoint: InstallPerfMon