:::: MENU ::::

Posts Categorized / System Administration

  • Nov 26 / 2008
  • 0
dbDigger, Security and Permissions, System Administration

password policies for SQL Server login for Windows Server 2003

On a computer running Windows Server 2003 or later hosting a SQL Server instance, which of the following password policies are checked for a SQL Server login when CHECK_POLICY is ON and CHECK_EXPIRATION is OFF?

  • Enforce password history
  • Minimum password age
  • Minimum password length
  • Password must meet complexity requirements

There is nothing written specifically indicating which policies are checked by CHECK_POLICY. The answer was determined by experimentation (SQL Server 2005 SP2 on Vista). A SQL Server login was created, then a query window was opened with the connection made using that login. ALTER LOGIN WITH PASSWORD = ” OLD_PASSWORD = ” was run when various policies were applied.

Password policy – http://msdn.microsoft.com/en-us/library/ms182717.aspx

Note: The Question is taken from SQLServercentral.com.

  • Nov 24 / 2008
  • 1
dbDigger, Monitoring and Analysis, System Administration

How to view virtual memory consumed by processes

Usually task manager is used to view the memory and CPU usage by different running processes. By default physical memory and CPU usage is shown under processes task in task manager.

So how to get virtual memory usage statistics for any process of windows OS? To get view of virtual memory statistics along with physical memory usage for processes.
Go to ‘view’ menu
click ‘select columns…’ option
Select ‘Virtual Memory Size’
and click ‘OK’ button

Then you also get virtual memory statistics for each process.

  • Nov 04 / 2008
  • 0
dbDigger, Performance Tunning and Optimization, System Administration

What is Address Windowing Extensions (AWE)

Address Windowing Extensions (AWE) is a set of extensions that allows an application to quickly manipulate physical memory greater than 4GB. The process of mapping an application’s virtual address space to physical memory under Address Windowing Extensions (AWE) is known as “windowing,” and is similar to the “overlay” concept of other environments. Certain data-intensive applications, such as database management systems and scientific and engineering software, need access to very large caches of data. Using the Address Windowing Extensions (AWE) API, the application can map the virtual window to any one of the physical regions. The application can reserve more than one virtual address space and map it to any of the allocated regions of physical memory, as long as the number of bytes reserved in the virtual address space matches that of the physical memory region. Address Windowing Extensions (AWE) usage is coded into the application itself. An application must have the Lock Pages in Memory privilege to use Address Windowing Extensions (AWE). To use AWE, you must enable the physical address extensions by adding the /PAE switch to your boot.ini file. Starting with Windows 2003 SP1, Windows automatically enables PAE on boot when more than 2 GB of RAM is installed. Some major benefits of AWE are:

  • A small group of new functions is defined to manipulate AWE memory.
  • AWE provides a very fast remapping capability. Remapping is done by manipulating virtual memory tables, not by moving data in physical memory.
  • AWE provides page size granularity appropriate to the processor (for example, 4K on x86), which is more useful to applications than large pages (for example, 2MB or 4MB on x86).
  • Sep 25 / 2008
  • 0
DBA Interview questions, dbDigger, Storage, System Administration

Understanding RAID levels for storage systems

As data is of primary importance for any organization. And DBAs pay utmost attention to make sure the reliability and performance of Relational data. SQL Server databases are configured on several levels of RAID. There are many RAID levels available on modern RAID controllers. Only a subset of these is most useful when configuring a Microsoft SQL Server. Each RAID level has a specific use and benefit. Using the wrong type of RAID level can not only hurt system performance, but also add more cost to your server configuration.
Experts recommend that you never use software arrays on a database server. Use of software arrays requires additional CPU power from Windows in order to calculate which physical disk the data is written to.
In hardware arrays, this overhead is offloaded to a physical PCI, PCIe or PCIx card within the computer (or within the SAN device), which has its own processor and software dedicated to this task.

RAID 1 – Mirror.
A RAID 1 array is most useful for high write files, such as the page file, transaction logs and tempdb database. A RAID 1 array takes two physical disks and creates an exact duplicate of the primary drive on the backup drive. There is no performance gain or loss when using a RAID 1 array. This array can survive a single drive failure without incurring any data loss.

RAID 5 – Redundant Stripe Set.
A RAID 5 array is most useful for high read files such as the database files (mdf and ndf files) and file shares. It is the most cost-effective, high-speed RAID configuration. With a RAID 5 array, there is a performance impact while writing data to the array because a parity bit must be calculated for each write operation performed. For read performance, the basic formula is (n-1)*o where n is the number of disks in the RAID 5 array and o is the number of I/O operations each disk can perform. Note: While this calculation is not perfectly accurate, it is generally considered close enough for most uses. A RAID 5 array can survive a single drive failure without incurring any data loss.

RAID 6 – Double Redundant Stripe Set.
Like a RAID 5 array, a RAID 6 array is most useful for high read files such as the database and file shares. With RAID 6, there is also a performance impact while writing data to the array because two parity bits must be calculated for each write operation performed. The same basic formula is used to calculate the potential performance of the drives (n-2)*o. A RAID 6 array can survive two drive failures without incurring any data loss.Because of the dual parity bits with RAID 6, it is more expensive to purchase than a RAID 5 array. However, RAID 6 offers a higher level of protection than RAID 5. When choosing between RAID 5 and RAID 6, consider the length of time to rebuild your array, potential loss of a second drive during that rebuild time, and cost.

RAID 10 – Mirrored Strip Sets.
A RAID 10 array is most useful for high read or high write operations. RAID 10 is extremely fast; however, it is also extremely expensive (compared to the other RAID levels available). In basic terms, a RAID 10 array is several RAID 1 arrays stripped together for performance. As with a RAID 1 array, as data is written to the active drive in the pair, it is also written to the secondary drive in the pair. A RAID 10 array can survive several drive failures so long as no two drives in a single pair are lost.

RAID 50 – Stripped RAID 5 Arrays.
A RAID 50 array is an extremely high-performing RAID array useful for very high-load databases. This type of array can typically only be done in a SAN environment. Two or more RAID 5 arrays are taken and stripped together and data is then written to the various RAID 5 arrays. While there is no redundancy between RAID 5 arrays, it’s unnecessary because the redundancy is handled within the RAID 5 arrays. A RAID 50 array can survive several drive failures so long as only a single drive per RAID 5 array fails.

Click here to read all posts related to SQL Server storage systems and I/O operations

A part separated by an article of DennyCherry

  • Jul 02 / 2008
  • 0
DBA Interview questions, dbDigger, Hardware and Platform, System Administration

Multi processors and multicore processors systems

Multiprocessor systems contain multiple CPUs that are not on the same chip. Today, multiprocessors are commonly found on the same physical board and connected through a high-speed communication interface. Multiprocessor systems are less complex than multicore systems, because they are essential single chip CPUs connected together. The disadvantage with multiprocessor systems is that they are expensive because they require multiple chips which is more expensive than a single chip solution.

Consult us to explore the Databases. Contact us