Hi Everyone,
I'm running a non-clustered sql environment, and I was
considering the scenario where I could install multiple
instances of sql server onto one "work-horse" server.
I'm concerned about the 2 Gb memory limit. Is this limit
per instance, or per physical machine?
E.g. one quad-processor server, with 6 Gb of memory
could easily accept 2 sql instances each with a 2Gb
memory limit.
Thanks in Advance
CraigHi,
This limitation in Memory / Cpu is instance based. So if you have 2 SQL
server instances you can allocate a maximum of 2 GB RAM for each instance.
FYI,
If your SQL Server edition is Enterprise edition it supports more than 2 GB
RAM for each instance. See books online on "Maximum Capacity specifications"
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"Craig Irvine" <anonymous@.discussions.microsoft.com> wrote in message
news:cd6601c43940$2592e2d0$a601280a@.phx.gbl...
> Hi Everyone,
> I'm running a non-clustered sql environment, and I was
> considering the scenario where I could install multiple
> instances of sql server onto one "work-horse" server.
> I'm concerned about the 2 Gb memory limit. Is this limit
> per instance, or per physical machine?
> E.g. one quad-processor server, with 6 Gb of memory
> could easily accept 2 sql instances each with a 2Gb
> memory limit.
> Thanks in Advance
> Craig
>|||Hi Craig,
The 2GB Memory limitation is per instance and the SQL Server can be
configured to use more memory than 2GB. Please refer to the following
article
http://support.microsoft.com/defaul...0&Product=sql2k
Excerpt from the KB Article
HOW TO: Configure memory for more than 2 GB in SQL Server
View products that this article applies to.
This article was previously published under Q274750
IN THIS TASK
SUMMARY
Support for Operating System Versions
Microsoft Windows 2000 Server
Microsoft SQL Server 2000
Microsoft SQL Server 7.0
Microsoft Windows 2000 Advanced Server or Microsoft Windows 2000 Datacenter
or Windows Server 2003
Microsoft SQL Server 2000
Microsoft SQL Server 7.0
Microsoft Windows NT 4.0 Enterprise Edition
REFERENCES
SUMMARY
This article describes how to configure SQL Server to use more than 2 GB of
physical memory.
Microsoft SQL Server 7.0 and Microsoft SQL Server 2000 dynamically acquire
and free memory as needed. When you run multiple instances of SQL Server on
a computer, each instance dynamically acquires and frees memory to adjust
for changes in the workload of the instance.
SQL Server 2000 Enterprise Edition introduces support for the use of
Microsoft Windows 2000 Address Windowing Extensions (AWE) to address
approximately 8 GB of memory for instances that run on Microsoft Windows
2000 Advanced Server, and approximately 32 GB for instances that run on
Microsoft Windows 2000 Datacenter. With AWE, SQL Server can reserve memory
that is not in use for other applications and the operating system. Each
instance that uses this memory; however, must statically allocate the
memory it needs. SQL Server can only use this AWE allocated memory for the
data cache and not for executables, drivers, DLLs, and so forth. For
additional information, click the article number below to view the article
in the Microsoft Knowledge
Base:
283037 Large Memory Support Is Available in Windows 2000
The extended memory size option is available only for Microsoft SQL Server
7.0, Enterprise Edition on the operating system and hardware that supports
the Enterprise Memory Architecture (EMA) feature. For more information
about how to configure your system to enable the EMA feature on a
particular system configuration, refer to your Microsoft Windows NT
documentation.
Some system vendors may provide products for Microsoft Windows NT, version
4.0 or later, so that SQL Server 7.0, Enterprise Edition can use the
extended memory size option. On Intel platforms, SQL Server 7.0 can use a
feature known as PSE36. On Alpha platforms, the feature that allows use of
the extended memory option is Very Large Memory (VLM). For more information
about the availability, installation, and configuration of these products,
contact your system vendor.
Note: To use Address Windowing Extensions (AWE) memory, you must run the
SQL Server 2000 database engine under a Windows account that has been
assigned the Windows lock pages in memory administrative credentials.
back to the top
Support for Operating System Versions
Windows 2000 Server
SQL Server 2000
Normally, both the SQL Server 2000 Enterprise Edition and SQL Server 2000
Developer Edition can use up to 2 GB of physical memory. With the use of
the AWE enable option, SQL Server can use up to 4 GB of physical memory.
NOTE: You cannot allocate more than 4 GB of physical memory to an
application on Windows 2000 Server because Physical Address Extension (PAE)
is not available on Microsoft Windows 2000 Server. Also, you cannot use the
3 GB switch in the Boot.ini file with Windows 2000 Server; however, you can
use the 3 GB switch in the Boot.ini file with Microsoft Windows 2000
Advanced Server or Microsoft Windows Datacenter versions. For additional
information, click the article number below to view the article in the
Microsoft Knowledge Base:
291988 A Description of the 4 GB RAM Tuning Feature and the Physical
Address Extension Switch
back to the top
SQL Server 7.0
SQL Server 7.0 versions can use up to 2 GB of physical memory.
NOTE: The extended memory option in SQL Server 7.0 is not available on
Windows 2000.
Windows 2000 Advanced Server or Windows 2000 Datacenter or Windows Server
2003
The maximum amount of physical memory addressable by a 32-bit addressing
mode is 4 GB. All processors based on the IA-32 architecture that begin
with the Intel Pentium Pro, support a new 36-bit physical addressing mode
known as Physical Address Extension (PAE). PAE allows up to 8 GB of
physical memory on Windows 2000 Advanced Server and up to 32 GB of physical
memory on Windows 2000 Datacenter Server. This is because the tested memory
limit on Windows 2000 Datacenter Server is 32 GB. The PAE mode kernel
requires an Intel architecture processor, Pentium Pro, or later and either
Windows 2000 Advanced Server or Windows 2000 Datacenter. For additional
information, click the article number below to view the article in the
Microsoft
Knowledge Base:
268230 Scaling Out Versus Scaling Up with Intel Physical Addressing
Extensions (PAE)
Note The maximum amount of memory that can be supported on Windows Server
2003 is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32
GB of physical RAM. Windows Server 2003 Datacenter Edition supports 64 GB
of physical RAM by using the Physical Address Extensions (PAE) feature. You
can use the 3 GB switch that is in the Boot.ini file with Microsoft Windows
Server 2003, Microsoft Windows Server 2003 Enterprise Edition, or with
Microsoft Windows Server 2003 Datacenter Edition.
SQL Server 2000
Both SQL Server 2000 Enterprise and SQL Server 2000 Developer Editions can
use the following options:
Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL
Server allows SQL Server 2000 to utilize more than 4 GB memory. Without the
/PAE switch SQL Server can only utilize up to 4 GB of memory.
NOTE: To allow AWE to use the memory range above 16 GB on Windows 2000 Data
Center, make sure that the /3GB switch is not in the Boot.ini file. If the
/3GB switch is in the Boot.ini file, Windows 2000 may not be able to
address any memory above 16 GB correctly.
When you allocate SQL Server AWE memory on a 32 GB system, Windows 2000 may
require at least 1 GB memory to manage AWE.
back to the top
Example
The following example shows how to enable AWE and configure a limit of 6 GB
for the max server memory option: sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO
-or-
-or-Use of the /3GB switch in the Boot.ini file allows SQL Server 2000 to
use up to 3 GB of available memory.
back to the top
SQL Server 7.0
SQL Server 7.0 Enterprise Edition requires the /3GB switch in the Boot.ini
in order to address up to 3 GB of available memory.
NOTE: The extended memory option in SQL Server 7.0 is not available on
Windows 2000 Advanced Server or Windows Datacenter.
Microsoft Windows NT 4.0 Enterprise Edition
SQL Server 2000
SQL Server 2000 Enterprise and Developer Edition requires the /3GB switch
in the Boot.ini file in order to address up to 3 GB of available memory.
NOTE: Windows NT 4.0 Enterprise Edition does not support the AWE memory
architecture model so AWE support is not available.
back to the top
SQL Server 7.0
SQL Server 7.0 Enterprise Edition requires the /3GB switch in the Boot.ini
file in order to address up to 3 GB of available memory.
back to the top
REFERENCES
For additional information, click the article numbers below to view the
articles in the Microsoft Knowledge Base:
170756 Available Switch Options for Windows NT Boot.ini File
171793 Information on Application Use of 4GT RAM Tuning
268363 Intel Physical Addressing Extensions (PAE) in Windows 2000
292934 Windows 2000 Datacenter Server Does Not Locate Memory Greater Than
16 GB
SQL Server 2000 Books Online; topics: "Managing AWE Memory"; "Running
Multiple Instances and Using Failover Clustering"
back to the top
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
Showing posts with label scenario. Show all posts
Showing posts with label scenario. Show all posts
Wednesday, March 28, 2012
multiple instances of sql - 2 Gb memory limitation?
Hi Everyone,
I'm running a non-clustered sql environment, and I was
considering the scenario where I could install multiple
instances of sql server onto one "work-horse" server.
I'm concerned about the 2 Gb memory limit. Is this limit
per instance, or per physical machine?
E.g. one quad-processor server, with 6 Gb of memory
could easily accept 2 sql instances each with a 2Gb
memory limit.
Thanks in Advance
Craig
Hi,
This limitation in Memory / Cpu is instance based. So if you have 2 SQL
server instances you can allocate a maximum of 2 GB RAM for each instance.
FYI,
If your SQL Server edition is Enterprise edition it supports more than 2 GB
RAM for each instance. See books online on "Maximum Capacity specifications"
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"Craig Irvine" <anonymous@.discussions.microsoft.com> wrote in message
news:cd6601c43940$2592e2d0$a601280a@.phx.gbl...
> Hi Everyone,
> I'm running a non-clustered sql environment, and I was
> considering the scenario where I could install multiple
> instances of sql server onto one "work-horse" server.
> I'm concerned about the 2 Gb memory limit. Is this limit
> per instance, or per physical machine?
> E.g. one quad-processor server, with 6 Gb of memory
> could easily accept 2 sql instances each with a 2Gb
> memory limit.
> Thanks in Advance
> Craig
>
|||Hi Craig,
The 2GB Memory limitation is per instance and the SQL Server can be
configured to use more memory than 2GB. Please refer to the following
article
http://support.microsoft.com/default...&Product=sql2k
Excerpt from the KB Article
HOW TO: Configure memory for more than 2 GB in SQL Server
View products that this article applies to.
This article was previously published under Q274750
IN THIS TASK
SUMMARY
Support for Operating System Versions
Microsoft Windows 2000 Server
Microsoft SQL Server 2000
Microsoft SQL Server 7.0
Microsoft Windows 2000 Advanced Server or Microsoft Windows 2000 Datacenter
or Windows Server 2003
Microsoft SQL Server 2000
Microsoft SQL Server 7.0
Microsoft Windows NT 4.0 Enterprise Edition
REFERENCES
SUMMARY
This article describes how to configure SQL Server to use more than 2 GB of
physical memory.
Microsoft SQL Server 7.0 and Microsoft SQL Server 2000 dynamically acquire
and free memory as needed. When you run multiple instances of SQL Server on
a computer, each instance dynamically acquires and frees memory to adjust
for changes in the workload of the instance.
SQL Server 2000 Enterprise Edition introduces support for the use of
Microsoft Windows 2000 Address Windowing Extensions (AWE) to address
approximately 8 GB of memory for instances that run on Microsoft Windows
2000 Advanced Server, and approximately 32 GB for instances that run on
Microsoft Windows 2000 Datacenter. With AWE, SQL Server can reserve memory
that is not in use for other applications and the operating system. Each
instance that uses this memory; however, must statically allocate the
memory it needs. SQL Server can only use this AWE allocated memory for the
data cache and not for executables, drivers, DLLs, and so forth. For
additional information, click the article number below to view the article
in the Microsoft Knowledge
Base:
283037 Large Memory Support Is Available in Windows 2000
The extended memory size option is available only for Microsoft SQL Server
7.0, Enterprise Edition on the operating system and hardware that supports
the Enterprise Memory Architecture (EMA) feature. For more information
about how to configure your system to enable the EMA feature on a
particular system configuration, refer to your Microsoft Windows NT
documentation.
Some system vendors may provide products for Microsoft Windows NT, version
4.0 or later, so that SQL Server 7.0, Enterprise Edition can use the
extended memory size option. On Intel platforms, SQL Server 7.0 can use a
feature known as PSE36. On Alpha platforms, the feature that allows use of
the extended memory option is Very Large Memory (VLM). For more information
about the availability, installation, and configuration of these products,
contact your system vendor.
Note: To use Address Windowing Extensions (AWE) memory, you must run the
SQL Server 2000 database engine under a Windows account that has been
assigned the Windows lock pages in memory administrative credentials.
back to the top
Support for Operating System Versions
Windows 2000 Server
SQL Server 2000
Normally, both the SQL Server 2000 Enterprise Edition and SQL Server 2000
Developer Edition can use up to 2 GB of physical memory. With the use of
the AWE enable option, SQL Server can use up to 4 GB of physical memory.
NOTE: You cannot allocate more than 4 GB of physical memory to an
application on Windows 2000 Server because Physical Address Extension (PAE)
is not available on Microsoft Windows 2000 Server. Also, you cannot use the
3 GB switch in the Boot.ini file with Windows 2000 Server; however, you can
use the 3 GB switch in the Boot.ini file with Microsoft Windows 2000
Advanced Server or Microsoft Windows Datacenter versions. For additional
information, click the article number below to view the article in the
Microsoft Knowledge Base:
291988 A Description of the 4 GB RAM Tuning Feature and the Physical
Address Extension Switch
back to the top
SQL Server 7.0
SQL Server 7.0 versions can use up to 2 GB of physical memory.
NOTE: The extended memory option in SQL Server 7.0 is not available on
Windows 2000.
Windows 2000 Advanced Server or Windows 2000 Datacenter or Windows Server
2003
The maximum amount of physical memory addressable by a 32-bit addressing
mode is 4 GB. All processors based on the IA-32 architecture that begin
with the Intel Pentium Pro, support a new 36-bit physical addressing mode
known as Physical Address Extension (PAE). PAE allows up to 8 GB of
physical memory on Windows 2000 Advanced Server and up to 32 GB of physical
memory on Windows 2000 Datacenter Server. This is because the tested memory
limit on Windows 2000 Datacenter Server is 32 GB. The PAE mode kernel
requires an Intel architecture processor, Pentium Pro, or later and either
Windows 2000 Advanced Server or Windows 2000 Datacenter. For additional
information, click the article number below to view the article in the
Microsoft
Knowledge Base:
268230 Scaling Out Versus Scaling Up with Intel Physical Addressing
Extensions (PAE)
Note The maximum amount of memory that can be supported on Windows Server
2003 is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32
GB of physical RAM. Windows Server 2003 Datacenter Edition supports 64 GB
of physical RAM by using the Physical Address Extensions (PAE) feature. You
can use the 3 GB switch that is in the Boot.ini file with Microsoft Windows
Server 2003, Microsoft Windows Server 2003 Enterprise Edition, or with
Microsoft Windows Server 2003 Datacenter Edition.
SQL Server 2000
Both SQL Server 2000 Enterprise and SQL Server 2000 Developer Editions can
use the following options:
Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL
Server allows SQL Server 2000 to utilize more than 4 GB memory. Without the
/PAE switch SQL Server can only utilize up to 4 GB of memory.
NOTE: To allow AWE to use the memory range above 16 GB on Windows 2000 Data
Center, make sure that the /3GB switch is not in the Boot.ini file. If the
/3GB switch is in the Boot.ini file, Windows 2000 may not be able to
address any memory above 16 GB correctly.
When you allocate SQL Server AWE memory on a 32 GB system, Windows 2000 may
require at least 1 GB memory to manage AWE.
back to the top
Example
The following example shows how to enable AWE and configure a limit of 6 GB
for the max server memory option: sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO
-or-
-or-Use of the /3GB switch in the Boot.ini file allows SQL Server 2000 to
use up to 3 GB of available memory.
back to the top
SQL Server 7.0
SQL Server 7.0 Enterprise Edition requires the /3GB switch in the Boot.ini
in order to address up to 3 GB of available memory.
NOTE: The extended memory option in SQL Server 7.0 is not available on
Windows 2000 Advanced Server or Windows Datacenter.
Microsoft Windows NT 4.0 Enterprise Edition
SQL Server 2000
SQL Server 2000 Enterprise and Developer Edition requires the /3GB switch
in the Boot.ini file in order to address up to 3 GB of available memory.
NOTE: Windows NT 4.0 Enterprise Edition does not support the AWE memory
architecture model so AWE support is not available.
back to the top
SQL Server 7.0
SQL Server 7.0 Enterprise Edition requires the /3GB switch in the Boot.ini
file in order to address up to 3 GB of available memory.
back to the top
REFERENCES
For additional information, click the article numbers below to view the
articles in the Microsoft Knowledge Base:
170756 Available Switch Options for Windows NT Boot.ini File
171793 Information on Application Use of 4GT RAM Tuning
268363 Intel Physical Addressing Extensions (PAE) in Windows 2000
292934 Windows 2000 Datacenter Server Does Not Locate Memory Greater Than
16 GB
SQL Server 2000 Books Online; topics: "Managing AWE Memory"; "Running
Multiple Instances and Using Failover Clustering"
back to the top
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
sql
I'm running a non-clustered sql environment, and I was
considering the scenario where I could install multiple
instances of sql server onto one "work-horse" server.
I'm concerned about the 2 Gb memory limit. Is this limit
per instance, or per physical machine?
E.g. one quad-processor server, with 6 Gb of memory
could easily accept 2 sql instances each with a 2Gb
memory limit.
Thanks in Advance
Craig
Hi,
This limitation in Memory / Cpu is instance based. So if you have 2 SQL
server instances you can allocate a maximum of 2 GB RAM for each instance.
FYI,
If your SQL Server edition is Enterprise edition it supports more than 2 GB
RAM for each instance. See books online on "Maximum Capacity specifications"
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"Craig Irvine" <anonymous@.discussions.microsoft.com> wrote in message
news:cd6601c43940$2592e2d0$a601280a@.phx.gbl...
> Hi Everyone,
> I'm running a non-clustered sql environment, and I was
> considering the scenario where I could install multiple
> instances of sql server onto one "work-horse" server.
> I'm concerned about the 2 Gb memory limit. Is this limit
> per instance, or per physical machine?
> E.g. one quad-processor server, with 6 Gb of memory
> could easily accept 2 sql instances each with a 2Gb
> memory limit.
> Thanks in Advance
> Craig
>
|||Hi Craig,
The 2GB Memory limitation is per instance and the SQL Server can be
configured to use more memory than 2GB. Please refer to the following
article
http://support.microsoft.com/default...&Product=sql2k
Excerpt from the KB Article
HOW TO: Configure memory for more than 2 GB in SQL Server
View products that this article applies to.
This article was previously published under Q274750
IN THIS TASK
SUMMARY
Support for Operating System Versions
Microsoft Windows 2000 Server
Microsoft SQL Server 2000
Microsoft SQL Server 7.0
Microsoft Windows 2000 Advanced Server or Microsoft Windows 2000 Datacenter
or Windows Server 2003
Microsoft SQL Server 2000
Microsoft SQL Server 7.0
Microsoft Windows NT 4.0 Enterprise Edition
REFERENCES
SUMMARY
This article describes how to configure SQL Server to use more than 2 GB of
physical memory.
Microsoft SQL Server 7.0 and Microsoft SQL Server 2000 dynamically acquire
and free memory as needed. When you run multiple instances of SQL Server on
a computer, each instance dynamically acquires and frees memory to adjust
for changes in the workload of the instance.
SQL Server 2000 Enterprise Edition introduces support for the use of
Microsoft Windows 2000 Address Windowing Extensions (AWE) to address
approximately 8 GB of memory for instances that run on Microsoft Windows
2000 Advanced Server, and approximately 32 GB for instances that run on
Microsoft Windows 2000 Datacenter. With AWE, SQL Server can reserve memory
that is not in use for other applications and the operating system. Each
instance that uses this memory; however, must statically allocate the
memory it needs. SQL Server can only use this AWE allocated memory for the
data cache and not for executables, drivers, DLLs, and so forth. For
additional information, click the article number below to view the article
in the Microsoft Knowledge
Base:
283037 Large Memory Support Is Available in Windows 2000
The extended memory size option is available only for Microsoft SQL Server
7.0, Enterprise Edition on the operating system and hardware that supports
the Enterprise Memory Architecture (EMA) feature. For more information
about how to configure your system to enable the EMA feature on a
particular system configuration, refer to your Microsoft Windows NT
documentation.
Some system vendors may provide products for Microsoft Windows NT, version
4.0 or later, so that SQL Server 7.0, Enterprise Edition can use the
extended memory size option. On Intel platforms, SQL Server 7.0 can use a
feature known as PSE36. On Alpha platforms, the feature that allows use of
the extended memory option is Very Large Memory (VLM). For more information
about the availability, installation, and configuration of these products,
contact your system vendor.
Note: To use Address Windowing Extensions (AWE) memory, you must run the
SQL Server 2000 database engine under a Windows account that has been
assigned the Windows lock pages in memory administrative credentials.
back to the top
Support for Operating System Versions
Windows 2000 Server
SQL Server 2000
Normally, both the SQL Server 2000 Enterprise Edition and SQL Server 2000
Developer Edition can use up to 2 GB of physical memory. With the use of
the AWE enable option, SQL Server can use up to 4 GB of physical memory.
NOTE: You cannot allocate more than 4 GB of physical memory to an
application on Windows 2000 Server because Physical Address Extension (PAE)
is not available on Microsoft Windows 2000 Server. Also, you cannot use the
3 GB switch in the Boot.ini file with Windows 2000 Server; however, you can
use the 3 GB switch in the Boot.ini file with Microsoft Windows 2000
Advanced Server or Microsoft Windows Datacenter versions. For additional
information, click the article number below to view the article in the
Microsoft Knowledge Base:
291988 A Description of the 4 GB RAM Tuning Feature and the Physical
Address Extension Switch
back to the top
SQL Server 7.0
SQL Server 7.0 versions can use up to 2 GB of physical memory.
NOTE: The extended memory option in SQL Server 7.0 is not available on
Windows 2000.
Windows 2000 Advanced Server or Windows 2000 Datacenter or Windows Server
2003
The maximum amount of physical memory addressable by a 32-bit addressing
mode is 4 GB. All processors based on the IA-32 architecture that begin
with the Intel Pentium Pro, support a new 36-bit physical addressing mode
known as Physical Address Extension (PAE). PAE allows up to 8 GB of
physical memory on Windows 2000 Advanced Server and up to 32 GB of physical
memory on Windows 2000 Datacenter Server. This is because the tested memory
limit on Windows 2000 Datacenter Server is 32 GB. The PAE mode kernel
requires an Intel architecture processor, Pentium Pro, or later and either
Windows 2000 Advanced Server or Windows 2000 Datacenter. For additional
information, click the article number below to view the article in the
Microsoft
Knowledge Base:
268230 Scaling Out Versus Scaling Up with Intel Physical Addressing
Extensions (PAE)
Note The maximum amount of memory that can be supported on Windows Server
2003 is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32
GB of physical RAM. Windows Server 2003 Datacenter Edition supports 64 GB
of physical RAM by using the Physical Address Extensions (PAE) feature. You
can use the 3 GB switch that is in the Boot.ini file with Microsoft Windows
Server 2003, Microsoft Windows Server 2003 Enterprise Edition, or with
Microsoft Windows Server 2003 Datacenter Edition.
SQL Server 2000
Both SQL Server 2000 Enterprise and SQL Server 2000 Developer Editions can
use the following options:
Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL
Server allows SQL Server 2000 to utilize more than 4 GB memory. Without the
/PAE switch SQL Server can only utilize up to 4 GB of memory.
NOTE: To allow AWE to use the memory range above 16 GB on Windows 2000 Data
Center, make sure that the /3GB switch is not in the Boot.ini file. If the
/3GB switch is in the Boot.ini file, Windows 2000 may not be able to
address any memory above 16 GB correctly.
When you allocate SQL Server AWE memory on a 32 GB system, Windows 2000 may
require at least 1 GB memory to manage AWE.
back to the top
Example
The following example shows how to enable AWE and configure a limit of 6 GB
for the max server memory option: sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO
-or-
-or-Use of the /3GB switch in the Boot.ini file allows SQL Server 2000 to
use up to 3 GB of available memory.
back to the top
SQL Server 7.0
SQL Server 7.0 Enterprise Edition requires the /3GB switch in the Boot.ini
in order to address up to 3 GB of available memory.
NOTE: The extended memory option in SQL Server 7.0 is not available on
Windows 2000 Advanced Server or Windows Datacenter.
Microsoft Windows NT 4.0 Enterprise Edition
SQL Server 2000
SQL Server 2000 Enterprise and Developer Edition requires the /3GB switch
in the Boot.ini file in order to address up to 3 GB of available memory.
NOTE: Windows NT 4.0 Enterprise Edition does not support the AWE memory
architecture model so AWE support is not available.
back to the top
SQL Server 7.0
SQL Server 7.0 Enterprise Edition requires the /3GB switch in the Boot.ini
file in order to address up to 3 GB of available memory.
back to the top
REFERENCES
For additional information, click the article numbers below to view the
articles in the Microsoft Knowledge Base:
170756 Available Switch Options for Windows NT Boot.ini File
171793 Information on Application Use of 4GT RAM Tuning
268363 Intel Physical Addressing Extensions (PAE) in Windows 2000
292934 Windows 2000 Datacenter Server Does Not Locate Memory Greater Than
16 GB
SQL Server 2000 Books Online; topics: "Managing AWE Memory"; "Running
Multiple Instances and Using Failover Clustering"
back to the top
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
sql
multiple instances of sql - 2 Gb memory limitation?
Hi Everyone,
I'm running a non-clustered sql environment, and I was
considering the scenario where I could install multiple
instances of sql server onto one "work-horse" server.
I'm concerned about the 2 Gb memory limit. Is this limit
per instance, or per physical machine?
E.g. one quad-processor server, with 6 Gb of memory
could easily accept 2 sql instances each with a 2Gb
memory limit.
Thanks in Advance
CraigHi,
This limitation in Memory / Cpu is instance based. So if you have 2 SQL
server instances you can allocate a maximum of 2 GB RAM for each instance.
FYI,
If your SQL Server edition is Enterprise edition it supports more than 2 GB
RAM for each instance. See books online on "Maximum Capacity specifications"
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"Craig Irvine" <anonymous@.discussions.microsoft.com> wrote in message
news:cd6601c43940$2592e2d0$a601280a@.phx.gbl...
> Hi Everyone,
> I'm running a non-clustered sql environment, and I was
> considering the scenario where I could install multiple
> instances of sql server onto one "work-horse" server.
> I'm concerned about the 2 Gb memory limit. Is this limit
> per instance, or per physical machine?
> E.g. one quad-processor server, with 6 Gb of memory
> could easily accept 2 sql instances each with a 2Gb
> memory limit.
> Thanks in Advance
> Craig
>|||Hi Craig,
The 2GB Memory limitation is per instance and the SQL Server can be
configured to use more memory than 2GB. Please refer to the following
article
http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&Product=sql2k
Excerpt from the KB Article
HOW TO: Configure memory for more than 2 GB in SQL Server
View products that this article applies to.
This article was previously published under Q274750
IN THIS TASK
SUMMARY
Support for Operating System Versions
Microsoft Windows 2000 Server
Microsoft SQL Server 2000
Microsoft SQL Server 7.0
Microsoft Windows 2000 Advanced Server or Microsoft Windows 2000 Datacenter
or Windows Server 2003
Microsoft SQL Server 2000
Microsoft SQL Server 7.0
Microsoft Windows NT 4.0 Enterprise Edition
REFERENCES
SUMMARY
This article describes how to configure SQL Server to use more than 2 GB of
physical memory.
Microsoft SQL Server 7.0 and Microsoft SQL Server 2000 dynamically acquire
and free memory as needed. When you run multiple instances of SQL Server on
a computer, each instance dynamically acquires and frees memory to adjust
for changes in the workload of the instance.
SQL Server 2000 Enterprise Edition introduces support for the use of
Microsoft Windows 2000 Address Windowing Extensions (AWE) to address
approximately 8 GB of memory for instances that run on Microsoft Windows
2000 Advanced Server, and approximately 32 GB for instances that run on
Microsoft Windows 2000 Datacenter. With AWE, SQL Server can reserve memory
that is not in use for other applications and the operating system. Each
instance that uses this memory; however, must statically allocate the
memory it needs. SQL Server can only use this AWE allocated memory for the
data cache and not for executables, drivers, DLLs, and so forth. For
additional information, click the article number below to view the article
in the Microsoft Knowledge
Base:
283037 Large Memory Support Is Available in Windows 2000
The extended memory size option is available only for Microsoft SQL Server
7.0, Enterprise Edition on the operating system and hardware that supports
the Enterprise Memory Architecture (EMA) feature. For more information
about how to configure your system to enable the EMA feature on a
particular system configuration, refer to your Microsoft Windows NT
documentation.
Some system vendors may provide products for Microsoft Windows NT, version
4.0 or later, so that SQL Server 7.0, Enterprise Edition can use the
extended memory size option. On Intel platforms, SQL Server 7.0 can use a
feature known as PSE36. On Alpha platforms, the feature that allows use of
the extended memory option is Very Large Memory (VLM). For more information
about the availability, installation, and configuration of these products,
contact your system vendor.
Note: To use Address Windowing Extensions (AWE) memory, you must run the
SQL Server 2000 database engine under a Windows account that has been
assigned the Windows lock pages in memory administrative credentials.
back to the top
Support for Operating System Versions
Windows 2000 Server
SQL Server 2000
Normally, both the SQL Server 2000 Enterprise Edition and SQL Server 2000
Developer Edition can use up to 2 GB of physical memory. With the use of
the AWE enable option, SQL Server can use up to 4 GB of physical memory.
NOTE: You cannot allocate more than 4 GB of physical memory to an
application on Windows 2000 Server because Physical Address Extension (PAE)
is not available on Microsoft Windows 2000 Server. Also, you cannot use the
3 GB switch in the Boot.ini file with Windows 2000 Server; however, you can
use the 3 GB switch in the Boot.ini file with Microsoft Windows 2000
Advanced Server or Microsoft Windows Datacenter versions. For additional
information, click the article number below to view the article in the
Microsoft Knowledge Base:
291988 A Description of the 4 GB RAM Tuning Feature and the Physical
Address Extension Switch
back to the top
SQL Server 7.0
SQL Server 7.0 versions can use up to 2 GB of physical memory.
NOTE: The extended memory option in SQL Server 7.0 is not available on
Windows 2000.
Windows 2000 Advanced Server or Windows 2000 Datacenter or Windows Server
2003
The maximum amount of physical memory addressable by a 32-bit addressing
mode is 4 GB. All processors based on the IA-32 architecture that begin
with the Intel Pentium Pro, support a new 36-bit physical addressing mode
known as Physical Address Extension (PAE). PAE allows up to 8 GB of
physical memory on Windows 2000 Advanced Server and up to 32 GB of physical
memory on Windows 2000 Datacenter Server. This is because the tested memory
limit on Windows 2000 Datacenter Server is 32 GB. The PAE mode kernel
requires an Intel architecture processor, Pentium Pro, or later and either
Windows 2000 Advanced Server or Windows 2000 Datacenter. For additional
information, click the article number below to view the article in the
Microsoft
Knowledge Base:
268230 Scaling Out Versus Scaling Up with Intel Physical Addressing
Extensions (PAE)
Note The maximum amount of memory that can be supported on Windows Server
2003 is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32
GB of physical RAM. Windows Server 2003 Datacenter Edition supports 64 GB
of physical RAM by using the Physical Address Extensions (PAE) feature. You
can use the 3 GB switch that is in the Boot.ini file with Microsoft Windows
Server 2003, Microsoft Windows Server 2003 Enterprise Edition, or with
Microsoft Windows Server 2003 Datacenter Edition.
SQL Server 2000
Both SQL Server 2000 Enterprise and SQL Server 2000 Developer Editions can
use the following options:
Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL
Server allows SQL Server 2000 to utilize more than 4 GB memory. Without the
/PAE switch SQL Server can only utilize up to 4 GB of memory.
NOTE: To allow AWE to use the memory range above 16 GB on Windows 2000 Data
Center, make sure that the /3GB switch is not in the Boot.ini file. If the
/3GB switch is in the Boot.ini file, Windows 2000 may not be able to
address any memory above 16 GB correctly.
When you allocate SQL Server AWE memory on a 32 GB system, Windows 2000 may
require at least 1 GB memory to manage AWE.
back to the top
Example
The following example shows how to enable AWE and configure a limit of 6 GB
for the max server memory option: sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO
-or-
-or-Use of the /3GB switch in the Boot.ini file allows SQL Server 2000 to
use up to 3 GB of available memory.
back to the top
SQL Server 7.0
SQL Server 7.0 Enterprise Edition requires the /3GB switch in the Boot.ini
in order to address up to 3 GB of available memory.
NOTE: The extended memory option in SQL Server 7.0 is not available on
Windows 2000 Advanced Server or Windows Datacenter.
Microsoft Windows NT 4.0 Enterprise Edition
SQL Server 2000
SQL Server 2000 Enterprise and Developer Edition requires the /3GB switch
in the Boot.ini file in order to address up to 3 GB of available memory.
NOTE: Windows NT 4.0 Enterprise Edition does not support the AWE memory
architecture model so AWE support is not available.
back to the top
SQL Server 7.0
SQL Server 7.0 Enterprise Edition requires the /3GB switch in the Boot.ini
file in order to address up to 3 GB of available memory.
back to the top
REFERENCES
For additional information, click the article numbers below to view the
articles in the Microsoft Knowledge Base:
170756 Available Switch Options for Windows NT Boot.ini File
171793 Information on Application Use of 4GT RAM Tuning
268363 Intel Physical Addressing Extensions (PAE) in Windows 2000
292934 Windows 2000 Datacenter Server Does Not Locate Memory Greater Than
16 GB
SQL Server 2000 Books Online; topics: "Managing AWE Memory"; "Running
Multiple Instances and Using Failover Clustering"
back to the top
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
I'm running a non-clustered sql environment, and I was
considering the scenario where I could install multiple
instances of sql server onto one "work-horse" server.
I'm concerned about the 2 Gb memory limit. Is this limit
per instance, or per physical machine?
E.g. one quad-processor server, with 6 Gb of memory
could easily accept 2 sql instances each with a 2Gb
memory limit.
Thanks in Advance
CraigHi,
This limitation in Memory / Cpu is instance based. So if you have 2 SQL
server instances you can allocate a maximum of 2 GB RAM for each instance.
FYI,
If your SQL Server edition is Enterprise edition it supports more than 2 GB
RAM for each instance. See books online on "Maximum Capacity specifications"
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"Craig Irvine" <anonymous@.discussions.microsoft.com> wrote in message
news:cd6601c43940$2592e2d0$a601280a@.phx.gbl...
> Hi Everyone,
> I'm running a non-clustered sql environment, and I was
> considering the scenario where I could install multiple
> instances of sql server onto one "work-horse" server.
> I'm concerned about the 2 Gb memory limit. Is this limit
> per instance, or per physical machine?
> E.g. one quad-processor server, with 6 Gb of memory
> could easily accept 2 sql instances each with a 2Gb
> memory limit.
> Thanks in Advance
> Craig
>|||Hi Craig,
The 2GB Memory limitation is per instance and the SQL Server can be
configured to use more memory than 2GB. Please refer to the following
article
http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&Product=sql2k
Excerpt from the KB Article
HOW TO: Configure memory for more than 2 GB in SQL Server
View products that this article applies to.
This article was previously published under Q274750
IN THIS TASK
SUMMARY
Support for Operating System Versions
Microsoft Windows 2000 Server
Microsoft SQL Server 2000
Microsoft SQL Server 7.0
Microsoft Windows 2000 Advanced Server or Microsoft Windows 2000 Datacenter
or Windows Server 2003
Microsoft SQL Server 2000
Microsoft SQL Server 7.0
Microsoft Windows NT 4.0 Enterprise Edition
REFERENCES
SUMMARY
This article describes how to configure SQL Server to use more than 2 GB of
physical memory.
Microsoft SQL Server 7.0 and Microsoft SQL Server 2000 dynamically acquire
and free memory as needed. When you run multiple instances of SQL Server on
a computer, each instance dynamically acquires and frees memory to adjust
for changes in the workload of the instance.
SQL Server 2000 Enterprise Edition introduces support for the use of
Microsoft Windows 2000 Address Windowing Extensions (AWE) to address
approximately 8 GB of memory for instances that run on Microsoft Windows
2000 Advanced Server, and approximately 32 GB for instances that run on
Microsoft Windows 2000 Datacenter. With AWE, SQL Server can reserve memory
that is not in use for other applications and the operating system. Each
instance that uses this memory; however, must statically allocate the
memory it needs. SQL Server can only use this AWE allocated memory for the
data cache and not for executables, drivers, DLLs, and so forth. For
additional information, click the article number below to view the article
in the Microsoft Knowledge
Base:
283037 Large Memory Support Is Available in Windows 2000
The extended memory size option is available only for Microsoft SQL Server
7.0, Enterprise Edition on the operating system and hardware that supports
the Enterprise Memory Architecture (EMA) feature. For more information
about how to configure your system to enable the EMA feature on a
particular system configuration, refer to your Microsoft Windows NT
documentation.
Some system vendors may provide products for Microsoft Windows NT, version
4.0 or later, so that SQL Server 7.0, Enterprise Edition can use the
extended memory size option. On Intel platforms, SQL Server 7.0 can use a
feature known as PSE36. On Alpha platforms, the feature that allows use of
the extended memory option is Very Large Memory (VLM). For more information
about the availability, installation, and configuration of these products,
contact your system vendor.
Note: To use Address Windowing Extensions (AWE) memory, you must run the
SQL Server 2000 database engine under a Windows account that has been
assigned the Windows lock pages in memory administrative credentials.
back to the top
Support for Operating System Versions
Windows 2000 Server
SQL Server 2000
Normally, both the SQL Server 2000 Enterprise Edition and SQL Server 2000
Developer Edition can use up to 2 GB of physical memory. With the use of
the AWE enable option, SQL Server can use up to 4 GB of physical memory.
NOTE: You cannot allocate more than 4 GB of physical memory to an
application on Windows 2000 Server because Physical Address Extension (PAE)
is not available on Microsoft Windows 2000 Server. Also, you cannot use the
3 GB switch in the Boot.ini file with Windows 2000 Server; however, you can
use the 3 GB switch in the Boot.ini file with Microsoft Windows 2000
Advanced Server or Microsoft Windows Datacenter versions. For additional
information, click the article number below to view the article in the
Microsoft Knowledge Base:
291988 A Description of the 4 GB RAM Tuning Feature and the Physical
Address Extension Switch
back to the top
SQL Server 7.0
SQL Server 7.0 versions can use up to 2 GB of physical memory.
NOTE: The extended memory option in SQL Server 7.0 is not available on
Windows 2000.
Windows 2000 Advanced Server or Windows 2000 Datacenter or Windows Server
2003
The maximum amount of physical memory addressable by a 32-bit addressing
mode is 4 GB. All processors based on the IA-32 architecture that begin
with the Intel Pentium Pro, support a new 36-bit physical addressing mode
known as Physical Address Extension (PAE). PAE allows up to 8 GB of
physical memory on Windows 2000 Advanced Server and up to 32 GB of physical
memory on Windows 2000 Datacenter Server. This is because the tested memory
limit on Windows 2000 Datacenter Server is 32 GB. The PAE mode kernel
requires an Intel architecture processor, Pentium Pro, or later and either
Windows 2000 Advanced Server or Windows 2000 Datacenter. For additional
information, click the article number below to view the article in the
Microsoft
Knowledge Base:
268230 Scaling Out Versus Scaling Up with Intel Physical Addressing
Extensions (PAE)
Note The maximum amount of memory that can be supported on Windows Server
2003 is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32
GB of physical RAM. Windows Server 2003 Datacenter Edition supports 64 GB
of physical RAM by using the Physical Address Extensions (PAE) feature. You
can use the 3 GB switch that is in the Boot.ini file with Microsoft Windows
Server 2003, Microsoft Windows Server 2003 Enterprise Edition, or with
Microsoft Windows Server 2003 Datacenter Edition.
SQL Server 2000
Both SQL Server 2000 Enterprise and SQL Server 2000 Developer Editions can
use the following options:
Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL
Server allows SQL Server 2000 to utilize more than 4 GB memory. Without the
/PAE switch SQL Server can only utilize up to 4 GB of memory.
NOTE: To allow AWE to use the memory range above 16 GB on Windows 2000 Data
Center, make sure that the /3GB switch is not in the Boot.ini file. If the
/3GB switch is in the Boot.ini file, Windows 2000 may not be able to
address any memory above 16 GB correctly.
When you allocate SQL Server AWE memory on a 32 GB system, Windows 2000 may
require at least 1 GB memory to manage AWE.
back to the top
Example
The following example shows how to enable AWE and configure a limit of 6 GB
for the max server memory option: sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO
-or-
-or-Use of the /3GB switch in the Boot.ini file allows SQL Server 2000 to
use up to 3 GB of available memory.
back to the top
SQL Server 7.0
SQL Server 7.0 Enterprise Edition requires the /3GB switch in the Boot.ini
in order to address up to 3 GB of available memory.
NOTE: The extended memory option in SQL Server 7.0 is not available on
Windows 2000 Advanced Server or Windows Datacenter.
Microsoft Windows NT 4.0 Enterprise Edition
SQL Server 2000
SQL Server 2000 Enterprise and Developer Edition requires the /3GB switch
in the Boot.ini file in order to address up to 3 GB of available memory.
NOTE: Windows NT 4.0 Enterprise Edition does not support the AWE memory
architecture model so AWE support is not available.
back to the top
SQL Server 7.0
SQL Server 7.0 Enterprise Edition requires the /3GB switch in the Boot.ini
file in order to address up to 3 GB of available memory.
back to the top
REFERENCES
For additional information, click the article numbers below to view the
articles in the Microsoft Knowledge Base:
170756 Available Switch Options for Windows NT Boot.ini File
171793 Information on Application Use of 4GT RAM Tuning
268363 Intel Physical Addressing Extensions (PAE) in Windows 2000
292934 Windows 2000 Datacenter Server Does Not Locate Memory Greater Than
16 GB
SQL Server 2000 Books Online; topics: "Managing AWE Memory"; "Running
Multiple Instances and Using Failover Clustering"
back to the top
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
Monday, March 12, 2012
multiple databases
Dear support,
I have a SQL server with multiple databases( say Database A to E) on a same instance.
This is my scenario:
I have an application, say application A, which read/writes to Database A, but also read-only data from B,C,D and E database. Currently, I have given a user, say User A, full access right(both read/write) on all these databases.
If I want to tight up the security by enforcing read/write on the A database, but read only on the B,C,D,E database for user A by the use of user defined role, is it possible? This way, any new recruit is hired, I will just add the role to the new user. However, I have trouble trying to define a database role, which will give me options to assign access rights or permissions on multiple databases.
Any suggestions?
Thank you!
MartinHi
If you access the read only database through views, stored procedures or
user defined functions then the owner of the view/stored procedure/UDF will
require the access.
See "Using Ownership Chains" in Books Online:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\adm
insql.chm::/ad_security_4iyb.htm
If you can make the database read only then this will make everything more
secure.
John
"Martin" <anonymous@.discussions.microsoft.com> wrote in message
news:EC7A94A0-5283-475B-9651-2360DA0DF075@.microsoft.com...
> Dear support,
> I have a SQL server with multiple databases( say Database A to E) on a
same instance.
> This is my scenario:
> I have an application, say application A, which read/writes to Database A,
but also read-only data from B,C,D and E database. Currently, I have given
a user, say User A, full access right(both read/write) on all these
databases.
> If I want to tight up the security by enforcing read/write on the A
database, but read only on the B,C,D,E database for user A by the use of
user defined role, is it possible? This way, any new recruit is hired, I
will just add the role to the new user. However, I have trouble trying to
define a database role, which will give me options to assign access rights
or permissions on multiple databases.
> Any suggestions?
> Thank you!
> Martin
>|||Thanks John!
I have a SQL server with multiple databases( say Database A to E) on a same instance.
This is my scenario:
I have an application, say application A, which read/writes to Database A, but also read-only data from B,C,D and E database. Currently, I have given a user, say User A, full access right(both read/write) on all these databases.
If I want to tight up the security by enforcing read/write on the A database, but read only on the B,C,D,E database for user A by the use of user defined role, is it possible? This way, any new recruit is hired, I will just add the role to the new user. However, I have trouble trying to define a database role, which will give me options to assign access rights or permissions on multiple databases.
Any suggestions?
Thank you!
MartinHi
If you access the read only database through views, stored procedures or
user defined functions then the owner of the view/stored procedure/UDF will
require the access.
See "Using Ownership Chains" in Books Online:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\adm
insql.chm::/ad_security_4iyb.htm
If you can make the database read only then this will make everything more
secure.
John
"Martin" <anonymous@.discussions.microsoft.com> wrote in message
news:EC7A94A0-5283-475B-9651-2360DA0DF075@.microsoft.com...
> Dear support,
> I have a SQL server with multiple databases( say Database A to E) on a
same instance.
> This is my scenario:
> I have an application, say application A, which read/writes to Database A,
but also read-only data from B,C,D and E database. Currently, I have given
a user, say User A, full access right(both read/write) on all these
databases.
> If I want to tight up the security by enforcing read/write on the A
database, but read only on the B,C,D,E database for user A by the use of
user defined role, is it possible? This way, any new recruit is hired, I
will just add the role to the new user. However, I have trouble trying to
define a database role, which will give me options to assign access rights
or permissions on multiple databases.
> Any suggestions?
> Thank you!
> Martin
>|||Thanks John!
Friday, March 9, 2012
Multiple connections in a CLR trigger
Hello
I think the “CLR Triggers for SQL Server 2005” article on
http://aspalliance.com/1273_CLR_Triggers_for_SQL_Server_2005.all
may be helpful in this discussion.
This popular white paper is written by a software engineer from our organization Mindfire Solutions (http://www.mindfiresolutions.com).
I hope you find it useful!
Cheers,
Byapti
Is there nay way that it is possible to connect to another database within a trigger…
My scenario is that an update in a table in database X triggers several updates in a table in Database Y. The databases are on the same SQL Server
Thanks for any ideas, solutions or hints you can give me..!!!
Hi
I developed one solution like that Try Referencing server Y in server X wthi sp_addlinkedserver and then directly try to make the update like this
update servery.database.dbo.tabley set yfield=xfield
from serverx.database.dbo.tablex
|||I tried the suggested function but i didn’t help and I still didn’t figure out how to make it work
Cheers
|||Did you try to just instatiate a new sqlconnection object using a normal connection string ? Make sure that you will need to grant a special trust level (external) to the assembly to make it work.Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||
I did try to instantiate a new sqlconnection object. But I sadly I don't know about the trust level which you are talking about... Could you give me further information have I could grant the trust level to the assembly? It would be highly appreciated
Thanks
|||This would be a quick sample:
// Create Table Table1
//(
// SomeInt INT
//)
//INSERT INTO Table1 VALUES (1)
//EXEC sp_configure 'clr enabled' , '1'
//ALTER DATABASE Northwind SET TRUSTWORTHY ON
//RECONFIGURE
// Enter existing table or view for the target and uncomment the attribute line
[Microsoft.SqlServer.Server.SqlTrigger(Name = "Trigger1", Target = "Table1", Event = "FOR INSERT")]
public static void Trigger1()
{
SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=master;Integrated Security=True");
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "Select 1";
conn.Open();
cmd.ExecuteNonQuery();
}
Jens K. Suessmeyer.
http://www.sqlserver2005.de
I think the “CLR Triggers for SQL Server 2005” article on
http://aspalliance.com/1273_CLR_Triggers_for_SQL_Server_2005.all
may be helpful in this discussion.
This popular white paper is written by a software engineer from our organization Mindfire Solutions (http://www.mindfiresolutions.com).
I hope you find it useful!
Cheers,
Byapti
Wednesday, March 7, 2012
Multiple Columns of Fact pointing to same dimension
Hi All,
Here is my scenario.
My fact table has the following
TransactionFiscalWeekId, BilledFiscalWeekId, PaymentFiscalWeekId, Amount
The dimension table has
FiscalYear,FiscalQuarter,FiscalMonth,Fis
calWeek,FiscalWeekId(lowest grain)
Now i need to have dimension built individually on each of the fact table co
lumns (TransactionFiscalWeekId, BilledFiscalWeekId, PaymentFiscalWeekId). Th
e three id(s) will have different values for the same row.
To implement this i need to build three dimensions on the same relational ta
ble. Basically In dimension build editor pick up the same table thrice and b
uild three individual dimensions. The editor will now have one fact table, a
nd the dimension table in t
hree different aliases.
Join as follows
Fact.TransactionFiscalWeekId = dimAlias1.FiscalWeekId
Fact.BilledFiscalWeekId= dimAlias2.BilledFiscalWeekId
Fact.PaymentFiscalWeekId= dimAlias3.PaymentFiscalWeekId
Questions.
1. Is there any way for me to use a single shared dimension FiscalPeriod and
achieve the same functionality?
2. Any better alternative design for the above functionality?
Thanks a lot!!
LalithaYou can use views to reference the same relational table more than once
Ray Higdon MCSE, MCDBA, CCNA
--
"Lalitha" <lalithas512@.hotmail.com> wrote in message
news:08F4F84C-CD32-42FF-9F22-B9A5DB42F2C5@.microsoft.com...
> Hi All,
> Here is my scenario.
> My fact table has the following
> TransactionFiscalWeekId, BilledFiscalWeekId, PaymentFiscalWeekId, Amount
> The dimension table has
> FiscalYear,FiscalQuarter,FiscalMonth,Fis
calWeek,FiscalWeekId(lowest grain)
> Now i need to have dimension built individually on each of the fact table
columns (TransactionFiscalWeekId, BilledFiscalWeekId, PaymentFiscalWeekId).
The three id(s) will have different values for the same row.
> To implement this i need to build three dimensions on the same relational
table. Basically In dimension build editor pick up the same table thrice and
build three individual dimensions. The editor will now have one fact table,
and the dimension table in three different aliases.
> Join as follows
> Fact.TransactionFiscalWeekId = dimAlias1.FiscalWeekId
> Fact.BilledFiscalWeekId= dimAlias2.BilledFiscalWeekId
> Fact.PaymentFiscalWeekId= dimAlias3.PaymentFiscalWeekId
> Questions.
> 1. Is there any way for me to use a single shared dimension FiscalPeriod
and achieve the same functionality?
> 2. Any better alternative design for the above functionality?
> Thanks a lot!!
> Lalitha|||Thanks for the response.
In relational tables part, i am clear. we can just form three different dime
nsions based on same underlying relational table.
But My idea is to use a single shared dimension.
The reason being a change in Fiscal hierarchy will involve change in one pla
ce alone then.
If i have three separate dimensions, then all three has to be changed.
Can i do something like three different private virtual dimensions
using a single shared dimension Fiscal Period?
Thanks
Lalitha
Here is my scenario.
My fact table has the following
TransactionFiscalWeekId, BilledFiscalWeekId, PaymentFiscalWeekId, Amount
The dimension table has
FiscalYear,FiscalQuarter,FiscalMonth,Fis
calWeek,FiscalWeekId(lowest grain)
Now i need to have dimension built individually on each of the fact table co
lumns (TransactionFiscalWeekId, BilledFiscalWeekId, PaymentFiscalWeekId). Th
e three id(s) will have different values for the same row.
To implement this i need to build three dimensions on the same relational ta
ble. Basically In dimension build editor pick up the same table thrice and b
uild three individual dimensions. The editor will now have one fact table, a
nd the dimension table in t
hree different aliases.
Join as follows
Fact.TransactionFiscalWeekId = dimAlias1.FiscalWeekId
Fact.BilledFiscalWeekId= dimAlias2.BilledFiscalWeekId
Fact.PaymentFiscalWeekId= dimAlias3.PaymentFiscalWeekId
Questions.
1. Is there any way for me to use a single shared dimension FiscalPeriod and
achieve the same functionality?
2. Any better alternative design for the above functionality?
Thanks a lot!!
LalithaYou can use views to reference the same relational table more than once
Ray Higdon MCSE, MCDBA, CCNA
--
"Lalitha" <lalithas512@.hotmail.com> wrote in message
news:08F4F84C-CD32-42FF-9F22-B9A5DB42F2C5@.microsoft.com...
> Hi All,
> Here is my scenario.
> My fact table has the following
> TransactionFiscalWeekId, BilledFiscalWeekId, PaymentFiscalWeekId, Amount
> The dimension table has
> FiscalYear,FiscalQuarter,FiscalMonth,Fis
calWeek,FiscalWeekId(lowest grain)
> Now i need to have dimension built individually on each of the fact table
columns (TransactionFiscalWeekId, BilledFiscalWeekId, PaymentFiscalWeekId).
The three id(s) will have different values for the same row.
> To implement this i need to build three dimensions on the same relational
table. Basically In dimension build editor pick up the same table thrice and
build three individual dimensions. The editor will now have one fact table,
and the dimension table in three different aliases.
> Join as follows
> Fact.TransactionFiscalWeekId = dimAlias1.FiscalWeekId
> Fact.BilledFiscalWeekId= dimAlias2.BilledFiscalWeekId
> Fact.PaymentFiscalWeekId= dimAlias3.PaymentFiscalWeekId
> Questions.
> 1. Is there any way for me to use a single shared dimension FiscalPeriod
and achieve the same functionality?
> 2. Any better alternative design for the above functionality?
> Thanks a lot!!
> Lalitha|||Thanks for the response.
In relational tables part, i am clear. we can just form three different dime
nsions based on same underlying relational table.
But My idea is to use a single shared dimension.
The reason being a change in Fiscal hierarchy will involve change in one pla
ce alone then.
If i have three separate dimensions, then all three has to be changed.
Can i do something like three different private virtual dimensions
using a single shared dimension Fiscal Period?
Thanks
Lalitha
Monday, February 20, 2012
Multiple authentication methods in scale-out deployment
Will SSRS 2008 support multiple authentication methods in a scale-out deployment (intranet / extranet scenario)? Books Online (July CTP) seems inconsistent in addressing this.
In the entry Server Architecture (Reporting Services) BOL states:
If your deployment requires multiple authentication providers, you can configure a scale-out deployment. For more information, see Planning for Scale-Out Deployment (Reporting Services).
When forwarding to the indicated link Planning for Scale-Out Deployment (Reporting Services), BOL states:
Report server instances in a scale-out deployment must have the same type of authentication extension. If you created a custom authentication extension, you must deploy and use that extension on all nodes.
Because of this restriction, you cannot configure a scale-out deployment to support simultaneous intranet and extranet access to the same report server database through separate report server instances where each instance is configured for different authentication types and authorization rules.
Which is it?
Regards,
Scott
It should be the second one (not supported). I will forward your feedback to the documentation folks.
Multiple authentication providers is under consideration for a post-2008 release.
Multiple authentication methods in scale-out deployment
Will SSRS 2008 support multiple authentication methods in a scale-out deployment (intranet / extranet scenario)? Books Online (July CTP) seems inconsistent in addressing this.
In the entry Server Architecture (Reporting Services) BOL states:
If your deployment requires multiple authentication providers, you can configure a scale-out deployment. For more information, see Planning for Scale-Out Deployment (Reporting Services).
When forwarding to the indicated link Planning for Scale-Out Deployment (Reporting Services), BOL states:
Report server instances in a scale-out deployment must have the same type of authentication extension. If you created a custom authentication extension, you must deploy and use that extension on all nodes.
Because of this restriction, you cannot configure a scale-out deployment to support simultaneous intranet and extranet access to the same report server database through separate report server instances where each instance is configured for different authentication types and authorization rules.
Which is it?
Regards,
Scott
It should be the second one (not supported). I will forward your feedback to the documentation folks.
Multiple authentication providers is under consideration for a post-2008 release.
Subscribe to:
Posts (Atom)