Advertisements

SQL Error – SQL Server Installation fails | Attributes do not match. Present attributes (Directory, Compressed, NotContentIndexed)…

January 9, 2018 Leave a comment

 
Few days back I got this query from a developer who was trying to install SQL Server, it was not getting installed and was giving below error:

Microsoft.SqlServer.Configuration.Sco.DirectoryAttributesMissmatch: Attributes do not match. Present attributes (Directory, Compressed, NotContentIndexed) , included attributes (0), excluded attributes (Compressed, Encrypted).


 

–> RCA: As the above error message mentions that the Directory is Compressed, it gives you an idea that the SQL Server installation is not supported on compressed drives. So, if you are installing SQL Server on a drive which has compression enabled it will give you this error.
 

–> Fix: You have to check the Drive properties and see if Compression is enabled, like shown below and un-compress it by un-checking the option.


Advertisements

Seeking response on: LDAP Authentication with SQL Server 2017 on Linux

January 8, 2018 1 comment

 
Few days back I got an email from one of the reader of this blog “Amit Bhatt”. As I haven’t worked much with the AD/LDAP stuff, thus I thought to throw this question to you guys thru this blog post. I also feel this may also help other Developers/DBAs hunting for similar stuff.
 

Here he goes:

We have installed SQL Server 2017 on Linux server. I am able to connect SQL Server locally as well remotely but with local user access.

I have my AD account created and have AD server information. Can you please help me how can I connect using my AD account to SQL Server 2017 on Linux remotely?

I heard something about LDAP Authentication. Is it possible to use this concept without adding Linux server to AD domain? Our security team does not allow to add Linux server in Active Directory group.

I am stucked here since last many days, requesting assistance on urgent basis.

 

Please provide your suggestion on the comment section below.
 

–> Responses:

1. To support AD Authentication, SQL depends on SSSD, SPN and a keytab file which have the required tokens to talk to AD. Without these in place SQL cannot talk to AD which is necessary for AD authentication.
Ref: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication

2. If you are looking to login to SQL Server on linux with Windows authentication the linux server should be joined to the domain. If the server cannot be added to domain then SQL authentication is the way to go. The below link has more details to configure windows authentication, https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication


 

–> Videos on Linux:

1. Create a Linux VM on Azure

2. Install SQL Server on Linux Azure VM

3. Connect SQL Server on Linux VM from SSMS


2017 blogging in review

December 31, 2017 1 comment

 

Happy New Year 2018 from SQLwithManoj !!!
 

This time again like previous year (in 2016) WordPress.com stats helper monkeys didn’t prepare annual report for any of their blogs for year 2017. So I prepared my own Annual Report.
 

–> Here are some Crunchy numbers from 2017

The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 741,708 times by 490,460 unique visitors in 2017. If it were an exhibit at the Louvre Museum, it would take about 17 days for that many people to see it.

There were 38 pictures uploaded, taking up a total of 3.1 MB. That’s about 3 pictures every month.

This blog also got its highest ever hits/views per day (i.e. 3295) on Nov 16th this year.

 

–> All-time posts, views, and visitors


 

–> Posting Patterns

In 2017, there were 30 new posts, growing the total archive of this blog to 509 posts.

LONGEST STREAK: 8 post each day, in June 2017

 

–> Attractions in 2017

These are the top 5 posts that got most views in 2017:

1. Download & Install SQL Server Management Studio (SSMS) 2016 (61,566 views)

2. SQL Server 2016 RTM full & final version available – Download now (38,368 views)

3. SQL Basics – Difference b/w TRUNCATE, DELETE and DROP? (15,407 views)

4. SQL Basics – Difference b/w WHERE, GROUP BY and HAVING clause (15,378 views)

5. Passed 70-461 Exam : Querying Microsoft SQL Server 2012 (13,022 views)

 

–> How did they find me?

The top referring sites and search engines in 2016 were:


 
–> Where did they come from?

Out of 210 countries, top 5 visitors came from India, United States, United Kingdom, Canada and Australia:


 
–> Followers: 352

Wordpress.com: 138
Email: 214
Facebook Page: 1,180

 

–> Alexa Rank (lower the better)

Global Rank: 236,536
India Rank: 44,871
Estimated Monthly Revenue: $172


Alexa history shows how the alexa rank of sqlwithmanoj.com has varied in the past, which in turn also tells about the site visitors.
 

That’s all for 2017, see you in year 2018, all the best !!!
 

Connect me on Facebook, Twitter, LinkedIn, YouTube, Google, Email


SQL Server 2017 – ColumnStore Index enhancements and improvements over previous versions

December 21, 2017 Leave a comment

 
ColumnStore Indexes were first introduced in SQL Server 2012, and this created a new way to store and retrieve the Index or Table data in an efficient manner.
 

What is a ColumnStore Index?
 

What all new features & enhancements done in ColumnStore Index from SQL Server 2012 to 2014 and 2016?
 

–> What’s new in SQL Server 2017?

1. Online Non-Clustered ColumnStore index build and rebuild support added

2. Clustered Columnstore Indexes now support LOB columns (nvarchar(max), varchar(max), varbinary(max))

3. Columnstore index can have a non-persisted computed columns

4. The -fc option in Database Tuning Advisor (DTA) for allowing recommendations of ColumnStore indexes
 

–> Video on ColumnStore Index:


SQL Server 2017 In-Memory enhancements and improvements over previous versions

December 20, 2017 Leave a comment

 
In-Memory tables were introduced in SQL Server 2014 and were also known as Hekaton tables. You can check my previous articles about In-memory tables for [SQL Server 2014] and [SQL Server 2016].
 

–> In-memory tables as new concept in SQL Server 2014/2016 had lot of limitations compared to normal Disk based tables. But with the new release of SQL Server 2017 some limitations are addressed and other features have been added for In-Memory tables. These improvements will enable scaling to larger databases and higher throughput in order to support bigger workloads. And compared to previous version of SQL Server it will be easier to migrate your applications to and leverage the benefits of In-Memory OLTP with SQL Server 2017.
 

–> I have collated all the major improvements here in the table below:

1. sp_spaceused is now supported for memory-optimized tables.

2. sp_rename is now supported for memory-optimized tables and natively compiled T-SQL modules.

3. CASE statements are now supported for natively compiled T-SQL modules.

4. The limitation of eight indexes on memory-optimized tables has been eliminated.

5. TOP (N) WITH TIES is now supported in natively compiled T-SQL modules.

6. ALTER TABLE against memory-optimized tables is now substantially faster in most cases.

7. Transaction log redo of memory-optimized tables is now done in parallel. This bolsters faster recovery times and significantly increases the sustained throughput of AlwaysOn Availability Group configuration.

8. Memory-optimized filegroup files can now be stored on Azure Storage. Backup/Restore of memory-optimized files on Azure Storage is supported.

9. Support for computed columns in memory-optimized tables, including indexes on computed columns.

10. Full support for JSON functions in natively compiled modules, and in check constraints.

11. CROSS APPLY operator in natively compiled modules.

12. Performance of B-tree (NonClustered) index rebuild for MEMORY_OPTIMIZED tables during database recovery has been significantly optimized. This improvement substantially reduces the database recovery time when NonClustered indexes are used.