Search This Blog

Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts

Friday, July 24, 2015

Getting Started with SQL Server 2014 In-Memory OLTP

SQL Server 2014 introduces memory-optimized database technology for optimizing the performance of OLTP workloads. In particular, it introduces memory-optimized tables for efficient, contention-free data access, and natively compiled stored procedures for efficient execution of business logic.    
With this post we are giving you a brief introduction to the new syntax for in-memory OLTP, and will show you how to get started with T-SQL and SQL Server Management Studio (SSMS).    
Before we start, let’s create a sample database. If you have an existing database you can skip this step.

-- optional: create database
CREATE DATABASE imoltp
GO

SSMS: To create a database,
1.    In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
2.    Right-click Databases, click New Database and then enter the value for the database name.


Step 1: enable your database for in-memory OLTP
We are going to add a filegroup for memory_optimized_data to our database, and add a container to this filegroup. This filegroup will be used to guarantee durability of memory-resident data in the event of a server crash or restart. During the crash recovery phase in server startup, the data is recovered from this filegroup and loaded back into memory.
When creating the container in the memory_optimized_data filegroup you must specify the storage location. In this example we picked the folder ‘c:\data’. Make sure the folder exists before running the script.
-- enable for in-memory OLTP - change file path as needed
ALTER DATABASE imoltp ADD FILEGROUP imoltp_mod CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE imoltp ADD FILE (name='imoltp_mod1', filename='c:\data\imoltp_mod1') TO FILEGROUP imoltp_mod
GO

SSMS: To add a memory_optimized_data filegroup and its container,
1.    In Object Explorer, expand the Databases node, right-click your database and then click Properties.
2.    To add a new memory optimized data filegroup, click the Filegroups page. Under MEMORY OPTIMIZED DATA, click Add filegroup and then enter the values for the filegroup.
3.    To add a file to the filegroup, click the General page. Under Database files, click Add and then enter the values for the file. Use file type FILESTREAM Data.

Getting Started with SQL Server 2014 In-Memory OLTP
Getting Started with SQL Server 2014 In-Memory OLTP Part 2
 
Step 2: create your first memory-optimized table

We are now ready to create our first memory-optimized tables. We have here two tables, ‘ShoppingCart’, and ‘UserSession’. ‘ShoppingCart’ is a durable table (the default), which means that its contents are persisted on disk and will not be lost on a server crash. ‘UserSession’ is a non-durable table (DURABILITY=SCHEMA_ONLY), which means that the contents of the table exist only in memory, and are lost on server restart.
Note that in CTP1 memory-optimized tables support only ‘nonclustered hash’ indexes. The bucket_count of the index should be roughly 1 to 2 times the number of unique index keys you expect to find in the table.
-- create memory optimized tables
USE imoltp
GO

-- durable table – contents of this table will not be lost on a server crash
CREATE TABLE dbo.ShoppingCart (
   ShoppingCartId int not null primary key nonclustered hash with (bucket_count=2000000),
   UserId int not null index ix_UserId nonclustered hash with (bucket_count=1000000),
   CreatedDate datetime2 not null,
   TotalPrice money
)
WITH (MEMORY_OPTIMIZED=ON)
GO

-- non-durable table – contents of this table are lost on a server restart
CREATE TABLE dbo.UserSession (
   SessionId int not null primary key nonclustered hash with (bucket_count=400000),
   UserId int not null,
   CreatedDate datetime2 not null,
   ShoppingCartId int,
   index ix_UserId nonclustered hash (UserId) with (bucket_count=400000)
)
WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY)
GO

SSMS: To create a memory-optimized table,
1.    In Object Explorer, right-click the Tables node of your database, click New, and then click Memory Optimized Table. A template for creating a memory-optimized table is displayed.
2.    To replace the template parameters, click Specify Values for Template Parameters on the Query menu. The shortcut key is Ctrl-Shift-M.

Step 3: load your data
You can load data into the tables in various ways, including INSERT .. SELECT from an existing disk-based table and BCP. In this example we are using simple INSERT statements for loading the data.
-- Basic DML
-- insert a few rows
INSERT dbo.UserSession VALUES (1,342,GETUTCDATE(),4)
INSERT dbo.UserSession VALUES (2,65,GETUTCDATE(),NULL)
INSERT dbo.UserSession VALUES (3,8798,GETUTCDATE(),1)
INSERT dbo.UserSession VALUES (4,80,GETUTCDATE(),NULL)
INSERT dbo.UserSession VALUES (5,4321,GETUTCDATE(),NULL)
INSERT dbo.UserSession VALUES (6,8578,GETUTCDATE(),NULL)
INSERT dbo.ShoppingCart VALUES (1,8798,GETUTCDATE(),NULL)
INSERT dbo.ShoppingCart VALUES (2,23,GETUTCDATE(),45.4)
INSERT dbo.ShoppingCart VALUES (3,80,GETUTCDATE(),NULL)
INSERT dbo.ShoppingCart VALUES (4,342,GETUTCDATE(),65.4)
GO

-- verify table contents
SELECT * FROM dbo.UserSession
SELECT * FROM dbo.ShoppingCart
GO

SSMS: To view the contents of a memory-optimized table,
⦁    In Object Explorer, right-click on your memory-optimized table, click on Script Table as, click on SELECT To, click on New Query Editor Window and then execute the query that is displayed.

Step 4: update statistics
Memory-optimized tables do not support auto_update_statistics, thus statistics will need to be updated manually. You can use UPDATE STATISTICS to update statistics for individual tables, or sp_updatestats for all tables in the database.
-- update statistics on memory optimized tables
UPDATE STATISTICS dbo.UserSession WITH FULLSCAN, NORECOMPUTE
UPDATE STATISTICS dbo.ShoppingCart WITH FULLSCAN, NORECOMPUTE
GO

Step 5: run queries
You are now ready to run your queries. Because they access memory-optimized tables, these queries will benefit from the latch-free data structures and more efficient data access. Here are a few examples.
-- in an explicit transaction, assign a cart to a session and update the total price.
-- note that the isolation level hint is required for memory-optimized tables with
-- SELECT/UPDATE/DELETE statements in explicit transactions
BEGIN TRAN
  UPDATE dbo.UserSession WITH (SNAPSHOT) SET ShoppingCartId=3 WHERE SessionId=4
  UPDATE dbo.ShoppingCart WITH (SNAPSHOT) SET TotalPrice=65.84 WHERE ShoppingCartId=3
COMMIT
GO
-- verify table contents
SELECT *
FROM dbo.UserSession u JOIN dbo.ShoppingCart s on u.ShoppingCartId=s.ShoppingCartId
WHERE u.SessionId=4
GO

Step 6: create natively compiled stored procedures
To further optimize the access to memory-optimized tables, and to optimize execution of your business logic, you can create natively compiled stored procedures. While these procedures are written using Transact-SQL, they do not support the full Transact-SQL surface area. For details, see Books Online.
Here is an example of a natively compiled stored procedure that accesses the tables we created previously.
-- natively compiled stored procedure for assigning a shopping cart to a session
CREATE PROCEDURE dbo.usp_AssignCart @SessionId int
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')

  DECLARE @UserId int,
    @ShoppingCartId int

  SELECT @UserId=UserId, @ShoppingCartId=ShoppingCartId
  FROM dbo.UserSession WHERE SessionId=@SessionId

  IF @UserId IS NULL
    THROW 51000, 'The session or shopping cart does not exist.', 1

  UPDATE dbo.UserSession SET ShoppingCartId=@ShoppingCartId WHERE SessionId=@SessionId
END
GO

EXEC usp_AssignCart 1
GO

The following stored procedure showcases the performance of natively compiled stored procedures by inserting a large number of rows into a memory-optimized table. This scripts inserts 1,000,000 rows.
Note that if log IO becomes a bottleneck in the application, SQL Server allows you to use a non-durable table (DURABILITY=SCHEMA_ONLY), which removes the log IO completely.
-- natively compiled stored procedure for inserting a large number of rows
--   this demonstrates the performance of native procs
CREATE PROCEDURE dbo.usp_InsertSampleCarts @StartId int, @InsertCount int
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')

  DECLARE @ShoppingCartId int = @StartId
  WHILE @ShoppingCartId < @StartId + @InsertCount
  BEGIN
    INSERT INTO dbo.ShoppingCart VALUES
         (@ShoppingCartId, 1, '2013-01-01T00:00:00', NULL)
    SET @ShoppingCartId += 1
  END

END
GO

-- insert 1,000,000 rows
DECLARE @StartId int = (SELECT MAX(ShoppingCartId)+1 FROM dbo.ShoppingCart)
EXEC usp_InsertSampleCarts @StartId, 1000000
GO

-- verify the rows have been inserted
SELECT COUNT(*) FROM dbo.ShoppingCart
GO

SSMS: To create a natively compiled stored procedure,
1.    In Object Explorer, right-click the Stored Procedures node of your database, click New, and then click Natively Compiled Stored Procedure. A template for creating natively compiled stored procedures is displayed.
2.    To replace the template parameters, click Specify Values for Template Parameters on the Query menu. The shortcut key is Ctrl-Shift-M.

For more details about the concepts for in-memory OLTP, as well as a reference of the syntax, see Books Online or get started and download SQL Server 2014 CTP1 here.
That’s all for today, but stay tuned for further posts on this blog!   
http://blogs.technet.com

Monday, June 23, 2014

Row Not Found at the Subscriber - Replication Issue

When you find an issue in replication with the error “The row was not found at the Subscriber when applying the replicated command.”, first we have to get the Transaction sequence number and Command ID from the error.
This can be found at Distributer to Subscriber history in replication monitor.

Once we get the Transaction Sequence Number and Command ID we can easily drill down to the command which is causing the issue by using sp_browsereplcmds. Before to this, we have to also find out publisher_database_id.

For finding publisher_database_id, we need to make use of Transaction Sequence Number and Command ID.
Query to find publisher_database_id using Transaction Sequence Number and Command ID
select * from msrepl_commands
where xact_seqno = 0x000BF8FB0003411E000400000000 and command_id=6



Once we get the publisher_database_id from the above query, then we need to execute the below query to get the command which is causing the error.
Query to find the command which is causing error
exec sp_browsereplcmds @xact_seqno_start = '0x000BF8FB0003411E000400000000',
@xact_seqno_end = '0x000BF8FB0003411E000400000000', @Command_id=6, @publisher_database_id=60


Once we get the command, we can manually sync the missing data from publisher to subscriber to make the replication work fine as before.

Note: All these commands have to be run on distribution database.
 
http://www.sanssql.com

Thursday, April 11, 2013

Upgrade SQL Express 2005 to Standard or Enterprise 2005


Microsoft so very graciously provides a free version of SQL 2005, SQL Express, with MOSS. If you install MOSS using the Basic option or Single Server under Advanced you get SQL Express automatically. So what if as a budding newbie SharePoint admin you chose the Basic option, but now as a wise aged SharePoint admin you've seen the error of your ways and want to use a more respectable version of SQL for your SharePoint backend? You're in luck. In this blog post I'll walk you through upgrading SQL Express to SQL Standard or Enterprise.
First thing you need to do is get a copy of SQL 2005 Standard or Enterprise. Which version you choose depends on the redundancy and availability you want. Either will upgrade from Express. After you have your media you can start the install. This step is important as you have to pass the setup program a parameter to let it know you're doing an upgrade. To upgrade use the command setup.exe SKUUPGRADE=1. It should look like this:
The setup should kick off and it won't give you any indication that you passed it a parameter. Never fear, it will come up later. When the setup gets to the instance selection make sure you don't accept the default instance and you choose the SQL Express instance. First click Installed Instances:
Then choose the OFFICESERVERS instance from the list and hit OK:
Your next screen should confirm that it found SQL Express:
If you click the Details… button you'll see that the setup confirms that SQL Express can be upgraded. This step is optional.
At this point you can Close the box and hit OK until the setup is finished. After the setup is finished you'll want to apply SP2 for SQL 2005 and any post SP2 patches that are available. I don't know if it's required but I always do an IISRESET after this to make sure that SharePoint reconnects properly to SQL.
That's all there is to it. Once you are using full SQL you have quite a few more options available to you like log shipping, database replication, SQL Profiler and more. If you have any questions about how to leverage those tools with SharePoint, leave me a comment and let me know.
tk
www.toddklindt.com 

Friday, June 15, 2012

Đồng bộ hóa dữ liệu trên 2 Database Server dùng SQL Server 2008


Khi bạn sở hữu một website với cơ sỡ dữ liệu đồ sộ và có lượng truy cập cao thì vấn đề an toàn dữ liệu và tính sẵn sàng cao của dữ liệu là hết sức cần thiết. Thông thường các website này sẽ chạy trên nhiều Database đặt ở các server khác nhau nhằm đáp ứng tính an toàn cho dữ liệu, đồng thời giảm tải lên 1 database server khi website có khi lượng truy cập quá lớn.
Trong bài viết này, mình sẽ trình bày cách triển khai cơ sỡ dữ liệu trên 2 Database server với khả năng đồng bộ dữ liệu với nhau, đảm bảo tính nhất quán của dữ liệu website.
Chuẩn bị:
  • 2 server để chứa database.
  • Trên 2 server có cài sẵn SQL Server 2008. Mình khuyến khích dùng SQL Server 2008 Enterprise chạy trên Windows Server 2008.
  • Cài thêm công cụ SQL Server Management để thao tác.
Tiến hành:
Khi đã cài SQL Server xong, bạn đảm bảo các dịch vụ như hình bên dưới đã start thành công!
  • SQL Server
  • SQL Server Agent
  • SQL Server Browser

Trong Protocols for MSSQLSQLSERVER đảm bảo đã Enable giao thức TCP/IP

Tiến hành dùng công cụ SQL Management để đăng nhập vào server 1 và server 2. Trong bài lab này mình sẽ sử dụng 2 server với tên là kenhgiaiphap01 và kenhgiaiphap02.

Sau khi đăng nhập xong, ở Server kenhgiaiphap01 tạo database là test1 và ở serverkenhgiaiphap02 tạo database là test2. Đây sẽ là cơ sỡ dữ liệu của website, 2 database này sẽ có dữ liệu hoàn toàn giống sau khi đã đồng bộ hóa.
Yêu cầu database test1 của bạn cần được import dữ liệuh trước (database test2 ko cần).

Sau đó, ở server kenhgiaiphap01 bạn bung Replication, bấm phải chuột vào Publication và chọn New Publication.

Cửa sổ Welcome hiện ra, ta chọn Next

Chọn database mà ta muốn đồng bộ hóa với server 2. Ở đây ta chọn test1
Chọn chế độ Merge Puplication
Chú ý: Nếu bạn chọn chế độ Transactional puplication thì dữ liệu sẽ được đồng bộ theo 1 chiều, tức là server 1 cập nhật dữ liệu thì server 2 cũng sẽ có dữ liệu . Tuy nhiên ngược lại thì không được. Còn chế độ Merge Puplication sẽ đồng bộ dữ liệu theo cả 2 chiều.

Do nhiều server có thể chạy các bản SQL Server khác nhau, nên ở đây bạn sẽ được yêu cầu chọn phiên bản. Mình sẽ chọn SQL Server 2008.

Chọn những thành phần trong Database mà bạn muốn nó được đồng bộ hóa.
Chú ý: Table của Database mà bạn muốn đồng bộ hóa cần có khóa chính.

Tiếp tục họn Next

Tiếp tục họn Next

Cho phép tạo Snapshot ngay lập tức và sau đó chọn Next

Chọn Security Setting

Nhập lại tài khoản đăng nhập SQL Server của Server 1. Ở đây, mình khuyến  khích bạn dùng chế độ đăng nhập Windows Account

Ok -> Next

Nhập tên hiển thị. Ở đây mình nhập là Test Replication

Sau đó nhấn Next, nếu không có gì sai thì kết quả sẽ như hình dưới đây.

Khi đã tạo một Publication thành công, ta tiếp tục click phải chuột lên nó và chọn New Subscriptions

Ở màng Welcome chọn Next

Chọn Cơ sở dữ liệu mà Server 2 cần lấy để đồng bộ hóa.

Nhấn Next

Ở đây, ta nhấn vào ADD SQL Server Subcriber và add vào server thứ 2 (kenhgiaiphap02) và chọn cơ sỡ dữ liệu test 2 trên server này.

Nhấn Next và nhập thông tin đăng nhập của Database server 2 (kenghaiphap02)

Nhấn next. Trong Agent Schedule ta chọn Run Continously

Nhấn next.

Nhấn Next

Kiểm tra lại thông tin và nhấn Finish

Nếu setup thành công, thì kế quả sẽ như hình dưới đây.

Chờ một chút để cơ sở dữ liệu từ test1 đồng bộ sang test2

Kiểm tra kết quả:
Trên Database Test1 tiến hành nhập một Record mới.

Mở Database test2 lên và thấy dữ liệu đã được cập nhật y như bên database test1. Và ngược lại nếu có sử thay đổi trên database test2 thì database test1 cũng sẽ được cập nhật.

Chúc bạn thành công! Smile

Wednesday, March 21, 2012

SQL SERVER – The server network address “TCP://SQLServer:5023″ can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

While doing SQL Mirroring, we receive the following as the most common error:
The server network address “TCP://SQLServer:5023″ cannot be reached or does not exist.
Check the network address name and that the ports for the local and remote endpoints are operational.
(Microsoft SQL Server, Error: 1418)
The solution to the above problem is very simple and as follows.
Fix/WorkAround/Solution: Try all the suggestions one by one.
Suggestion 1: Make sure that on Mirror Server the database is restored with NO RECOVERY option (This is the most common problem).
Suggestion 2: Make sure that from Principal the latest LOG backup is restored to mirror server. (Attempt this one more time even though the full backup has been restored recently).
Suggestion 3: Check if you can telnet to your ports using command TELNET ServerName Ports like “telnet SQLServerName 5023″.
Suggestion 4: Make sure your firewall is turned off.
Suggestion 5: Verify that the endpoints are started on the partners by using the state or state_desc column the of the sys.database_mirroring_endpoints catalog view. You can start end point by executing an ALTER ENDPOINT statement.
Suggestion 6: Try the following command as one of the last options.
GRANT CONNECT ON ENDPOINT::Mirroring TO ALL
Suggestion 7: Delete the end points and recreate them.
If any of above solutions does not fix your problem, do leave comment here. Based on the comment, I will update this article with additional suggestions.
Please note that some of the above suggestions can be security threat to your system. Please use them responsibly and review your system with security expert in your company.
http://blog.sqlauthority.com

Friday, June 24, 2011

How do I configure SQL Server Express to allow remote tcp/ip connections on port 1433?

The following article explains how to allow SQL Server Express to accept remote connections over TCP/IP for port 1433. By default, when SQL Server Express is installed it gerates a random port to listen on. In addition, SQL Server Express only listens for connection on localhost. Using the SQL Server Configuration Manager, you will need to remove the dynamic port setting and tell SQL Server Express to use port 1433 and your assigned IP address (10.x.x.x).

To allow SQL Server Express to accept remote connections, please follow these steps:
1) Log into your server through Remote Desktop Connection (instructions for connecting to your server through RDC can be found here).
2) Click Start, Programs, Microsoft SQL Server 2005/2008 and select SQL Server Configuration Manager.


3) Select SQL Server Network Configuration


4) Double click on Protocols for SQLEXPRESS


5) Right click TCP/IP and select Properties

6) Click on the IP Address tab, and then under IP2 - IP Address, enter your internal ip address (10.x.x.x).


7) Scroll down to IPAll make sure TCP Dynamic Ports is blank and that TCP Port is set to 1433.

8) Click OK
9) Make sure that port: 1433 is enable on your VDS firewall (instructions for enabling firewall ports can be found here).
10) Mixed mode authentication must also be enabled for remote connections (instructions for enabling firewall ports can be found here).
support.webecs.com