posted Oct 16, 2010 2:21 PM by Ricardo Fonseca
[
updated Feb 17, 2011 6:01 PM
]
This post is here to provide a canvas for SQL Server 2008 installation , it fit most of the installation need. and describe brief information on every step on what option to select and why.
We take two perspective in this post the manual installation , step by step with print screen and the automatic installation process with a configuration template file where you specify the default directories and account name and password.
Please note that I for all account names and password are virtual and use only as illustration. It will be up to you to set the values according to account credential that fit the best for your business.
Pre-Requisites
account name : dbadmin account password : YourStrongPasswordInstallation ProcessAs for advise, If anything goes not as indicated, or an error occur during the installation process. Make sur to transfer the information to the person in charge in your organisation.
Manual Installation Step by StepStep 1 * |
Note : Select the setup exe on the root folder, depending on your os subsystem it will know to run the x64 or x32 bits installation process.Step 2
Note : Select the “Installation” menu item on the left panel, then select the “New SQL Server Stand-alone installation or add features to an existing installation” menu item on the right panelStep 3
Note : make sure to have all support requirements checked.Step 4Note : the evaluation key will come automatically as this is a developer edition , just hit next button .
Step 5
Note : click the checkbox “I accept the license terms” option and hit next.Step 6
Note : at this stage , sql server will prompt you to install any requirements files missing , just hit install button.Step 7 Note : at this stage make sure that all support rules are checked and hit the next button
Step 8
Note : at this stage hit Select All button. and then the next button.
Step 9 Note : make sure you select the “default instance” radio button and that you have the instance id set to MSSQLSERVER
Step 10
Note : Space requirements step make sure you have all the requirements set and hit the next button
Step 11-1
Note : Set all account credentials to the dbadmin credential , please refer to Manual Installation / Pre Requisites for details. Choose the startup-type according to the print-screen, Then proceed to the Collation Panel.
Setp 11-2
Note : at the Collation Panel you will need to change the Database Engine and Analysis Services to the collation Latin1_General_CI_AI in order to do so please follow steps indicated on the following print-screen
Step 11-3
Step 11-4
Step 11-5
Note : when both collation are correctly set you can hit the next button
Step 12-1
Note : at this stage you want to set the authentication mode to Mixed Mode. the Password for the system administrator account will be initially set to “YourStrongPassword”. After you will proceed and select dbadmin account as part of the SQL Server administrators. Once this is done you can select the Data Directories panel for advanced configuration
Step 12-2 Note : by default this can stay as it is unless we have setup the disksubsystem otherwise (example in production for maximum performance we have separated the logs files from the data files and tempdb file is also splitted). Therefore this is to be confirmed everytime we setup a new sql server installation. Then proceed to the filestream panel.
Step 12-3
Note : make sure the FileStream option is not checked and hit the next button.
Step 13-1
Note : at this stage, include the dbadmin account as part of the of the users who have administrative permissions for Analysis services. and then select the Data Directories panel.
Step 13-2
Note : by default this can stay as it is unless we have setup the disksubsystem otherwise (example in production for maximum performance we have separated the logs files from the data files and temp files can also be splitted). Therefore this is to be confirmed everytime we setup a new sql server installation. Then you can hit the next button.
Step 14 Note : at this stage make sure to select the radio button Install the native mode default and then hit the next button
Step 15
Note : uncheck all checkboxes and then hit the next button
Step 16
Note : at this stage make sure all installation rules are checked and then hit the Next Button.
Step 17
Note : Review all the options and configuration options that you have selected during the installation process and then hit the install Button.
Step 18
Note : Let the system proceed with installation and ask for person in charge to test the installation
Automatic InstallationAs an alternative to the manual installation the SQL Server installation can be achieved in command line simply using the steps described below :- Proceed to command line
- Proceed to the root directory of the media/folder where is located the SQL Server binaries (e:\ in my case)
- Type the command
- Setup.exe /ConfigurationFile=c:\MSSQLSERVER_Installation_ConfigurationFile.ini /QUIET
- At this point the installation will start in silent mode
- if the installation succeeds you will have a brief information advising you about it
- if the installation fails you will have the detailes on wich step and why,
- in that scenario the report will have to be transmitted to the person in charge
Important Notes:
1. Make sure to change accounts name and password according to your business policy. 2. As for the manual steps described on the section Step 12-2 and 13-2 the configuration file may need to be updated with different data/log/temp locations drives and folder. This need to be addressed prior the installation within the configuration file.
|
posted Sep 11, 2010 8:52 PM by Ricardo Fonseca
[
updated Sep 11, 2010 9:13 PM
]
Following my last post, I summarize here a list of the pros and cons of the different solutions previously discussed. High availability / scalability mode
| Pros
| Cons
|
Replication
- Transactional
- Merge
- Snapshot
- Peer-to-peer
|
[x] Data availability
[x] High scalability
[x] Can be mixed with db mirroring
[x] Good Reporting Solution
[x] Little down time |
[x] No automatic fail over
[x] Possible Data Loss
[x] Latency on secondary server
[x] Adv. Setup
[x] Adv. Troubleshooting
[x] Schema driven
| Log Shipping
|
[x] Data availability
[x] High scalability
[x] Easy Setup (FileSystem)
[x] Easy Troubleshooting
[x] can be mixed with db mirroring
[x] Good Reporting Solution
[x] Little down-time
[x] Same database can have multiple destination server |
[x] No automatic fail over
[x] Latency on secondary server
[x] Possible Data Loss
[x] Need Shared Folder setup
[x] Require Folder Security
| Database Mirroring
|
[x] Automatic failover with witness setup
[x] High availability
[x] Easy Setup
[x] Easy Troubleshooting
[x] No Data Loss
[x] Can be mixed with Log Shipping
|
[x] Data availability with latency only over snapshot database
|
|
posted Sep 11, 2010 8:09 PM by Ricardo Fonseca
[
updated Sep 11, 2010 9:09 PM
]
I have been asked a couple of times now what is the best solution for my environments in terms of availability, scalability. And as my experience grows I tend to answer with the famous "it depends". Why, well because regarding your needs (reporting server , load balance load across multiple server(s), hot fail-over server , data-warehouse server etc...) a solution may fit best for a customer and another one will suit best another customer.
As in the real life, describing the purpose of your secondary server will drive 80% of the requirements to answer correctly the question. The last 20% will go with related business requirements and possibilities in terms of infrastructure and $. I have listed below and describe the architecture for the replication, database mirroring and the log-shipping solutions.
a. Database Replication Transactional / Merge / Snapshot / Peer to peer
Publishing Data and Database Objects When
creating a publication, you choose the tables and other database
objects that you want to publish. You can publish the following
database objects using replication. The database objects within a
replication environment are called "articles".
| Database object | Snapshot replication and
transactional replication | Merge replication |
|---|
Tables | X | X | Partitioned Tables | X | X | Stored Procedures – Definition (Transact-SQL and CLR) | X | X | Stored Procedures – Execution (Transact-SQL and CLR) | X | no | Views | X | X | Indexed Views | X | X | Indexed Views as Tables | X | no | User-Defined Types (CLR) | X | X | User-Defined Functions
(Transact-SQL and CLR) | X | X | Alias Data Types | X | X | Full text indexes | X | X | Schema Objects
(constraints, indexes, user DML triggers, extended properties, and collation) | X | X |
A
replication high availability architecture is defined by 3 majors
elements. The publisher that manage the publication of the data to be
replicated, the distributor that manage the distribution of the
transaction and finally the subscriber which will subscribe to a set of
publisher data objects :
- the publisher manage the objects that that will be available for the subscribers to load (called articles)
- the distributor
implement all the agents jobs that will deal with the transfer ,
historic and the maintenance of the transaction log distribution
between the publisher and their subscribers.
- the subscriber persist the publisher data to an offset location. The susbriber will have the possibility to choose from a list of the publisher publication object list described above. b. Database MirroringArchitecture without witness server
Architecture with witness server
How database mirroring works
The principal and mirror servers communicate and cooperate as partners in a database mirroring session. The two partners perform complementary roles in the session: the principal role and the mirror role.
Database mirroring involves redoing every operation that occurs on the principal database onto the mirror database as quickly as possible.
Redoing
is accomplished by sending a stream of active transaction log records
to the mirror server. Unlike replication, which works at the logical
level, database mirroring works at the level of the physical log
record.
Note :
beginning in SQL Server 2008, the principal server compresses the
stream of transaction log records before sending it to the mirror
server. This log compression occurs in all mirroring sessions. Operating Modes
A database mirroring session runs with either synchronous (high-safety mode) or asynchronous (high-performance mode)
operation. Under asynchronous operation, the transactions commit
without waiting for the mirror server to write the log to disk, which
maximizes performance. Under synchronous operation, a transaction is
committed on both partners, but at the cost of increased transaction
latency. High-safety mode with automatic failover requires a third server instance, known as a witness.
Unlike the two partners, the witness supports automatic failover by
verifying whether the principal server is up and functioning.
Note : the high-performance mode is not available in SQL Server Standard versions.
Transaction Safety and Operating Modes
Whether
an operating mode is asynchronous or synchronous depends on the
transaction safety setting. If you exclusively use SQL Server
Management Studio to configure database mirroring, transaction safety
settings are configured automatically when you select the operation
mode. If you use
Transact-SQL to configure database mirroring, you must understand how
to set transaction safety. Transaction safety is controlled by the
SAFETY property of the ALTER DATABASE statement. On a database that is
being mirrored, SAFETY is either FULL or OFF.
-
If the SAFETY option is set to FULL, database mirroring operation is
synchronous, after the initial synchronizing phase. If a witness is set
in high-safety mode, the session supports automatic failover.
-
If the SAFETY option is set to OFF, database mirroring operation is
asynchronous. The session runs in high-performance mode, and the
WITNESS option should also be OFF.
Log shipping consists of three operations: - Back up the transaction log at the primary server instance.
- Copy the transaction log file to the secondary server instance.
- Restore the log backup on the secondary server instance.
The
log can be shipped to multiple secondary server instances. In such
cases, operations 2 and 3 are duplicated for each secondary server
instance. A
log shipping configuration does not automatically fail over from the
primary server to the secondary server. If the primary database becomes
unavailable, any of the secondary databases can be brought online
manually. Primary Server and Database
The
primary server in a log shipping configuration is the instance of the
SQL Server Database Engine that is your production server. The
primary database is the database on the primary server that you want to
back up to another server. The primary database must use the full or
bulk-logged recovery model; switching the database to simple recovery
will cause log shipping to stop functioning. Secondary Server and Databases
The
secondary server in a log shipping configuration is the server where
you want to keep a warm standby copy of your primary database.
A
secondary server can contain backup copies of databases from several
different primary servers. The secondary database must be initialized
by restoring a full backup of the primary database and the tail log
backup.
|
posted Sep 10, 2010 10:23 PM by Ricardo Fonseca
[
updated Sep 15, 2010 1:14 PM
]
(RAID 5 vs RAID 6 vs RAID 10 )
This follows the first post regarding read / write test comparison
against various RAID Setup. Note that in the use case we tested the
following partitions :
- a RAID 5 Lun Parition with 5 Disks ,
- a RAID 6 Lun Parition with 5 Disks ,
- two different RAID 10 partition on on a lun using 6 Disks and another one using 8 disks.
The server used is a IBM x336 series with 16GB of RAM, windows 2003 x64
installed. The setup raid was local to the machine, the cache
controller was integrated to the machine and its size was 256MB.
Following is the legend table to help you to interpret the different
test cases scenario therefore will you find all the detail of each
scenario that has been tested :
| Test Case | threads# | Block Size (KB) | Out. Request | Duration |
| T1 | 2 | 64 | 1 | 120 seconds |
| T2 | 2 | 64 | 2 | 120 seconds |
| T3 | 2 | 64 | 4 | 120 seconds |
| T4 | 2 | 64 | 8 | 120 seconds |
| T5 | 2 | 64 | 16 | 120 seconds |
| T6 | 2 | 64 | 32 | 120 seconds |
| T7 | 2 | 64 | 64 | 120 seconds |
| T8 | 2 | 64 | 128 | 120 seconds |
A. Result extracted for write performance :
|
|
| T1 | T2 | T3 | T4 | T5 | T6 | T7 | T8 |
1 | RAID6 -
9HD;1T;7200k - | MB/S | 25.84 | 26.84 | 22.28 | 26.73 | 23.07 | 24.42 | 30.58 | 27.22 |
| RAID6 -
9HD;1T;7200k - | IO/S | 413.45 | 429.49 | 356.55 | 427.75 | 369.16 | 390.77 | 489.30 | 435.56 |
2 | RAID5 -
5HD;1T;7200k - | MB/S | 17.61 | 19.51 | 19.62 | 20.91 | 21.00 | 19.05 | 21.32 | 20.20 |
| RAID5 -
5HD;1T;7200k - | IO/S | 281.83 | 312.24 | 313.92 | 334.59 | 336.14 | 304.86 | 341.16 | 323.32 |
3 | RAID10 -
6HD;146Go;15kFC - | MB/S | 66.41 | 70.88 | 73.27 | 68.22 | 61.87 | 62.10 | 71.78 | 74.44 |
| RAID10 -
6HD;146Go;15kFC - | IO/S | 1062.70 | 1134.21 | 1172.37 | 1091.59 | 990.04 | 993.60 | 1148.51 | 1191.04 |
4 | RAID10 -
8HD;300Go;15kFC - | MB/S | 78.41 | 89.17 | 100.07 | 108.70 | 110.07 | 108.10 | 112.21 | 107.55 |
| RAID10 -
8HD;300Go;15kFC - | IO/S | 1254.59 | 1426.80 | 1601.18 | 1739.20 | 1761.27 | 1729.73 | 1795.36 | 1720.81 |
B. Result extracted for read performance :
|
| Type | T1 | T2 | T3 | T4 | T5 | T6 | T7 | T8 |
1 | RAID6 - 9HD;1T;7200k - | MB/S | 21.17 | 39.34 | 62.95 | 86.31 | 102.64 | 111.41 | 119.42 | 119.62 |
| RAID6 - 9HD;1T;7200k - | IO/S | 338.84 | 629.45 | 1007.32 | 1381.07 | 1642.37 | 1782.65 | 1910.72 | 1913.98 |
2 | RAID5 - 5HD;1T;7200k - | MB/S | 15.49 | 26.56 | 35.59 | 42.37 | 44.22 | 43.99 | 44.72 | 44.45 |
| RAID5 - 5HD;1T;7200k - | IO/S | 247.90 | 425.08 | 569.53 | 678.01 | 707.58 | 703.85 | 715.65 | 711.33 |
3 | RAID10 - 6HD;146Go;15kFC - | MB/S | 37.08 | 60.20 | 92.65 | 119.86 | 124.92 | 130.66 | 135.52 | 137.32 |
| RAID10 - 6HD;146Go;15kFC - | IO/S | 593.40 | 963.34 | 1482.50 | 1917.91 | 1998.84 | 2090.68 | 2168.37 | 2197.15 |
4 | RAID10 - 8HD;300Go;15kFC - | MB/S | 41.60 | 76.63 | 118.05 | 174.60 | 192.70 | 189.30 | 180.71 | 185.79 |
| RAID10 - 8HD;300Go;15kFC - | IO/S | 665.60 | 1226.20 | 1888.93 | 2793.66 | 3083.24 | 3028.83 | 2891.40 | 2972.75 |
Result :
As for the interpretation of the output we can clearly see that setup
in RAID 10 is still the more the most efficient in regards our tests
scenarios. For read and write instructions it has demonstraste to be
faster quite significantly for reads and even more for write
operations. |
posted Sep 10, 2010 10:13 PM by Ricardo Fonseca
[
updated Sep 15, 2010 1:14 PM
]
(RAID 5 vs RAID 1E vs RAID 10 )
Every-time, as a consultant DBA, a customer ask me the question which
RAID and why will fit best their needs. I tend to provide my
experiences, my ideas and share but rarely it's enough.
In this blog entry I will try show exactly for one specific machine
what will be the efficiency for each and individual RAID setup in terms
of rough IO/S and MB/s.
Note that the tool that I have used is SQLIO, in a near future I plan
to also re-do the tests with the tool Iometer. What is important to
know is that the situation when I did the tests was ideal. The Raid
Setup had been done then re-done according to the needs and there were
no application and no user connected to the server during the tests.
The server used is a IBM x336 series with 16GB of RAM, windows 2003 x64
installed. The setup raid was local to the machine, the cache
controller was integrated to the machine and its size was 256MB.
Following is the legend table to help you to interpret the different
test cases scenario therefore will you find all the detail of each
scenario that has been tested :
| Test Case | threads# | Block Size (KB) | Out. Request | Duration |
| T1 | 2 | 64 | 1 | 120 seconds |
| T2 | 2 | 64 | 2 | 120 seconds |
| T3 | 2 | 64 | 4 | 120 seconds |
| T4 | 2 | 64 | 8 | 120 seconds |
| T5 | 2 | 64 | 16 | 120 seconds |
| T6 | 2 | 64 | 32 | 120 seconds |
| T7 | 2 | 64 | 64 | 120 seconds |
| T8 | 2 | 64 | 128 | 120 seconds |
A. Result extracted for write performance :
|
|
| T1 | T2 | T3 | T4 | T5 | T6 | T7 | T8 |
| 1 | RAID5 - 4HD;145G;15k - | MB/S | 19.06 | 21.81 | 25.20 | 39.92 | 46.08 | 46.04 | 46.64 | 46.82 |
| RAID5 - 4HD;145G;15k - | IO/S | 305.02 | 349.08 | 408.14 | 638.73 | 737.38 | 736.64 | 746.35 | 749.15 |
| 2 | RAID1E - 4HD;300G;10k - | MB/S | 13.65 | 15.01 | 15.29 | 15.54 | 15.48 | 15.49 | 15.27 | 15.55 |
| RAID1E - 4HD;300G;10k - | IO/S | 218.40 | 240.30 | 244.76 | 248.76 | 247.72 | 247.91 | 244.35 | 248.95 |
| 3 | RAID10 - 4HD;300G;10k - | MB/S | 22.05 | 27.08 | 33.91 | 57.42 | 73.12 | 74.57 | 74.44 | 73.93 |
| RAID10 - 4HD;300G;10k - | IO/S | 352.91 | 433.41 | 542.56 | 918.76 | 1169.92 | 1193.24 | 1191.07 | 1182.93 |
B. Result extracted for write performance :
|
|
| T1 | T2 | T3 | T4 | T5 | T6 | T7 | T8 |
| 1 | RAID5 - 4HD;145G;15k | MB/S | 12.35 | 12.17 | 12.39 | 12.37 | 12.38 | 12.46 | 12.54 | 12.61 |
| RAID5 - 4HD;145G;15k | IO/S | 197.69 | 194.78 | 198.24 | 198.07 | 198.09 | 199.46 | 200.67 | 201.8 |
| 2 | RAID1E - 4HD;300G;10k | MB/S | 8.42 | 8.28 | 8.25 | 8.41 | 8.49 | 8.34 | 8.39 | 8.3 |
| RAID1E - 4HD;300G;10k | IO/S | 134.80 | 132.59 | 132.03 | 134.63 | 135.84 | 133.56 | 134.27 | 132.85 |
| 3 | RAID10 - 4HD;300G;10k | MB/S | 16.64 | 16.58 | 16.70 | 16.49 | 16.55 | 16.56 | 16.72 | 16.76 |
| RAID10 - 4HD;300G;10k | IO/S | 266.36 | 265.36 | 267.35 | 263.97 | 264.81 | 265.09 | 267.56 | 268.18 |
Result : As for the
interpretation of the output we can clearly see that setup in RAID 10
is far the most efficient in regards our tests scenarios. For read and
write instructions it has demonstraste to be faster quite significantly
and that even though in the RAID5 setup we had 15k drive rather than
the 10k when setup in RAID10. |
posted Feb 7, 2009 4:50 PM by Ricardo Fonseca
[
updated Apr 24, 2009 6:13 AM
]
The binary providing multithread database server data loading and comparing structure is available for download.
You can have a descriptions of what is included and download the binary in the following URL:
DataTranscoder
Thank you
Ricardo |
posted Dec 3, 2008 7:43 AM by Ricardo Fonseca
[
updated Dec 19, 2008 5:50 AM
]
The library providing multithread database server stress test is available for download.
you can have a descriptions of what is included and download the Dll in the following URL:
SQLStressTool
Thank you
Ricardo |
posted Nov 30, 2008 10:33 PM by Ricardo Fonseca
[
updated Nov 30, 2008 10:36 PM
]
The 3 libraries that compose the SQLDashboard core framework are available for download.
you can have a descriptions of what is included and download the Dlls in the following URL:
SQLDashBoard
Thank you
Ricardo
|
posted Nov 2, 2008 7:48 PM by Ricardo Fonseca
[
updated Nov 2, 2008 6:53 PM
]
The section Experience and Services are now completed.
The contact section has been completed as well with agent solo and linked in account.
The next section that will be designed will be Portfolio. I will mainly express the majors project that I've worked on and, if possible, provide a video or snapshots of them.
Thank you.
Ricardo
|
posted Oct 16, 2008 9:37 PM by Ricardo Fonseca
[
updated Oct 23, 2008 10:02 AM
]
The doors are open ! The first version, not fully completed, of the website is online. It responds to www.databaseskills.net .I upload the site information into my Agent Solo and Linked In account . This should bring me a lot of consulting opportunities. I will keep this blog up to date for new announcement and interesting information that can be shared among us.Thank youRicardo |
|
