The Changes of SSMS from SQL Server 2005 to SQL Server 2012


Summary of Changes:

1. The node "Full-Text Search" in SSMS 2005 Object Explorer no longer exists in SQL Server 2008 and upper anymore. This node was designed as a convenience way to start/stop the FTS service, and it is redundant with "SQL Server Full-Text Search" service in SQL Server Configuration Manager. It has been removed from Object Explorer in SSMS from SQL Server 2008 and onward. The FTS Service name also has been modified sightly to "SQL Full-text Filter Daemon Launcher" from SQL Server 2008. To create a Full-Text Catalog in SQL Server 2008 and onward, you need to go to a database, then Storage.

2. The items in the green box are new features for that particular version.

3. There are no differences b/t the object explorer in SQL Server 2008 and that in 2008 R2.

4. SQL Mail has been discontinued in SQL Server 2012.

Where is the Performance Log and Alerts?

1.     Windows 2000, Windows XP, Windows Server 2000/2003/2008

Performance Logs and Alerts, a service introduced in Windows 2000, improves the logging and alert capabilities that were provided in Windows NT 4.0. Logging is used for detailed analysis and record-keeping purposes. Retaining and analyzing log data collected over a period of several months can be helpful for capacity and upgrade planning.

Windows 2000 provides two types of performance-related logs—counter logs and trace logs, and an alerting function.

For more information, see http://technet.microsoft.com/en-us/library/cc938554.aspx

2.     Windows Vista/7/8/8.1, Windows Server 2008 R2/2012

Starting from Windows Vista, Windows 7, Windows Server 2008 R2, and Windows Server 2012, Windows Performance Monitor is a Microsoft Management Console (MMC) snap-in that provides tools for analyzing system performance. From a single console, you can monitor application and hardware performance in real time, customize what data you want to collect in logs, define thresholds for alerts and automatic actions, generate reports, and view past performance data in a variety of ways.

Windows Performance Monitor combines the functionality of previous stand-alone tools including Performance Logs and Alerts (PLA), Server Performance Advisor (SPA), and System Monitor. It provides a graphical interface for the customization of Data Collector Sets and Event Trace Sessions.

Windows Performance Monitor performs data collection and logging using Data Collector Sets.

For more information, see http://technet.microsoft.com/en-us/library/cc749154.aspx

3.     How to create a performance baseline and a performance alert under the new interface?

See http://technet.microsoft.com/en-us/library/cc722414.aspx for details

Changes of SQL Server Agent User Group from SQL Server 2005 to SQL Server 2014 (the same for SQL Server service)

In SQL Server 2005, when SQL Server is installed, a group called SQLServer2005SQLAgentUser$MSSQLSERVER or SQLServer2005SQLAgentUser$ServerName$InstanceName is created. You can find these groups in Local Groups. If we need more granular control on the permission of the account running SQL Server Agent, we can place the account into this SQL Server Agent user group and assign this group with necessary minimum permissions.

In SQL Server 2008 and 2008R2, when SQL Server is installed, the group name has been changed to SQLServerSQLAgentUser$ServerName$MSSQLSERVER or SQLServerSQLAgentUser$ServerName$InstanceName.

In SQL Server 2012 and 2014 in the Windows Server 2008 R2 or Windows 7/8 environments, a virtual account using the instance name as the service name is used, in the format NT SERVICE\<SERVICENAME>. Services that run as virtual accounts access network resources by using the credentials of the computer account in the format <domain_name>\<computer_name>$. In my case, SQL Server 2012 was installed as the default instance on Windows 8.1, so the default SQL Server Agent account is NT Service\SQLSERVERAGENT. On my VM for the 2014 tabular instance, it is NT Service\SQLAGENT$SQL2014Tabular. Note that this virtual account is not visible in the list of Local Users or Groups. 

Bring Chaos to Order - A Collection of SQL Server Sample Databases and Sample Project Files from SQL Server 2000 to SQL Server 2012

Note 1: The links on the Microsoft websites are only valid on 3/13/2014.
Note 2: I have also created folders for different versions of SQL Server on My Google Docs.
Note 3: Although this document contains the major sample databases and projects, there are additional samples. You can find more samples at http://sqlserversamples.codeplex.com/.

1.     SQL Server 2000 (My Google Doc Location: https://drive.google.com/#folders/0B0NWdGCu7BBXZjRUZnlYMTZlV00)


I do not have SQL Server 2000 installed on my PC. For consistency, I have moved the four files – northwind.mdf, northwind.ldf, pubs.mdf, and pubs.ldf to the same location as the files for SQL Server 2005 at

D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data)



(D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data)

(The default path is C:\Program Files\Microsoft SQL Server\90\Samples\)

For consistency, I have installed it to:

D:\Program Files\Microsoft SQL Server\90\Samples

3.     SQL Server 2008 (SR4 - Sample Refresh 4) (https://drive.google.com/#folders/0B0NWdGCu7BBXYkxMX1lwVS1UcEE)


D:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA

Note: When you execute the AdventureWorks2008_SR4.exe, the executable not only installs the SQL Server 2008 version for the three AW databases, but it also installs the 2005 version of the three AW databases. Interestingly, the AW databases for the 2005 and 2008 versions are different. But the differences are minor after inspection by using SQL Compare (Red Gate). For instance,
·       AdventureWorks for 2005 uses the collation of Latin1_General_CS_AS for the nvarchar columns, where AdventureWorks for 2008 employs SQL_Latin1_General_CP1_CI_AS for the collation
·       AdventureWorks for 2005 has some comments which are not in the 2008 version
·       There are some minor code changes.


D:\Program Files\Microsoft SQL Server\100\Samples

c.      No separate samples for Report Builder 2.0 with SQL Server 2008.

For Report Builder 2.0 sample reports and tutorials, you need only install AdventureWorks2008.



D:\Program Files\Microsoft SQL Server\100\Samples


D:\Program Files\Microsoft SQL Server\MSSQL10_50.R2\MSSQL\DATA


Although it is named SQL2008R2, it is actually SQL2008 samples. However, if you have other 2008R2 samples like the ones in the next list are installed, the samples for SQL Server 2008 have to be upgraded in the 2008R2 environment. Both the 2008 and 2008R2 non-DB sample files are installed to:

D:\Program Files\Microsoft SQL Server\100\Samples

d.     Nevertheless, there are some additional samples for SQL Server 2008 R2:
                                          i.     SSRS sample reports: AdventureWorks Sample Reports 2008 R2 (AdventureWorks Sample Reports 2008 R2.zip), unzipped to:

D:\Program Files\Microsoft SQL Server\100\Samples\AdventureWorks 2008 R2 Sample Reports

D:\Program Files\Microsoft SQL Server\100\Samples\AdventureWorks 2008R2 Analysis Services Project\AdventureWorks 2008R2 Analysis Services Project\enterprise

                                       iii.     SSIS Samples for SQL Server 2008R2 RTM (SQL2008R2.Integration_Services.Samples.x64.msi), installed to

D:\Program Files\Microsoft SQL Server\100\Samples\Integration Services


They are just some rdl files.



This database needs to be installed to SQL Azure remotely, not locally.

a.          Databases:
i.                AdventureWorks2012,
ii.              AdventureworksDW2012,
iii.            AdventureworksLT2012,
b.         Other Samples and Tutorials
ii.              Analysis Services Tutorial SQL Server 2012,
e.          SQL Azure Databases
i.                 AdventureWorks2012ForWindowsAzureSQLDatabase
ii.               OlympicsData workbook - Excel, SSIS, Azure sample

32-bit Database vs. 64-bit Database?

Problem:

In the process of organizing the SQL Server sample databases and projects, I often see different download files for 32-bit and 64-bit (e.g., AdventureWorksDB.msi vs.  AdventureWorksDB_x64.msi). This makes me think: OK, how do I know the version of a database? how do I know if a database is a 32-bit or 64-bit?

Solution:

1. For the database version

(1) Turn on TRACEON flag 3604, run DBCC DBINFO:

DBCC TRACEON (3604);  
GO 

 DBCC DBINFO
GO 

 DBCC TRACEOFF (3604);

(2) In the output, find dbi_version (THE CURRENT VERSION) and dbi_createversion

 (3) Know the database version number
  • SQL Server 7.0 databases have version number 515
  • SQL Server 2000 databases have version number 539
  • SQL Server 2005 databases have version number 611/612
  • SQL Server 2008 databases have version number 655
  • SQL Server 2008R2 databases have version number 663
  • SQL Server 2012 databases have version number 706
  • SQL Server 2014 databases have version number 782
  • SQL Server 2016 databases have version number 829
2. For the database 32-bit vs. 64-bit property, see the post below:

http://stackoverflow.com/questions/2526375/32bit-to-64bit-sql-server-2008-database-conversion

The key point is that there is no such concept at the database level. The difference between 32-bit and 64-bit is only applicable to the server instance. However, attaching a so-called 64-bit database (meaning it was created on a 64-bit instance) to a 32-bit instance to the same or higher version of server instance is fine.

But I am kind of understanding why MS folks use different file names for different architecture machines: The default installation path depends on the architecture of processor. So, to be aligned with other installation files, they decide to use different names for the same thing (i.e., the database).
Subscribe to: Posts ( Atom )