Guide - SQL Server
Kony Platform
Release 5.0.7
Copyright © 2013 Kony, Inc. All rights reserved.
February, 2014
This document contains information proprietary to Kony, Inc., is bound by the Kony license agreements and may not be used except in the context of understanding the use and methods of Kony software without prior, express, written permission. Kony and Empowering Everywhere are trademarks of Kony, Inc. Microsoft, the Microsoft logo, Internet Explorer, Windows and Windows Vista are registered trademarks of Microsoft Corporation. Apple, the Apple logo, iTunes, iPhone, iPad, OS X, Objective-C, Safari and Xcode are registered trademarks of Apple, Inc.. Google, the Google logo, Android and the Android logo are registered trademarks of Google, Inc. Chrome is a trademark of Google, Inc. BlackBerry, PlayBook, Research in Motion, and RIM are registered trademarks of BlackBerry. All other terms, trademarks or service marks mentioned in this
Revision History
Date
Document Version
Description of Modifications/Release
09/28/2012 1.0 Document Release
03/20/2013 2.0 Added Cluster Controller, passbook and quartzalone support
08/13/2013 3.0 Added upgrade section for KPNS 5.0.6 02/13/2014 4.0 Updated the "Database Upgrade" section for
Table of Contents
1. Overview 5 1.1 Scope 5 1.2 Intended Audience 5 1.3 Typographical Conventions 5 1.4 Contact Us 6 2. Introduction 7 2.1 Prerequisites 82.2 Scripts for Kony database setup: 8
2.3 Approach for KPNS Database Setup 9
3. Database Setup 10
3.1 Steps for Database Setup 10
3.2 Database Setup Verification 11
3.3 Database Setup Complete 11
4. Database Upgrade 12
4.1 Steps for Database Upgrade 12
4.2 Upgrade KPNS from version 5.0.5 to version 5.0.7 12
4.3 Upgrade KPNS from version 5.0.6 to version 5.0.7 12
4.4 Database Upgrade Verification 13
1. Overview
KonyOne Push Notification (KPN) database gives you an overall idea of the KPN database system
requirements, setup and configuration procedures. This document details all the detail steps required to setup the KPN SQL Server database.This database allows you to send Push Notifications to multiple platforms ( for example, iPhone, Android, BlackBerry and Windows).
1.1 Scope
This document describes the setup and configuration of KPN database.
1.2 Intended Audience
This document is intended for database administrators and other technical personnel responsible for setting up and maintaining the Kony Push Notification Database.
1.3 Typographical Conventions
The following are the typographical conventions used throughout the document:
Conventions
Explanation
Monospace
n User input text, system prompts and responses n File Path n Commands n Program Code n File Names. Italic n Emphasis
n Names of Books and Documents n New Terminology. Bold n Windows n Menus n Buttons n Icons n Fields n Tabs n Folders. URL Active link to a URL.
Note: Provides helpful hints or additional information.
Important! Highlights actions or information that might cause problems to systems or
1.4 Contact Us
We welcome your feedback on our documentation. Write to us [email protected] technical questions, suggestions, comments, or to report problems on Kony's product line, contact
2. Introduction
Kony Push Notifications (KPN) provides a generic Push Notifications Service that allows you to send Push
Notifications to multiple platforms (for example, iPhone, Android, Blackberry, and Windows) using a single interface, without having to worry about the requirements for individual platforms (for example, formatting, status query, retry on failure etc.).
KPN also allows you to send Push Notifications to customer specific IDs (for example email addresses) that a customer provides; rather than Unique Identifiers (Unique Identifier is provided by a Push Notifications Service provider when a user successfully registers for Push Notifications). You can also query the status of the Push Notification messages.
2.1 Prerequisites
Prerequisites for installing the KPNS Database for SQL Server:
1. Only a qualified SQL Server Database Administrator (DBA) must handle the SQL Server Database setup in order to successfully complete the KPNS database deployment.
2. SQL Server software must be installed and basic database must have been already created , to continue with KPNS database schema setup.
3. Database collation has to be set for Database, this is possible while creating database, hence ensure that create the database needed for KPNS with your required language as Database collation, for example, for Arabic language support, use Database collation as Arabic_100_CI_AS, similarly for other languages use appropriate collation for support. Use SQL Server Management Studio to create database with name as ‘kpnsdb’ with appropriate Collation support.
Read this information to understand the System requirements and necessary Software before installing KPNS.
2.1.1 Hardware Requirements (per physical instance)
Component
Requirement
Processor Dual Core 2.2 MHz
Memory 8 GB
Internal Storage 73 GB (15K RPM) with 2 Drives (Raid 1) Network 1 Gigabit Ethernet Ports
IP Configuration Statically assigned IP addressing Operating System Windows
2.1.2 Software System Requirements (per physical instance)
Purpose
Server Name
Database software SQL Server 2008
2.2 Scripts for Kony database setup:
The Database scripts are available in the SQL_Server-scripts.tar file on the Kony Developer Portal at:
http://developer.kony.com/PluginReleases.
Navigate to appropriate major GA version and download the files from PushNotificationServer > Download
> Scripts.
2.3 Approach for KPNS Database Setup
You need to follow the "bottom>up" approach to perform the KPNS database setup. It would be performed in the following order:
1. Operating System 2. Storage
3. Clusterware (if any)
4. Database software and Database
3. Database Setup
You can perform the database setup by executing the script files:
SQL Server Database folder contains the
SQL_Server-scripts.tar
file.This file contains the following script files:
1. SQL_Server_CREATE_DB_SCHEMAS.SQL
2. SQL_Server_KPNSDB_Create.sql
3. SQL_Server_KPNSDB_STOREDPROCEDURE.sql
4. SQL_Server_QUARTZALONE_Create.sql
5. SQL_Server_USERMGMT_CREATE.sql
We assume that the
SQL_Server-scripts.tar
file is copied to and extracted at some folder on the system. Here after the location where the file is copied/extracted is referred to as <File_Location>.3.1 Steps for Database Setup
Perform the following tasks to setup the database:1. Log in to the host machine/server which has access to SQL Server instance and navigate to the location of the script files.
l Using SQL Server Management Studio,create the database with the name KPNS in your SQL Server instance along with the schemas named as kpnsdb,usermgmt also,create a database login dbclient with SQL Server authentication which will be owning all the KPNS database and schemas.
2. Execute scripts using SQL Server Management Studio’s query editor or using sqlcmd client. For sqlcmd method follow the following steps.
> cd <File_Location>
> sqlcmd -S "<IP/hostname\SQLSERVER-instancename>"-U dbclient - P "kony123"
Note: Use either IP address or <hostname\SQLSERVER-instance name> while connecting to SQL Server
using sqlcmd,also please note the password used here is default, please use actual password while executing the commands.
3. Execute
SQL_Server_CREATE_DB_SCHEMAS.sql
script file.This script creates database and schemas followed by creation of kpnsdb objects in kpnsdb schema.:r "SQL_Server_CREATE_DB_SCHEMAS.SQL" :r "SQL_Server_KPNSDB_Create.sql"
4. Execute the following script file.This scripts creates QUARTZ framework objects in the QUARTZALONE schema.
:r "SQL_Server_QUARTZALONE_Create.sql"
5. Execute
SQL_Server_usermgmt_create.sql
script file.This script creates usermgmt objects in usermgmt schema.:r "SQL_Server_USERMGMT_CREATE.sql"
3.2 Database Setup Verification
Run the following commands to check if the deployment is successful and all the objects are created successfully.
use master go
select name from sys.databases go
use KPNS go
SELECT TABLE_SCHEMA, TABLE_NAME,TABLE_TYPE FROM [KPNS].[INFORMATION_SCHEMA].[TABLES] order by 3,1,2 go
3.3 Database Setup Complete
Once you are done with all the Database setup procedures, you can now proceed with the installation of KPNS Server on application servers. For more details, refer the "Install and Configure KPNS" section in the "Kony_Push_Notifications_User_Guide" document.
4. Database Upgrade
You can upgrade the database setup by executing the upgrade script files available in the
SQL_Server-scripts.tar
file.l upgrade_from_5.0.5_to_5.0.7_kpnsdb.sql l upgrade_from_5.0.6_to_5.0.7_kpnsdb.sql l upgrade_from_5.0.6_to_5.0.7_quartzalone.sql
Important: If you are using internationalization, please refer Step 3 in thePrerequisitessection.
We assume that the
SQL_Server-scripts.tar
file is copied to and extracted at some folder on the system. Here after the location where the file is copied/extracted is referred to as <File_Location>.4.1 Steps for Database Upgrade
Perform the following tasks to upgrade the database:1. Login to the host machine/server which has access to SQL Server instance and navigate to the location of the script files.
2. Execute upgrade scripts using SQL Server Management Studio’s query editor or using sqlcmd client. For sqlcmd method follow the following steps.
> cd <File_Location>
> sqlcmd -S "<IP/hostname\SQLSERVER-instancename>"-U dbclient - P "<password>"
Note: Use either IP address or <hostname\SQLSERVER-instance name> while connecting to SQL Server
using sqlcmd,also please note the password used here is default, please use actual password while executing the commands.
4.2 Upgrade KPNS from version 5.0.5 to version 5.0.7
Execute the following statement. These script updates objects in KPNSDB and QUARTZALONE schema. :r "upgrade_from_5.0.5_to_5.0.7_kpnsdb.sql"
:r "upgrade_from_5.0.6_to_5.0.7_kpnsdb.sql" :r "upgrade_from_5.0.6_to_5.0.7_quartzalone.sql"
Important: Please verify the scripts execution.
4.3 Upgrade KPNS from version 5.0.6 to version 5.0.7
Execute the following statement. These script updates the objects in KPNSDB and QUARTZALONE schema.
:r "upgrade_from_5.0.6_to_5.0.7_kpnsdb.sql" :r "upgrade_from_5.0.6_to_5.0.7_quartzalone.sql"
Important: Please verify the scripts execution.
Note: While running the below statements if you get any error related data already exists and column data
type not allowed to change, please delete the data from the MessageEntry and messagerequest tables and then run the below commands again.
4.4 Database Upgrade Verification
Run the following commands to check if the deployment is successful and all the objects are created successfully.
use master go
select name from sys.databases go
use KPNS go
SELECT TABLE_SCHEMA, TABLE_NAME,TABLE_TYPE FROM [KPNS].[INFORMATION_SCHEMA].[TABLES] order by 3,1,2 go
4.5 Database Upgrade Complete
Once you are done with all the Database upgrade procedures, you can now proceed with the upgrade of KPNS. For more details, refer the "Upgrade KPNS' section in the "Kony_Push_Notifications_User_Guide" document.